Problems with ADO.Net Updating

[djpaz25]

I'm a VB6 programmer comfortable with ADO who is trying to use ADO.Net. I'm using an Access database. I have a Dataset defined that matches the structure of an Access table. My dataset has only a single record in it, and I populate the fields in the dataset programmatically. So far, so good.

 

It's now time to store those values in the database. I know the value of the key field, which is unique. So one option is to create a DataCommand and build the CommandText in the form:

 

Update Table Set Field1 = Value, Field2 = Value Where KeyField = Value

 

and do an ExecuteNonQuery

 

There are two problems with that. One is that I have about 30 fields, so that is going to be quite an SQL string. Plus some of the fields are null, which complicates things. Secondly, I have a dataset already defined, so I thought using a DataAdapter would be easier.

 

I've been messing around with the DataAdapter's UpdateCommand, but I'm not sure how to use that with the dataset. I can set the UpdateCommand to

 

Update Table Set Field1 = ?, Field2 = ? where KeyField = ?

 

but then I (apparantly) have to define a parameter collection that includes all 30 parameters, set their types, and then copy the values from the dataset to the parameters. Is there some easier way? Can the parameter collection be defined automagically from the dataset? What form should the UpdateCommand's CommandText take?

 

And on the subject of nulls (or DBNulls), how can I set the value of a field so that its value in the database is null? When defining a dataset, does a particular property need to be set to enable null values for a field?

 

ADO recordsets sure seemed simpler than these disconnected ADO.Net datasets.

 

Thanks for any help.

[georgepatotk]

Let me show you an example for adding new record and editing existing record using dataadapter, dataset, and datarow

 

'Adding new record

Dim dr As DataRow = ds.tbl.NewRow

dr("Name") = txtName.Text

dr("Age") = txtAge.Text

ds.Tables("tbl").Rows.Add(dr)

da.Update(.ds)

 

'Editing Existing record

Dim pos As Integer

pos = me.BindingContext(ds, "tbl").Position

Dim dr As DataRow = ds.tbl.Rows(pos)

dr("Name") = txtName.Text

dr("Age") = txtAge.Text

da.Update(ds)

 

kindly update me whether it helps.

[djpaz25]

Thank you for taking the time to answer. That helps, but my real problem is in defining the UpdateCommand for the DataAdapter. How do I do that when I am potentially updating dozens of fields? And what if some of the fields are Null?

[davidh]

I have been using the SQLCommandBuilder to build the UpdateCommand, but with somewhat mixed results, so I wouldn't mind knowing how I should *really* be doing it too!! I've got a sub that updates two tables, and in this code the first table doesn't get updated (even though the dataset has the new data in it) and the second table does. Any ideas??

 

       Dim build As New SqlCommandBuilder(daProduct)
       'Update data
       daProduct.Update(DsProduct1, "tblProduct")
       build = New SqlCommandBuilder(daResults)
       daResults.Update(DsProduct1, "tblResultsDescription")