Simple Data UPdae Question

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

Guest

I have a very simple Access data base. No new info is going to be
added...the only changes are to existing fields. I have 2 tables both with
one row each. I'm using vb.net.

I can easily retrieve the data via "Reader"...but how to I update for changes?

Thanks
 
What would that line(s) of code look like...

I have something that starts like this:

Dim AdConn As New OleDbConnection(CX)
AdConn.Open()

SQL = "Select * from Admin"
Dim AdCommand As New OleDbCommand(SQL, AdConn)
Dim AdDataSet As New DataSet

Dim AdAdapter As New OleDbDataAdapter(SQL, AdConn)
AdAdapter.Fill(AdDataSet, "Admin")

I'm not sure what do to after this...the field in question is titled
password...

Thanks
 
First, create a strongly-typed dataset using the DataSet Designer.
You can click on Data/AddNewDataSource and follow the wizard.

This generates a bunch of code for you behind the scenes, including
the data adapter. If you want to look at the code, select your
project, then hit the icon ShowAllFiles in the Solution Explorer.
Then look at the xxDataSet.Designer.vb code, where xxDataSet
is whatever you called your dataset when you created it.

My strongly-typed dataset is called CarriersDataSet. I set this to
point to my a table in my database called "Carriers". The following
code opens the dataset, reads through each row and changes the
field "c_Carrier" in each row, then after doing that, saves the changes.

Dim ds As CarriersDataSet = New CarriersDataSet
Dim adapter As CarriersTableAdapter = New CarriersTableAdapter
adapter.Fill(ds.Carriers)
Dim dt As DataTable = DirectCast(ds.Tables(0), DataTable)
For Each dr As DataRow In ds.Tables(0).Rows
Dim Carrier As String = dr.Item("c_Carrier").ToString
Console.WriteLine(String.Format("Carrier = '{0}'", Carrier))
Carrier &= "_x"
dr.Item("c_carrier") = Carrier 'replace the item in the table
Next
adapter.Update(ds) 'update all changes

If you want to do it the hard way, i.e. typing in all the code yourself,
you're on the right track, but you have to write your own update
command objects. Here's the same code as above, the hard way,
but without the update commands.

Dim ConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=yourdatabase.mdb" & _
";Persist Security Info=False;Jet OLEDB"
Dim ds2 As DataSet = New DataSet
Dim conn As OleDbConnection = New OleDbConnection(ConnectionString)
conn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("select * from Carriers", conn)
Dim adapter2 As OleDbDataAdapter = New OleDbDataAdapter(cmd)
adapter2.Fill(ds2, "Carriers")
For Each dr As DataRow In ds2.Tables(0).Rows
Dim Carrier As String = dr.Item("c_Carrier").ToString
Console.WriteLine(String.Format("Carrier = '{0}'", Carrier))
Next
conn.Close()
conn = Nothing

Good luck.
Robin S.
----------------------------------
 
Arne,

If you want to read using a Reader, than you can use the
command.ExecuteNonQuery to update the database.

Be sure that you do using your transact SQL either in a SP or just as text
the proper concurrency checking.

I hope this helps,

Cor
 
Back
Top