Results 1 to 27 of 27

Thread: [RESOLVED] SQLite Rename column

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Resolved [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

  2. #2
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: SQLite Rename column

    Thank you sir for the help but it is still not working.

    syntax error near Column

  4. #4
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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

  6. #6
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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?

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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.

  8. #8
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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.

  9. #9
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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.

  13. #13
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: SQLite Rename column

    Can you show your GetInstanceFromBinFolder function?

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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

  15. #15
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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?

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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.

  17. #17
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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.

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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?

  19. #19
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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.

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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")

  21. #21
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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).

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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

  23. #23
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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?

  24. #24
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: SQLite Rename column

    Quote Originally Posted by samer22 View Post
    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>

  25. #25

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: SQLite Rename column

    Thank you gentelmen
    Can you post your code?
    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

  26. #26
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: SQLite Rename column

    @wqweto is right, there is no need (though no harm) to explicitly call Set Rs = Nothing.

  27. #27

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    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
  •  



Click Here to Expand Forum to Full Width