James Barash
James at fcidms.com
Mon Apr 28 16:02:31 CDT 2008
John:
If you already have a data adapter that you used to fill your data table,
you need to add an UpdateCommand to that data adapter and then use it to
update the datastore. Below is a very quick and dirty version from an
existing program I've used. It should at least give you a place to start. It
assumes you already have a data adapter named da, a data table named dt and
a connection named conn. You will have to change the table and field names
to match yours.
da.UpdateCommand = New SqlClient.SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.CommandType = CommandType.Text
da.UpdateCommand.CommandText = "Update tblValues set FieldValue=@FieldValue,
FieldDisplayValue = @FieldDisplayValue where FieldNumber=@FieldNumber"
da.UpdateCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FieldValue",
System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 0,
0, "FieldValue", System.Data.DataRowVersion.Current, False, Nothing, "", "",
""))
da.UpdateCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FieldDisplayValue",
System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 0,
0, "FieldDisplayValue", System.Data.DataRowVersion.Current, False,
Nothing, "", "", ""))
da.UpdateCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FieldNumber",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0,
"FieldNumber", System.Data.DataRowVersion.Current, False, Nothing, "",
"", ""))
da.Update(dt)
James Barash
-----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 4:18 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [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.
>
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com