ADO.NET

[Guest]

I am using OleDbDataAdapter in order to fill a data table and update it.
The fill method is working fine but the update does not work, and I dont
know why.
Here is my code:

Dim data_table As DataTable
Dim conn As OleDbConnection
Dim data_adapter As OleDbDataAdapter
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
test_db.mdb")
conn.Open()
data_adapter = New OleDbDataAdapter("select * from test_table", conn)
data_table = New DataTable
data_adapter.Fill(data_table)
Me.DataGridView1.DataSource = data_table

'do some changes on the datagridview

Dim temp_val As String = data_table.Rows(row_index)(col_index)
data_adapter.UpdateCommand = New OleDbCommand("update test_table set minimum
= '" & temp_val & "' where growth_day = 1", conn)
data_adapter.Update(data_table)
conn.Close()

minimum and growth_day are fields in my database wich growth_day is the key.
I think the code is ok but the database stays the same and does not accept
the changes. Can someone please help me and tell why does the update is not
working?
I am using VS2005.

 

[The Frog]

Hi Gad,

I think the issue is that you are trying to update the data_table like
it is a real table. In ADO.Net it is similar to a real table but it
has different objects that you need to work with. The easiest way I
find to think about it is to break the problem up into parts like
this:

'set up the needed variables

Dim DS as NEW DataSet
Dim strConn as String
Dim strSQL as String
Dim Conn as OLEDBConnection
Dim DA as DataAdapter
Dim CMD as NEW OleDbCommand

'set up the strings

strSQL = "Select * from test_Table"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= test_db.mdb"

'set up the connection object

Conn = new OleDBConnection(strConn)

'set up the command object

CMD = NEW OleDbCommand (strSQL,Conn)

'set up the DataAdapter, set the Select command, and map the incoming
data to
'a datatable and name it

DA = NEW OleDbDataAdapter
DA.TableMappings.Add("Table", "NameOfTable")
DA.SelectCommand = CMD
DA.Fill(DS)
'next is optional and will retrieve schema data for the datatable
'DA.FillSchema(DS,schematype.source,"NameOfTable")

'Now you have a dataset, filled datatable, and a data adapter, BUT you
dont
'have the necessary updating logic to put data back to the underlying
table in
'the database yet. Since this is a single table query it is probably
easiest to use
'a command builder object to do the work for you.

DIM cb as NEW OleDb.OleDbCommandBuilder(DA)

'your updating logic is not set for update, insert, and delete in the
data adapter

'now you need to modify a row in the datatable to alter any values you
want...
'ADD a row:

dim ROW as DataRow = ds.tables("NameOfTable").NewRow
ROW("ColumnName") = "Give it a value"
.....

'MODIFY an existing row:
dim ROW as DataRow
ROW = ds.tables("NameOfTable").rows.find("Primary Key Value")
IF ROW is nothing then
'what you want to do in case of failure
ELSE
ROW("ColumnName") = "New Value Goes Here"
.....
END IF

'DELETE a row
dim ROW as DataRow
ROW = ds.Tables("NameOfTable").rows.find("Primary Key Value")
ds.tables("NameOfTable").remove(ROW)

'OR

ds.Tables("NameOfTable").RemoveAt(rowIndexNumber) ' this is an integer
value

'now you can update your changes to the table in the database

da.update("NameOfTable")

I hope that this points you in the right direction. There is a
significant change with the ADO.Net tools when compared to the classic
ADO tools. The big one is that you must supply your own updating logic
(or use a command builder), and that all data is worked with
'offline'. The sample code above should get you on the right path. I
would also recommend a book Microsoft ADO.NET by Dave Sceppa
(Microsoft Press). I have found this to be the most useful thing on
the topic I have ever read. Lots of examples and nice clear
explanations.

Cheers

The Frog

 

[Guest]

Why do I need the command builder?

 

[Guest]

The update row still not working, only add and delete.

 

[Omar Abid]

The update row still not working, only add and delete.

The update is working, but it's lacking something
you must make 3 case :
1)when an added item
2)when a deleted item
3)when modified
you must detect those 3 case and then do the necessary to update
Now I don't remember exactly the code, I'll send it to you later

 

[The Frog]

Hi Gad,

Can you post your new modified code that includes the changes listed
above?
Are you trying to run this inside a transaction? Do you have other
users accessing the data at the same time (I am looking for
concurrency issues here)? Could you also please do the following:
- add a line of code after the commandbuilder object is created as
follows:
console.writeline(cb.getupdatecommand.commandtext)

and post that as well. I would like to see the updating logic that the
command builder has generated, perhaps it needs a little tweaking...

Cheers

The Frog

 

