开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