SQL Updae

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

Answer 1

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?

Answer 2

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

Answer 3

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

 

Answer 4

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