[Guest]

Hi

My application is very small and no other user is trying to access the data.
Here is the code i wrote:

Dim conn As OleDbConnection
Dim DA As New OleDbDataAdapter
Dim CMD As OleDbCommand
Dim DS As New DataSet

conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=test_db.mdb")
conn.Open()
CMD = New OleDbCommand("select * from test_table", conn)
DA.TableMappings.Add("test_table", "test_table")
DA.SelectCommand = CMD
DA.Fill(DS)
Me.DataGridView1.DataSource = DS.Tables(0)

Dim cb As New OleDb.OleDbCommandBuilder(DA)
MessageBox.Show(cb.GetUpdateCommand.CommandText)
Dim ROW As DataRow
ROW = DS.Tables(0).Rows(row_index)
ROW(col_index) = 15
DA.Update(DS.Tables(0))

Thank you very much for your help.

 

[The Frog]

Hi Gad,

I think I found the problem. The issue is that the tablemapping is
trying to map from "test_table" to "test_table". When a data adpater
fetches data from an underlying table it automatically calls that
table by the generic name "table". Could you try to change the
tablemappings line to this:

DA.TableMappings.Add("table", "test_table")

That should give you the correct mappings.

Next, I would establish the command builder object before binding the
data grid view. So in the end you code would look like this...

Dim conn As OleDbConnection
Dim DA As New OleDbDataAdapter
Dim CMD As OleDbCommand
Dim DS As New DataSet


conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=test_db.mdb")
CMD = New OleDbCommand("select * from test_table", conn)
DA.TableMappings.Add("test_table", "test_table")
DA.SelectCommand = CMD
DA.Fill(DS)

Dim cb As New OleDb.OleDbCommandBuilder(DA)

Me.DataGridView1.DataSource = DS.Tables(0)

Now I would make the modification code for the changes to the
DataRows.

I take it you are wanting to use the edits that are done in the data
grid view as the changes that you place into the database. If this is
so then what you need to do is, if I am not mistaken (MVP please feel
free to jump in here) simply call the update command at the
appropriate moment from either events occuring in the cells in the
data grid or from a button on the form.

The idea is that when the change is entered by the user, your code
captures the change when the user is finished, and then can call the
da.update("Test_Table") method from that change event.

I think that this should get you back on track. If you need help with
the data grid then you will find plenty of examples and problems in
this newsgroup. Simply search on DataGrid +Update and you will get
heaps of information. I think with these minor alterations your data
adapter is okay. By the way you dont need to "open" the connection
object, the data adapter will do that for you and close it again too!

Cheers

The Frog

 

[Omar Abid]

Hi Gad,

I think I found the problem. The issue is that the tablemapping is
trying to map from "test_table" to "test_table". When a data adpater
fetches data from an underlying table it automatically calls that
table by the generic name "table". Could you try to change the
tablemappings line to this:

DA.TableMappings.Add("table", "test_table")

That should give you the correct mappings.

Next, I would establish the command builder object before binding the
data grid view. So in the end you code would look like this...

Dim conn As OleDbConnection
Dim DA As New OleDbDataAdapter
Dim CMD As OleDbCommand
Dim DS As New DataSet

conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=test_db.mdb")
CMD = New OleDbCommand("select * from test_table", conn)
DA.TableMappings.Add("test_table", "test_table")
DA.SelectCommand = CMD
DA.Fill(DS)

Dim cb As New OleDb.OleDbCommandBuilder(DA)

Me.DataGridView1.DataSource = DS.Tables(0)

Now I would make the modification code for the changes to the
DataRows.

I take it you are wanting to use the edits that are done in the data
grid view as the changes that you place into the database. If this is
so then what you need to do is, if I am not mistaken (MVP please feel
free to jump in here) simply call the update command at the
appropriate moment from either events occuring in the cells in the
data grid or from a button on the form.

The idea is that when the change is entered by the user, your code
captures the change when the user is finished, and then can call the
da.update("Test_Table") method from that change event.

I think that this should get you back on track. If you need help with
the data grid then you will find plenty of examples and problems in
this newsgroup. Simply search on DataGrid +Update and you will get
heaps of information. I think with these minor alterations your data
adapter is okay. By the way you dont need to "open" the connection
object, the data adapter will do that for you and close it again too!

Cheers

The Frog

That's won't run it need to create a select command

 

[The Frog]

Hi Omar,

The select statement is located in the CMD object that is appended to
the DataAdapter object (DA). The update, insert, and delete commands
are (in this case) built by the commandbuilder object and are appended
to the DataAdapter object as part of the commandbuilder objects
constructor.

This code works fine.

Cheers

The Frog