HOWTO: Iterate thru dataset rows changing field values

[Dan Sikorsky]

How do you iterate thru a dataset to change money fields to a different
value?
Here's what I have. My dataset is filled directly from a stored procedure.

' Create Instance of Connection and Command Object

Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim myCommand As New SqlDataAdapter("OrdersList", myConnection)

' Mark the Command as a SPROC

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

' Create and Fill the DataSet

Dim myDataSet As New DataSet()

myCommand.Fill(myDataSet, "OrderList")

Dim drow As DataRow

For Each drow In myDataSet.Tables("OrderList").Rows

drow.BeginEdit()

drow.Item("OrderTotal") += 6

drow.EndEdit()

drow.AcceptChanges()

Next

 

[Bernie Yaeger]

Hi Dan,

There's one big problem with your code, if you wish to update the backend:
by using 'acceptchanges' you are essentially changing the row's state to
'unmodified'. If you then wish to update the back end, the changes will not
be seen.

Re your direct question - a money column is no different than any othe
column:

drow.("unitprice") += 6.95 ' I leave 'item' out, the default I think

HTH,

Bernie Yaeger

 

[Dan Sikorsky]

So, just comment out the .AcceptChanges, and leave everything else alone?

Have you a known working example I can see?

 

[Bernie Yaeger]

Hi Dan,

Here's a working example of such code - note that it does not contain the
update command but instead a commandbuilder object, which creates the update
commands dynamically. However, the commandbuilder object is only useable
for simpel. single table updates; if the update is more complicated, you
have to write your own update commands. If you need help with that, let me
know and I and others here can help you with that as well. NB: without
update commands (either written by you or generated by the commandbuilder)
the back end will not be updated.

Dim dahistd As New SqlDataAdapter("select * from histd where posstatus = 'T'
and billed <> 'Y'", oconn)

Dim dshistd As New DataSet("histd")

dahistd.Fill(dshistd, "histd")

Dim commandbuilder_histd As SqlCommandBuilder = New
SqlCommandBuilder(dahistd)

Dim irow as Datarow

For Each irow In dshistd.Tables(0).Rows

arrayseekp(0) = irow("bipad")

arrayseekp(1) = irow("issuecode")

priceval = 99.99

' I actually get the price in a convoluted function; no need to recreate
that here

row("uprice") = priceval

irow("ptype") = "US PRICE"

irow("billed") = "I"

Next

Try

dahistd.Update(dshistd, "histd")

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

HTH,

Bernie Yaeger

 

[Dan Sikorsky]

I don't want to update the database, I just want to update the dataset that
is in a dataview that is bound to a datagrid that is display on the screen
to the user. What has happened here is that the store source code doesn't
have Handling charges; the closest thing it does have is a shipping charge
but that is not tied to the order ... it is tied to each line item in the
order. So, I just need to change what appears on the screen, not the
underlying database.

How do I put some debug 'print' statements in the .vb file to see whats in
the dataset and print to the screen? The Response object doesn't seem to
have scope in the .vb file.

 

[Bernie Yaeger]

Hi Dan,

This is an asp .net app? (response object). In any case, yes, your code
should be fine - you're getting it originally from an sp; then you update it
with the data from the order table. Your code looks fine and once updated,
you should then make it the datasource of the grid. I see no problem in
this case. Does it not appear correctly?

HTH,

Bernie

 

[Dan Sikorsky]

No, it's not changing the screen display. I wanted to print some debug
statements to make sure on changing the right .aspx.vb or .vb file, but I
can't seem to do that. Any ideas on how to response.write to the page?

 

[Bernie Yaeger]

Hi Dan,

I must be unclear about something:

response.write(irow("custid"))

should print directly to the screen.

Bernie