Is this UPDATE table statement correct in an msdn topic
I have seen this type of UPDATE statement (just like insert statement) in the following msdn topic:
http://msdn.microsoft.com/en-us/library/aa0416cz.aspx#Y2461
UPDATE statement:-
adapter.UpdateCommand = New SqlCommand("UPDATE Customers " &
"(CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " & _
"WHERE CustomerID = @oldCustomerID AND CompanyName = " &
"@oldCompanyName", connection)
Is this statement correct o开发者_开发技巧r not?
I have tried executing it and it is giving syntax errors.
No, it should be:
UPDATE Customers
SET
CustomerID = @CustomerID,
CompanyName = @CompanyName
WHERE
CustomerID = @oldCustomerID AND
CompanyName = @oldCompanyName
Or to be complete 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 reference for you and this situation: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx
That SQL appears to be correct for an INSERT INTO
but not for an UPDATE
It should read:
adapter.UpdateCommand = New SqlCommand("UPDATE Customers" & _
" SET CustomerID = @CustomerID, CompanyName = @CompanyName)" & _
" WHERE CustomerID = @oldCustomerID AND CompanyName =" & _
" @oldCompanyName", connection)
That SQL is what one would call paramaterized, so that makes this code (lower in the snippet) 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
As far as I can see the syntax is not valid. The following gives Incorrect syntax near '('.
I suggest changing it as per Dan's answer.
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
精彩评论