-
Jan 14th, 2021, 07:08 AM
#1
Thread Starter
Fanatic Member
[RESOLVED] SQLite Rename column
Hello everyone
I need to rename a column in one of my tables.
This is the code I'm using
Code:
Cnn.Execute "ALTER TABLE Total_tbl" _
& "RENAME COLUMN Tfirst TO Tlast;"
but I'm getting syntax error.
I should note that this table is linked to a parent table via foreign key. (ID _ Tid)
I'm using sqiteand Vbrichclient5)
thank you
-
Jan 14th, 2021, 07:35 AM
#2
Re: SQLite Rename column
Supposedly SQLite added renaming columns in 3.25, so if you have an up-to-date RC5 then you should be able to rename columns. I do notice that your SQL is missing a space between the table name and "RENAME" so you total query will look like this:
Code:
ALTER TABLE Total_tblRENAME COLUMN Tfirst TO Tlast;
Try putting a space between the table name and the "RENAME" keyword to see if that works.
-
Jan 14th, 2021, 10:35 AM
#3
Thread Starter
Fanatic Member
Re: SQLite Rename column
Thank you sir for the help but it is still not working.
syntax error near Column
-
Jan 14th, 2021, 10:49 AM
#4
Re: SQLite Rename column
Can you post your new code/SQL statement?
Also, if you copy & paste the following code into the Immediate window and press Enter, what version # appears?
Code:
?New_c.Connection.Version
-
Jan 14th, 2021, 11:08 AM
#5
Thread Starter
Fanatic Member
Re: SQLite Rename column
Thank you sir
Code:
Cnn.Execute "ALTER TABLE Total_tbl RENAME Tfirst TO Tlast;"
When execting this code I get error
Code:
?New_c.Connection.Version
Object variable or with block variable not set
-
Jan 14th, 2021, 11:17 AM
#6
Re: SQLite Rename column
You don't have the "COLUMN" keyword in your SQL any more. It should be "ALTER TABLE Total_tbl RENAME COLUMN TFirst to Tlast;"
Regarding the Object variable not set error - that shouldn't happen if you copy & pasted into the immediate window while a project using RC5 is open. Did you have your project open when you tried to run the command?
-
Jan 14th, 2021, 11:26 AM
#7
Thread Starter
Fanatic Member
Re: SQLite Rename column
You don't have the "COLUMN" keyword in your SQL any more. It should be "ALTER TABLE Total_tbl RENAME COLUMN TFirst to Tlast;"
I'm sorry for missing "COLUMN" when posting the code here.
My code on the form is this:
Code:
Cnn.Execute "ALTER TABLE Total_tbl RENAME column Tfirst TO Tlast;"
Regarding the Object variable not set error - that shouldn't happen if you copy & pasted into the immediate window while a project using RC5 is open. Did you have your project open when you tried to run the command?
This is all What I did:
I opened the .vbp project.
Then Opened the immediate window.
Paste the code
Press enter
I get the error.
-
Jan 14th, 2021, 11:29 AM
#8
Re: SQLite Rename column
The code you just posted is still missing the "COLUMN" keyword
Should be:
Code:
Cnn.Execute "ALTER TABLE Total_tbl RENAME COLUMN Tfirst TO Tlast;
(Notice "RENAME COLUMN", not just "RENAME").
But if you are getting an "Object or with variable not set" error in the immediate window while a project referencing RC5 is open, then there's something strange going on.
-
Jan 14th, 2021, 11:31 AM
#9
Re: SQLite Rename column
Oops, just saw you edited your post to include the COLUMN keyword now. So is it still failing? If so I suspect you are using an older RC5 version with a version of SQLite <3.25. If this is the case then RENAME COLUMN support won't be there. Perhaps worth a try to update RC5 to the latest version available at vbrichclient.com
-
Jan 14th, 2021, 11:41 AM
#10
Thread Starter
Fanatic Member
Re: SQLite Rename column
thank you sir
try to update RC5 to the latest version available at vbrichclient.com
I'll do and let you know
-
Jan 14th, 2021, 01:02 PM
#11
Thread Starter
Fanatic Member
Re: SQLite Rename column
Hello sir
I deleted RC5. I downloaded the RC6 package and registred RC6.dll . Then I substituted the old dlls with the new ones in the Bin Folder. In the Sub Main, I changed this code:
Set New_c = GetInstanceFromBinFolder("vbRichClient5", "cConstructor")
to
Set New_c = GetInstanceFromBinFolder("RC6", "cConstructor")
I launched the project, executed the code in the immediate window but the same error
Do I need to install a newer version of SQlite?
thank you sir
-
Jan 14th, 2021, 01:19 PM
#12
Thread Starter
Fanatic Member
Re: SQLite Rename column
thank you sir
The code for renaming the column is working now.
But the code for checking the version still throws error.
-
Jan 14th, 2021, 01:30 PM
#13
Re: SQLite Rename column
Can you show your GetInstanceFromBinFolder function?
-
Jan 14th, 2021, 01:51 PM
#14
Thread Starter
Fanatic Member
Re: SQLite Rename column
That's what I'm using in my Sub Main
Code:
Set New_c = GetInstanceFromBinFolder("RC6", "cConstructor")
Code:
Public Function GetInstanceFromBinFolder(ByVal ShortDllFileName As String, ClassName As String, _
Optional RelBinFolderName$ = "Bin") As Object
Select Case LCase$(Right$(ShortDllFileName, 4))
Case ".dll", ".ocx" 'all fine, nothing to do
Case Else: ShortDllFileName = ShortDllFileName & ".dll" 'expand the ShortFileName about the proper file-ending when it was left out
End Select
Set GetInstanceFromBinFolder = GetInstance(GetExePath & RelBinFolderName & "\" & ShortDllFileName, ClassName)
End Function
-
Jan 14th, 2021, 02:18 PM
#15
Re: SQLite Rename column
Do you have the RC6.dll, cairo_sqlite.dll, and DirectCOM.dll located in the folder that is returned by GetExePath & RelBinFolderName?
-
Jan 14th, 2021, 02:51 PM
#16
Thread Starter
Fanatic Member
Re: SQLite Rename column
Do you have the RC6.dll, cairo_sqlite.dll, and DirectCOM.dll located in the folder that is returned by GetExePath & RelBinFolderName?
yes sir
Everything works fine with RC6 except the code for checking version in immediate window
Last edited by samer22; Jan 14th, 2021 at 02:55 PM.
-
Jan 14th, 2021, 03:03 PM
#17
Re: SQLite Rename column
OK I think what is happening is that you have a Public variable called New_c and this is being set in the Main() sub. This would be a more local scope that the built-in RC6 New_C method, so it's the one that's being used when you try to use New_c in the Immediate window. Since it doesn't get set until Sub Main() executes, you are getting the object or with block variable not set error.
Instead of a public/global New_c variable, it might be better to use a function along the lines of this:
Code:
Public Function New_C() As cConstructor
Static so_Constructor As cConstructor
If so_Constructor Is Nothing Then
' Attempt to create cConstructor object without using the registry
Set so_Constructor = GetInstanceFromBinFolder("RC6", "cConstructor")
If so_Constructor Is Nothing Then
' Reg-free creation failed, attempt to create using normal VB6 registry-based method
Set so_Constructor = New cConstructor
End If
End If
Set New_C = so_Constructor
End Function
With this function you can call New_c in the Immediate window and in your app.
-
Jan 14th, 2021, 03:25 PM
#18
Thread Starter
Fanatic Member
Re: SQLite Rename column
excuse me sir
But I did not understand where I should insert this function.
In The ModRegfreedll Module?
Do I have to replace the GetInstanceFromBinFolder fuction by this function?
-
Jan 14th, 2021, 03:28 PM
#19
Re: SQLite Rename column
Leave the GetInstanceFromBinFolder alone (the function I posted calls it, so you need to keep it). Do you have a line in a standard module like this:
Code:
Public New_c As cConstructor
or
Code:
Global New_c As cConstructor
or maybe just
Code:
New_c As cConstructor
?
If so, remove that line and put my "Public Function New_C()" code in that module.
-
Jan 14th, 2021, 03:49 PM
#20
Thread Starter
Fanatic Member
Re: SQLite Rename column
thank you sir
this is my version: 3.31.1
But there appears another problem
When launching the application I have a new error in the Sub Main
Invalid use of property
Code:
Set New_C = GetInstanceFromBinFolder("RC6", "cConstructor")
-
Jan 14th, 2021, 03:51 PM
#21
Re: SQLite Rename column
Yes sorry, you should remove that line too (and any other lines that try to set New_c that will raise the same Invalid use of property error).
-
Jan 14th, 2021, 04:13 PM
#22
Thread Starter
Fanatic Member
Re: SQLite Rename column
thank you very much sir
Everything woks like a charm
I have another worry that is causing me a headache since long time and I apologize for this as it is quite out of our subject.
When updating two tables under the same Cmd_Save , Do I have to set RS to nothing after Rs.UpdateBatch before selecting the second table?
My apologies again
-
Jan 15th, 2021, 08:53 AM
#23
Re: SQLite Rename column
I'm not sure how you are updating the 2 tables in Cmd_Save. Is Cmd_Save a cCommand object with a single SQL statement? Or are you changing the SQL? In either case there should be no RS/UpdateBatch method involved if you are using a cCommand.Execute call.
Can you post your code?
-
Jan 15th, 2021, 09:34 AM
#24
Re: SQLite Rename column
Originally Posted by samer22
When updating two tables under the same Cmd_Save , Do I have to set RS to nothing after Rs.UpdateBatch before selecting the second table?
Makes no difference and you can test it youself. Just make sure to wrap both batch-updates in a DB transaction.
Even better -- check all your updates are wrapped in separate transactions with proper commit/rollback, even when batch-updating a single table.
cheers,
</wqw>
-
Jan 15th, 2021, 12:11 PM
#25
Thread Starter
Fanatic Member
Re: SQLite Rename column
Thank you gentelmen
Code:
Private Sub save_Click()
StrSql = "Select * from tbl1 where ID = " & TxtID.Text & ""
Set Rs = Cnn.OpenRecordset(StrSql)
If Rs.EOF Then Rs.AddNew
Rs.Fields("ID").Value = TxtID.Text
...............................................
...............................................
Rs.UpdateBatch
Set Rs = Nothing
StrSql = "Select * from tbl2 where PID = " & TxtID.Text & ""
Set Rs = Cnn.OpenRecordset(StrSql)
If Rs.EOF Then Rs.AddNew
Rs.Fields("PID").Value = TxtID.Text
...............................................
...............................................
Rs.UpdateBatch
Set Rs = Nothing
End Sub
My worry is about the "Set Rs = Nothing" in red color.
I'm asking this question because I have been working with access as database for some while and in the case above , closing the connection is necessary before opening another table.
thank you
-
Jan 15th, 2021, 12:15 PM
#26
Re: SQLite Rename column
@wqweto is right, there is no need (though no harm) to explicitly call Set Rs = Nothing.
-
Jan 15th, 2021, 12:30 PM
#27
Thread Starter
Fanatic Member
Re: SQLite Rename column
Thank you again gentelmen
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|