ADO to ADO.net

May 22 2008 4:29 AM
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.
 

Answers (3)