Is this UPDATE statement correct in msdn tag

Question

I saw this type of UPDATE statement (like the insert statement) in the following msdn topic:

http://msdn.microsoft.com/en-us/library/aa0416cz.aspx#Y2461

UPDATE operation: -

adapter.UpdateCommand = New SqlCommand("UPDATE Customers " & "(CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " & _ "WHERE CustomerID = @oldCustomerID AND CompanyName = " & "@oldCompanyName", connection) 

Is this statement correct or not?

I tried to execute it and it gives syntax errors.

Answer 1

No, it should be:

 UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName 

Or, to be bundled with your sample code, it should be:

 adapter.UpdateCommand = New SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", connection) 

Here is another link for you and this situation: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx

Answer 2

This SQL seems correct for INSERT INTO , but not for UPDATE It should read:

 adapter.UpdateCommand = New SqlCommand("UPDATE Customers" & _ " SET CustomerID = @CustomerID, CompanyName = @CompanyName)" & _ " WHERE CustomerID = @oldCustomerID AND CompanyName =" & _ " @oldCompanyName", connection) 

This SQL is what can be called parameterized, so this code (below in the snippet) is very important:

 adapter.UpdateCommand.Parameters.Add( _ "@CustomerID", SqlDbType.NChar, 5, "CustomerID") adapter.UpdateCommand.Parameters.Add( _ "@CompanyName", SqlDbType.NVarChar, 30, "CompanyName") ' Pass the original values to the WHERE clause parameters. Dim parameter As SqlParameter = dataSet.UpdateCommand.Parameters.Add( _ "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID") parameter.SourceVersion = DataRowVersion.Original parameter = adapter.UpdateCommand.Parameters.Add( _ "@oldCompanyName", SqlDbType.NVarChar, 30, "CompanyName") parameter.SourceVersion = DataRowVersion.Original 

Answer 3

As far as I understand, the syntax is invalid. The following gives Incorrect syntax near '('.

I suggest changing it to answer Dan .

 CREATE TABLE Customers ( CustomerID INT, CompanyName VARCHAR(10) ) DECLARE @CustomerID INT, @CompanyName VARCHAR(10), @oldCustomerID INT, @oldCompanyName VARCHAR(10) UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName