DataAdapter.Update(Datatable) is not working

Question

DataAdapter.Update(Datatable) is not working

hmsanghavi

Mr or Mrs. 500

Points: 535
More actions
May 3, 2012 at 8:18 am

#257505

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE DimRequestMasterTestHS SET [RunningBoard] = ?,[DutyBoard] =? ,[DriverId] =? ,[PublicServiceCode] = ?,[ServiceCode] = ?,[DepotCode] = ?,[Direction] = ?,[EOJMethod] = ?, JourneyNo=? WHERE [RequestID]=? ";
daUpdate.UpdateCommand = cmd;
cmd.Connection = myStagingConnection;
//daUpdate = new SqlDataAdapter("Select * From DimRequestMasterTestHS", myStagingConnection);
//daUpdate.Fill(ds.Tables["PositionUpdates"]);
//cmdBuilder = new SqlCommandBuilder(da);
//daUpdate.UpdateCommand = cmdBuilder.GetUpdateCommand();
daUpdate.UpdateBatchSize = 1000;
daUpdate.Update(ds , "PositionUpdates");
daUpdate is DataAdapter Object

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

hmsanghavi (5/3/2012)
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE DimRequestMasterTestHS SET [RunningBoard] = ?,[DutyBoard] =? ,[DriverId] =? ,[PublicServiceCode] = ?,[ServiceCode] = ?,[DepotCode] = ?,[Direction] = ?,[EOJMethod] = ?, JourneyNo=? WHERE [RequestID]=? ";
daUpdate.UpdateCommand = cmd;
cmd.Connection = myStagingConnection;
//daUpdate = new SqlDataAdapter("Select * From DimRequestMasterTestHS", myStagingConnection);
//daUpdate.Fill(ds.Tables["PositionUpdates"]);
//cmdBuilder = new SqlCommandBuilder(da);
//daUpdate.UpdateCommand = cmdBuilder.GetUpdateCommand();
daUpdate.UpdateBatchSize = 1000;
daUpdate.Update(ds , "PositionUpdates");
daUpdate is DataAdapter Object

don't think a DataAdapter should be used in this case...there's mno data at the client.

shouldn't that be something using ExecuteNonQuery

SqlCommand myCommand = new SqlCommand(myExecuteQuery, myConnection);

myCommand.Connection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hmsanghavi Mr or Mrs. 500 Points: 535 More actions · Answer 2

I m going to update many records at a time.

for that I collect all the data in datatable and then I want to dump it in sql table.

I don't want to update records one by one because I have millions of records and it will take time alot

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

That sounds painful. Why do you need millions of rows in a DataTable? For that much data manipulation it seems like you should keep it all in SQL instead of pulling it all out, massaging it, and then updating.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/