Updating Source Database in ADO.NET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm learning VB.NET and am trying to code a simple form to read and update an
Access Database. I can connect to the database, read a row into a
dataset/table and bind it to textboxes on a form. But I can't get any
changes to the textboxes to update the original Access Database. I've have
read two books, I have looked at all the code samples and tried to emulate
them for my table but nothing works. I've tried the wizards.... I've tried
code... nada... I don't get any errors. Just nothing gets updated. So
given this table in an Access database
CUSTOMERS
CUSTID Char(6)
CUSTNAME Char(30)

how can I read a single row into a dataset (WHERE CUSTID = '000001'), bind
the fields to texboxes, and take any changes to the texboxes and update the
corresponding row in Access Table again. I don't even have to use
parameterized queiries.

What code would you use to do this.
 
You will want to read up on the dataadapter Update method, but here are the
general steps to add a new row (assuming you have an existing dataset):

Open the connection;
Create the new row (Dim drNew As DataRow);
Assign the textbox values to the columns in the new row;
Add the new row to the table in the dataset;
Create the dataadapter (Dim da As New OleDBDataAdapter);
Update the database table using the new row in the dataset table;
Close the connection
 
I've read scores of pages about the DataAdapter Update method and none of
them tell me in enough detail how to do this. Every example they give is
slightly different from the others.

Some of what you say I have already been doing.
But when you say:
"Add the new row to the table in the dataset;" --- What code specifically
does this? This may be my problem, in that I'm not adding the changes back
to the row in the Data Table before I run the update to the database.

Then what code specifically takes that updated row and adds it back to the
source database. I have a feeling that I am missing some piece of this as
well.

Thanks in advance.

Thanks
 
Here I'm creating a new row for the dtOilFields table, adding values to the
columns, then adding the row to the dataset table. Then I create the
dataadapter and submit the Insert to the database table using
OilFieldInsert, a function that provides all the parameters to the stored
procedure. (For clarity, I've eliminated connection open/close, null checks
and Try-Catch block.)

Dim drNew As DataRow
drNew = dsWinMatrix.Tables("dtOilFields").NewRow

drNew("OilCompanyID") = CInt(lstCompanies.SelectedValue)
drNew("OilFieldName") = txtFieldName.Text
drNew("OilFieldLocation") = txtLocation.Text

dsWinMatrix.Tables("dtOilFields").Rows.Add(drNew)

Dim da As New SqlDataAdapter
da.InsertCommand = OilFieldInsert()
da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters
da.Update(dsWinMatrix, "dtOilFields")
 
Back
Top