a question about DataAdapter

  • Thread starter Thread starter Tony qu
  • Start date Start date
T

Tony qu

If I generate DataAdapter.UpdateCommand by myself, can I use the following
sql sentence as UpdateCommand

UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName,
ContactName = @ContactName, ContactTitle = @ContactTitle WHERE (CustomerID =
@Original_CustomerID) AND (CompanyName = @Original_CompanyName) AND
(ContactName = @Original_ContactName OR @Original_ContactName IS NULL AND
ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR
@Original_ContactTitle IS NULL AND ContactTitle IS NULL)

because in DeleteCommand,the value of @Original_XXX parameters can be obtain
from database by using timestamp. But in fact, I failed to do so. The mssql
return a error says "Incorrect syntax near CustomerID". Do you know why?
Thanks for your help in advance.
 
If you are generating the UpdateCommand by yourself i.e. without using a
SqlCommandBuilder, make sure that you are also setting up the parameters and
mapping them to the dataset.

For e.g. you need to add the following code to Add the @CustomerID and
@Orignal_CustomerID parameters to the command

SqlParameter pcustID = new
SqlParameter("@CustomerID",SqlDbType.Varchar,50,ParameterDirection.Input,fal
se,0,0,"CustomerID",DataRowVersion.Current,null);

SqlParameter porigCustID = new
SqlParameter("@CustomerID",SqlDbType.Varchar,50,ParameterDirection.Input,fal
se,0,0,"CustomerID",DataRowVersion.Orignal,null);

dataadapter.UpdateCommand.Parameters.Add(pcustID);
dataadapter.UpdateCommand.Parameters.Add(porigCustID);

//Notice that the oringal_custID has DataRowVersion.Orignal whereas custID
has DataRowVersion.Current, this is the key difference between the
parameters. The two parameters are used for Optimistic concurrency checking.
You will need to add all the parameters in the update command in a similar
manner.
 
Back
Top