locked
SQL Updae RRS feed

  • Question

  • User1390435587 posted

    Hello

    Having trouble with doing a SQL update. This is the first time I have used multiple tables in a project. I need the syntax to define the database and the table I think...

    Dim Sql As String = "UPDATE ['Tools!Book_Out_Table'] SET Booked_in_by = @Booked_in_by, Date_In=@Date_I

    The above is a snip from the code. The database is called 'Tools' and the table is called 'Book_out_Table' -  When I try using the above, and all sorts of variations of the above I either get a syntax failure message or invalid object name failure message.

    I read in some help it should be [Tools!]Book_Out_Table but that did not work either.

    Any idea where I'm going wrong?

    thanks as ever

    Peter

    Tuesday, February 18, 2014 3:05 AM

Answers

  • User1390435587 posted

    Thank you both for taking the time to reply. It seems I confused myself (normlly the way!!) - The last site I wrote used a table with gaps in the name, i.e 'this is the table name' -

    This required that I use single quotes

    Sql As String = "UPDATE ['this is the table name'] SET Booked_in_by = @Booked_in_by, Date

    In this latest site there are no gaps in the table name but I put the quotes there by mistake (copied from last time!). This caused a failure, from there I thought I had to declare the database as well as the name,so thats what I was trying. I sort of headed off in the wrong direction. The actual answer was quite simply....

    Dim Sql As String = "UPDATE [Book_Out_Table] SET Booked_in_by = @Booked_in_by, Date_In=@Date_In,

    Which I found after monkeying around with it for a while

    Again, thanks for your time, I do appreaciate it.

    best regards

    Peter

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 18, 2014 5:51 AM

All replies

  • User-1012551284 posted

    Are you sure that everything is spelled correctly, and nothing is uppercase, when it should be lowercase?

    Can I please see the Tools table structure?

    Tuesday, February 18, 2014 3:45 AM
  • User1071505668 posted

    Hi 

    Please try the below code

    UPDATE Tools.dbo.Book_Out_Table SET Booked_in_by = @Booked_in_by, Date_In=@Date_I

    In this dbo refers to the schema name of the table. If you haven't created any schema then by default it will create the schema as dbo. Check the above query and reply to me.

    Thanks

    .Net Pickles

    Tuesday, February 18, 2014 3:51 AM
  • User1390435587 posted

    Thank you both for taking the time to reply. It seems I confused myself (normlly the way!!) - The last site I wrote used a table with gaps in the name, i.e 'this is the table name' -

    This required that I use single quotes

    Sql As String = "UPDATE ['this is the table name'] SET Booked_in_by = @Booked_in_by, Date

    In this latest site there are no gaps in the table name but I put the quotes there by mistake (copied from last time!). This caused a failure, from there I thought I had to declare the database as well as the name,so thats what I was trying. I sort of headed off in the wrong direction. The actual answer was quite simply....

    Dim Sql As String = "UPDATE [Book_Out_Table] SET Booked_in_by = @Booked_in_by, Date_In=@Date_In,

    Which I found after monkeying around with it for a while

    Again, thanks for your time, I do appreaciate it.

    best regards

    Peter

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 18, 2014 5:51 AM
  • User753101303 posted

    Hi,

    Or UPDATE [A Table wiith space in it] etc.. should work as well. In SQL Server, the default is to use [] to quote identifiers (still it's likely error prone to put spaces in object name).

    Base on the! in 'Tools!Book_Out_Table' I actually believe that the confusion might come from Excel. The ! character is a way to address a particular range inside a sheet when using OleDb to access Excel files (if I remember). So you perhaps mix several SQL dialect particularities here...

    Tuesday, February 18, 2014 5:58 AM