Need to loop through dataset

[CMW]

In VB.NET I'm retrieving a single table from a SQL Server (tblformdata). I
need to search each record in the dataset to see if any data is missing from
the records (i.e., missing first name, missing last name, missing city,
missing state, missing zip, etc.). If I find that a column data is missing
from a record (i.e., last name), I'd like to update a column/field with a
code indicating data is missing (i.e., ErrorCode = 100). Later, another VB
app will only display records with the ErrorCode column set to 100 to allow
a user to correct the missing data.

Can anyone give me some sample code that shows how I might loop through each
record in the dataset to check for any missing column data?

Thank you.
CMW

[Jay B. Harlow [MVP - Outlook]]

CMW,
David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press
provides a number of examples on using DataSets & DataTables, plus a
complete reference for ADO.NET.

How is the first app going to get the DataSet to the second app?

Is ErrorCode on SQL Server (tblformdata) or is it just part of the DataSet?

I would create a DataAdapter to retrieve the data into the DataSet.

I would use a for each loop to check each row of the DataTable. Updating the
ErrorCode column as needed.

Within the other vb app, I would bind to a DataView with a filter criteria
selecting 'bad' records.

Hope this helps
Jay
"CMW" <co*************@hotmail.com> wrote in message
news:lfDlb.2242$e01.3206@attbi_s02...

In VB.NET I'm retrieving a single table from a SQL Server (tblformdata). I
need to search each record in the dataset to see if any data is missing from the records (i.e., missing first name, missing last name, missing city,
missing state, missing zip, etc.). If I find that a column data is missing
from a record (i.e., last name), I'd like to update a column/field with a
code indicating data is missing (i.e., ErrorCode = 100). Later, another VB
app will only display records with the ErrorCode column set to 100 to allow a user to correct the missing data.

Can anyone give me some sample code that shows how I might loop through each record in the dataset to check for any missing column data?

Thank you.
CMW

[Jay B. Harlow [MVP - Outlook]]

CMW,
Based on your email, I should offer a word of caution.

Remember with a DataSet, you are going to download the entire table into the
data set.

You can use a for each to check each row, when you update a row it will be
marked internally as such. You can add rows to the other table, they will
also be flagged as such.

At the end of the process you can use a couple DataAdapters to then update
the SQL Server with the modified data.

I would recommend you check out Sceppa's book.

As I really see you have three options here:
1. Create a SQL Stored Procedure with a cursor & do every thing in SQL
Server, you could even schedule this SP to run periodically, or at the end
of the initialize import.

2. Use a DataSet to process the data, which is what I outlined in my
original response.

3. Use only a DataCommand to create a DataReader to read one record at a
time, if any changes are needed use a second DataCommand to update that row.
A third DataCommand could be used to insert records into your second table.

Sceppa's book should help you decide between 2 & 3. From a performance point
of view 3 will probably be faster, however 2 may be more straight forward to
program. The advantage of 2 is you can save the DataSet as an XML file,
which the second VB app would load and make any changes as the second app
made changes it would then update the SQL Server, the first program would
not update the SQL Server in this case... In fact the original input could
simple put the data in a DataSet...

Hope this helps
Jay

"CMW" <co*************@hotmail.com> wrote in message
news:lfDlb.2242$e01.3206@attbi_s02...

In VB.NET I'm retrieving a single table from a SQL Server (tblformdata). I
need to search each record in the dataset to see if any data is missing from the records (i.e., missing first name, missing last name, missing city,
missing state, missing zip, etc.). If I find that a column data is missing
from a record (i.e., last name), I'd like to update a column/field with a
code indicating data is missing (i.e., ErrorCode = 100). Later, another VB
app will only display records with the ErrorCode column set to 100 to allow a user to correct the missing data.

Can anyone give me some sample code that shows how I might loop through each record in the dataset to check for any missing column data?

Thank you.
CMW

[Bernie Yaeger]

Hi CMW,

Here's some pretty simple code that loops through a dataset/datatable and
does some update work (oconn is the connection that has already been
established):
Dim dapubcb As New SqlDataAdapter("select * from pubcb where paidstat <>
'Y'", oconn)

Dim dspubcb As New DataSet("pubcb")

Dim commandbuilder_dspubcb As SqlCommandBuilder = New
SqlCommandBuilder(dapubcb)

' you must have either a commandbuilder or update commands to make changes
to the backend (SQL Server)

dapubcb.Fill(dspubcb, "pubcb")

Dim totcbcount As Double

Dim irow as Datarow

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

totcbcount += irow("cbamount")

irow("paidstat") = "Y"

irow("vflag") = "Z"

Next

Try

dapubcb.Update(dspubcb, "pubcb")

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

HTH,

Bernie Yaeger
"CMW" <co*************@hotmail.com> wrote in message
news:lfDlb.2242$e01.3206@attbi_s02...

In VB.NET I'm retrieving a single table from a SQL Server (tblformdata). I
need to search each record in the dataset to see if any data is missing from the records (i.e., missing first name, missing last name, missing city,
missing state, missing zip, etc.). If I find that a column data is missing
from a record (i.e., last name), I'd like to update a column/field with a
code indicating data is missing (i.e., ErrorCode = 100). Later, another VB
app will only display records with the ErrorCode column set to 100 to allow a user to correct the missing data.

Can anyone give me some sample code that shows how I might loop through each record in the dataset to check for any missing column data?

Thank you.
CMW

[Cor]

Hi CMW,

A dataset has tables, they work in the same way as every other table

So rought written
\\\\
dim i as integer
for i from 1 to dsCMW.tables(0).rows.count - 1
if dsCMW.tables(0).rows(i)("lastname") = dbnull.value then
dsCMW.tables(0).rows(i)("erroritem") = "100"
end if
next
///

I said rough written so if there is an error, don't blaim me?

Cor