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("C onnectionString"))

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
--
Dan Sikorsky, MSCS BSCE BAB

[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" <ds****@gte.net> wrote in message
news:Od**************@TK2MSFTNGP10.phx.gbl...

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("C onnectionString"))

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
--
Dan Sikorsky, MSCS BSCE BAB

[Dan Sikorsky]

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

Have you a known working example I can see?

--
Dan Sikorsky, MSCS BSCE BAB
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP12.phx.gbl...

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" <ds****@gte.net> wrote in message
news:Od**************@TK2MSFTNGP10.phx.gbl...

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("C onnectionString"))

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
--
Dan Sikorsky, MSCS BSCE BAB

[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" <ds****@gte.net> wrote in message
news:Od**************@TK2MSFTNGP10.phx.gbl...

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("C onnectionString"))

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
--
Dan Sikorsky, MSCS BSCE BAB

[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.

--
Dan Sikorsky, MSCS BSCE BAB
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eS**************@TK2MSFTNGP11.phx.gbl...

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" <ds****@gte.net> wrote in message
news:Od**************@TK2MSFTNGP10.phx.gbl...

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("C onnectionString"))

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
--
Dan Sikorsky, MSCS BSCE BAB

[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" <ds****@gte.net> wrote in message
news:Oi**************@TK2MSFTNGP11.phx.gbl...

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.

--
Dan Sikorsky, MSCS BSCE BAB
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eS**************@TK2MSFTNGP11.phx.gbl...

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" <ds****@gte.net> wrote in message
news:Od**************@TK2MSFTNGP10.phx.gbl...

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("C onnectionString"))

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
--
Dan Sikorsky, MSCS BSCE BAB

[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?

--
Dan Sikorsky, MSCS BSCE BAB
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:ew**************@tk2msftngp13.phx.gbl...

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" <ds****@gte.net> wrote in message
news:Oi**************@TK2MSFTNGP11.phx.gbl...

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.

--
Dan Sikorsky, MSCS BSCE BAB
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eS**************@TK2MSFTNGP11.phx.gbl...

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" <ds****@gte.net> wrote in message
news:Od**************@TK2MSFTNGP10.phx.gbl...
> 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("C onnectionString"))
>
> 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
>
>
> --
> Dan Sikorsky, MSCS BSCE BAB
>
>
>

[Bernie Yaeger]

Hi Dan,

I must be unclear about something:

response.write(irow("custid"))

should print directly to the screen.

Bernie

"Dan Sikorsky" <ds****@gte.net> wrote in message
news:O9**************@TK2MSFTNGP12.phx.gbl...

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?

--
Dan Sikorsky, MSCS BSCE BAB
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:ew**************@tk2msftngp13.phx.gbl...

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" <ds****@gte.net> wrote in message
news:Oi**************@TK2MSFTNGP11.phx.gbl...

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.

--
Dan Sikorsky, MSCS BSCE BAB
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eS**************@TK2MSFTNGP11.phx.gbl...
> 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" <ds****@gte.net> wrote in message
> news:Od**************@TK2MSFTNGP10.phx.gbl...
> > 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("C onnectionString")) > >
> > 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
> >
> >
> > --
> > Dan Sikorsky, MSCS BSCE BAB
> >
> >
> >
>
>