jwcolby
jwcolby at colbyconsulting.com
Mon Apr 28 17:12:37 CDT 2008
Thanks James.
I also found a piece of code that shows how to use a commandbuilder
object to generate those update commands automatically. At my skill
level, automatic is a good thing.
John W. Colby
www.ColbyConsulting.com
James Barash wrote:
> 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
>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>
>