TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Jonathan Trahair
NA
36
0
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.
Reply
Answers (
3
)
Delete Record from Combox
DATA ADAPTER