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.