ADO to ADO.net

Jonathan Trahair

I am trying to learn how to replace ADO's recordsets. Suppose I have
SELECT * FROM ItemsSold WHERE ProductCode = 'T468' AND Colour = 'Blue'
In the table ItemsSold, there may possibly be a T468 in Blue. Or not. If there is I want to update the price to £19.50. If there isn't, I want to add a record. So far I have:
            mstrSQL = "SELECT * FROM ItemsSold WHERE ProductCode = 'T468' AND Colour = 'Blue'"
            objDs = New DataSet()
            ObjDa = New OleDbDataAdapter(mstrSQL, mstrOLEConnectionString)
            ObjDa.Fill(objDs, "xxx")
            For Each objRow In objDs.Tables(0).Rows
                objConn = New OleDb.OleDbConnection(mstrOLEConnectionString)
                objConn.Open()
                Dim cmd As New System.Data.OleDb.OleDbCommand("xxx", objConn)
                cmd.CommandTimeout = 60
                cmd.Connection = objConn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "UPDATE ItemsSold SET UnitPrice = '19.50' WHERE RecNo = " & objRow("RecNo")
                cmd.ExecuteScalar()
            Next

In other words I can cycle through the, um, recordset 'For Each objRow In objDs.Tables(0).Rows' and update each T468 in Blue record one by one using UPDATE. This already works OK.
However, in the old ADO recordset, there was a trap for where T468 in Blue didn't exist, so you can INSERT INTO.
Where does this happen in the new ADO.net code above?
Also, ObjDa.Fill(objDs, "xxx") and Dim cmd As New System.Data.OleDb.OleDbCommand("xxx", objConn) both contain the string xxx, placed there by me. It doesn't seem to relate to anything - should it?
Is this the best way of inserting and/or updating records? Some of my invoices contain 8-9000 items - should I 'Dim cmd As New etc' 9000 times?
Please help - thank you.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bechir Bejaoui18y ago
Generally to manage errors when the dataAdapter fills the data into the data set, you should use this way, insert those lines of code
simply embed this line of code within the code bloc
adapter.FillError+=new FillErrorEventHandler(adapter_FillError);
and then define an event handler method for the fill error as so
public void adapter_FillError(object sender, EventArgs e)
{
// TO DO implement the code to handle the error that occurs when the adapter fills data
}
Jonathan Trahair18y ago
Thanks for the reply.
If I have an invoice with '000s of items, each one from the ItemsSold table and each invoice item different, we'll have '000s of For loops each cycling round 000's of items.
Cycling through the entire table (say, 7,000 items x 7,000 invoice items) will take too long.

Is there a property of ObjDa which can be read if the dataset is empty, before the
'For Each objRow In objDs.Tables(0).Rows' hits Nothing and an error?
Thanks again.
Mahesh Chand18y ago
mstrSQL = "SELECT * FROM ItemsSold WHERE ProductCode = 'T468' AND Colour = 'Blue'"
            objDs = New DataSet()
            ObjDa = New OleDbDataAdapter(mstrSQL, mstrOLEConnectionString)
            ObjDa.Fill(objDs, "xxx")
            For Each objRow In objDs.Tables(0).Rows
                objConn = New OleDb.OleDbConnection(mstrOLEConnectionString)
                objConn.Open()
                Dim cmd As New System.Data.OleDb.OleDbCommand("xxx", objConn)
                cmd.CommandTimeout = 60
                cmd.Connection = objConn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "UPDATE ItemsSold SET UnitPrice = '19.50' WHERE RecNo = " & objRow("RecNo")
                cmd.ExecuteScalar()
            Next

First of you, you need to move everything which is common out of For Each loop:

objConn = New OleDb.OleDbConnection(mstrOLEConnectionString)
                objConn.Open()
                Dim cmd As New System.Data.OleDb.OleDbCommand("xxx", objConn)
                cmd.CommandTimeout = 60
                cmd.Connection = objConn
                cmd.CommandType = CommandType.Text

Now, in your mstrSQL, you are reading data where ProductCode = 'T468' AND Colour = 'Blue'". In other words, you are not getting any rows that does not match this criteria.

To get all rows, you need to remove WHERE clause from mstrSQL. Then when you loop through, you will compare something like this within your For each loop:

if objRow("ProductCode" = 'T468') AND objRow("Colour" = 'Blue') then
// UPDATE COMMAND

else

// INSERT COMMAND

endif