473,372 Members | 917 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

HOWTO: Iterate thru dataset rows changing field values

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

Nov 20 '05 #1
7 5237
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

Nov 20 '05 #2
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


Nov 20 '05 #3
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

Nov 20 '05 #4
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


Nov 20 '05 #5
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



Nov 20 '05 #6
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
>
>
>



Nov 20 '05 #7
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
> >
> >
> >
>
>



Nov 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Bill C. | last post by:
Hi, I've got a simple console app that just reads an XML file into a DataSet then prints out a description of each table in the DataSet, including column names and row values for each column. ...
1
by: STeve | last post by:
Hi guys, I was wondering how I would go about creating a string array from a single column from a dataset. What I was thinking was iterate through each row in the dataset, finding out the size...
3
by: Dmitry Karneev | last post by:
Hi! I have a rather interesting problem. I have a dataset that contatin data from database. I need to clone this dataset with data but all id's in tables must be NULL and all DataRowStates must...
8
by: drg | last post by:
I have a DataSet with a DateTime field. I am using an EventRow r, to update it with values from a TextBox in a DataGrid. Suppose a DataGrid in edit mode had a field with value: 7/7/2005. Then the...
4
by: Antonio Budano | last post by:
Hi there, How can I iterate through collections starting not from the first item but from an offset? I need to check if a dataset contains records that have some field values that can conflict...
2
by: martin | last post by:
hi, I am using vb.net. I have wrote a data access class and one of my methods returns a dataset. I would like to iterate through this, although this is proving problematic for me. Can...
2
by: Lacka | last post by:
Hi, If I have this sequence: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("DB")) Dim dbCommand As...
4
by: dtblankenship | last post by:
Hello everyone, I know this question has been asked many times in the forums, and after spending a few days reading, I am still confused as to the answer. I have a ListBox (lstBox),...
5
by: Roy Lawson | last post by:
I am having no problems connecting to a DB, creating a DataAdapter, and creating a dataset...and connecting to the data. Using the builtin data objects to do all this. My only problem now is...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.