Last post Feb 18, 2014 05:58 AM by PatriceSc
Feb 18, 2014 03:05 AM|pw2002uk|LINK
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
Feb 18, 2014 03:45 AM|jenskvist|LINK
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?
Feb 18, 2014 03:51 AM|kannadasbe|LINK
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.
Feb 18, 2014 05:51 AM|pw2002uk|LINK
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.
Feb 18, 2014 05:58 AM|PatriceSc|LINK
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...