ado.net adding a new record without having a dataset

[vbDavidC]

I am adding a new record to a table via a dataset/adapter.

I have got the following to work for me but I am wondering if there is
a better way to do this.

I am having to have something in my DS before I can add records, since
DS does not exist. I'd rather not have a SELECT since I really don't
need any records in my dataset that I will be adding a new record to.

Dim dsNewRow As DataRow
Dim DS As New DataSet
Dim DA As New OleDb.OleDbDataAdapter

con.Open()
'part I have a question about
sql = "SELECT * FROM lookupTable WHERE Lname = ''"
DA = New OleDb.OleDbDataAdapter(sql, con)
DA.Fill(DS, "lookupTable")
'end
dsNewRow = DS.Tables("lookupTable").NewRow()
dsNewRow.Item(1) = "test string"
DS.Tables("lookupTable").Rows.Add(dsNewRow)
DA.Fill(DS, "lookupTable")


In my SQL command I have also used something like "SELECT * FROM
lookupTable WHERE LName = ''.

I am new to VB.net and want to make sure I learn the right method.

 

[RobinS]

Why do you think this is true:

I am having to have something in my DS before I can add records, since
DS does not exist.

Do you mean that you want to open a dataset just to insert records into a
table in your database, i.e. without selecting any records?

If you aren't going to use the table of inserted records, and you're just
going to use it to insert records, I would create an INSERT query and
execute it using ExecuteNonQuery for each record you want to insert.

Otherwise, your idea of selecting from the table with a primarykey = a
value it couldn't possibly be" will work fine. It's a bit tacky, but
effective.

If you choose the INSERT method, I'd check the performance. In my
experience working with Access with VB6 and ADO, if you are inserting a lot
of records (hundreds or thousands), this is slower than using a dataset. I
haven't tried it with ADO.Net, but I'd check it out if you were willing to
use INSERT queries.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
----------------------------------------------------------------