[dba-VB] ADO.Net Update SQL Server

When I try to call the update method of the data set object I get an error:

"Update requires a valid update command when passed datarow collection 
with modified rows."

I probably sound ungrateful but in your code you appear to set up a sql 
statement for an update statement:

             adapter.UpdateCommand = New 
SqlCommand(Constants.UPDATE_SQL_PARAMETERIZED, cnn)
             adapter.UpdateCommand.Parameters.Add("@" + 
Constants.PARSED_NAME_COL_NAME, SqlDbType.NVarChar, 
Constants.NAME_COL_LENGTH, Constants.PARSED_NAME_COL_NAME)
             adapter.UpdateCommand.Parameters.Add("@" + 
Constants.ID_COL_NAME, SqlDbType.Int, 0, Constants.ID_COL_NAME)
             adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None
             adapter.UpdateBatchSize = pageSize


My problem is that is just Chinese to me.  I have no background 
knowledge in plain English of what an update sql statement is supposed 
to look like (in the ADO context) and absolutely no understanding of 
even what I am trying to accomplish (expressed in plain English).  I am 
uneducated, and I can find no way to get educated.

I must own 6 books on ADO.Net and VB and every single one of them 
expects me to drag and drop objects out onto a form.  OR they very 
helpfully set everything up and show me how to do stuff RIGHT UP TO 
USING IT, but never go that last step of sending updates back to the 
database.  Open, update, display in a grid, cleanup and close.  Hmm.... 
and I paid good money for these books.

They do not discuss anything at all about what is going on at the code 
level.  So when I try to walk through your code it is simply 
indecipherable to me.  If I don't know what you are trying to accomplish 
I can't understand how you code will accomplish that thing.

Please do not take these statements as criticisms of your code, simply 
that I am a freshman student still taking the basket weaving classes 
looking at masters code.  Thus it isn't useful to tell me to trace 
through the code.

MY code doesn't do any of that stuff.  My code is pretty simple:

Set up a connection, a data adapter, a table.  Iterate the table row by 
row, updating fields of the rows.

Now... how to update the table back to SQL Server...

Given how simple everything has been up to this point I have to believe 
that I am just missing a concept and code to implement that concept.  I 
have called the update method and I have googled the error messages and 
nothing...

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Hi John,
> 
> The answer is in the code I published today on my site related to 'Re:
> [dba-VB] ADO.Net':
> 
> http://smsconsulting.spb.ru/samples/Module.vb.txt
> 
> - Strategy 3 - trace in single threaded mode and you'll find what to do.
> 
>     Public Function SQLTDSGetDataTablePaged(ByVal startRow As Integer, ByVal
> pageSize As Integer, ByRef adapter As SqlDataAdapter, ByRef dataSet As
> DataSet) As DataTable
> ...
> 
> And
> 
>     Public Sub SQLTDSUpdate(ByVal adapter As SqlDataAdapter, ByVal dataSet
> As DataSet)
> ....
> 
> 
> HTH,
> 
> --
> Shamil
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Monday, April 28, 2008 10:50 PM
> To: VBA
> Subject: [dba-VB] ADO.Net Update SQL Server
> 
> Does ANYBODY know how to write a table back out to SQL Server?
> 
> All of my books show cute little examples of building up a data table 
> manually then BINDING it to a control or whatever.  I am not finding 
> anything with code example that shows how to WRITE changed data back to 
> SQL Server.
> 
> I have managed to load a data table, write changes to the rows and 
> now... I have to write it back out to SQL Server.  The table has the PK, 
> NameToSplit, LName, FName etc etc.  What am I missing?
> 
> I desperately need help (yea, yea, not MENTAL help) and I need it 
> quickly.  I have been trying to do this last piece for DAYS and I am not 
> succeeding.
>