ADO.NET - This can't be right!

  • Thread starter Thread starter Edbone
  • Start date Start date
E

Edbone

Hello all. I am new to VB.Net, but have been porgramming in VB for a
few years. I am trying to migrate to VB.Net, and until I hit ADO.Net,
I was pretty impressed. I don't think I am getting this right. From
what I have read, in order to update a database, using changed data
from a datagrid, a DataAdapter, and a DataSet you have to write a
small program. I have never liked the performance of the data
controls in VB, so I am not using the DataAdapter in VB.Net, although
they may be faster. What I guess I am asking is this, is there an
easier way? This is where I am at now...

Dim strConn, strSQL As String

strConn = "Provider=Advantage OLE DB Provider;Data
Source=w2k3srv1\databases\ads\csgdata ;Advantage Server
Type=ADS_REMOTE_SERVER"

strSQL = "Select * from collectorinfo order by username"
da = New OleDbDataAdapter(strSQL, strConn)
ds = New DataSet

da.Fill(ds, "CollectorInfo")

DataGrid1.DataSource = ds
DataGrid1.DataMember = "CollectorInfo"

No problem, and to change the data in the dataset to correspond to
changes in the datagrid I am doing this.

If ds.HasChanges Then ds.AcceptChanges()

Again, no problem, the dataset is updated, but how do I get these
changes to the DB table without writing SQL statements? Looking at
the code the DataAdapter control created, I have to ask - does all
that need to be done just to update a field in a table.

Thanks for reading, and if you do, replying :)

-Eddie
 
Yes, you do have to write the update functionality yourself and yes, the DA
control can do simple forms of this for yoiu automatically.

But to guard against concurrency, you are better off writing the update
statements yourself.
 
If you used the IDE to build your DataAdapter, it would have generated the
Select Statement, Insert Statement, Update Statement and Delete Statement.
You have the opportunity to not generate the Insert or the Update or the
Delete.

Assuming that you generated these statement, you can then use the
DataAdapter.Update() method to update the database.

Is this what you were looking for?
 
Hi,

Edbone said:
Hello all. I am new to VB.Net, but have been porgramming in VB for a
few years. I am trying to migrate to VB.Net, and until I hit ADO.Net,
I was pretty impressed. I don't think I am getting this right. From
what I have read, in order to update a database, using changed data
from a datagrid, a DataAdapter, and a DataSet you have to write a
small program. I have never liked the performance of the data
controls in VB, so I am not using the DataAdapter in VB.Net, although
they may be faster. What I guess I am asking is this, is there an
easier way? This is where I am at now...

Dim strConn, strSQL As String

strConn = "Provider=Advantage OLE DB Provider;Data
Source=w2k3srv1\databases\ads\csgdata ;Advantage Server
Type=ADS_REMOTE_SERVER"

strSQL = "Select * from collectorinfo order by username"
da = New OleDbDataAdapter(strSQL, strConn)
ds = New DataSet

da.Fill(ds, "CollectorInfo")

DataGrid1.DataSource = ds
DataGrid1.DataMember = "CollectorInfo"

No problem, and to change the data in the dataset to correspond to
changes in the datagrid I am doing this.

If ds.HasChanges Then ds.AcceptChanges()
Click to expand...
Again, no problem, the dataset is updated, but how do I get these
changes to the DB table without writing SQL statements?
Click to expand...

The dataset is not updated by calling AcceptChanges. Only the RowState for
every row is updated to Original.
AcceptChanges is meant only to reset the row state after successful update
to database.

Looking at
the code the DataAdapter control created, I have to ask - does all
that need to be done just to update a field in a table.
Click to expand...

As others replied, using Update method and corresponding commands is the
easiest way even for controlling concurrency.
 
Hi Edbone,

If you are not using the IDE to build your OledbAdapter, have then a look
for.

OledbCommandbuilder.
Although some of us here do not like it, is it a very simple way to do what
you ask
it builds for you the Update, Insert and Delete statemens from the Select.

And to overcome direct all problems, have a look at
dataset.haschanges
dataset.getchanges
dataset.acceptchanges (purpose as Miha told you)


I hope this helps?

Cor
 
Edbone,

If you really don't want to write the access code you can look at
OLEDBCommandBuilder. It has issues, but if your goal is not writing the SQL
code, that's the tool MS gave us.
 
Yes and no, thanks. I was just making sure that was I was reading was
correct. I was a little suprised to see the amount of code needed to
update a table. I guess, like others (I hope), I just took ADO for
granted. I understand ADO.NET does not automatically insert update
logic when updateing a table, I just didn;t know what went in to it.

Thanks again for the reply!

-Eddie
 
OK than, I'll look in to the CommandBuilder. Modifying generated code
seems like it would be a lot easier than writing it from scratch (like
not having to type in all the field names)

Thank you all for your replys. I think this answers my questions.

-Eddie
 
Be aware that the CommandBuilder only works well with very simple updates.
You may find that it is, in fact, better to write the statement from
scratch.
 
You must use this line of command in you source code

OleDbCommandBuilder cb = new OleDbCommandBuilder(dataAdaptername);

then you can save you change to your database.
This is because ADO.NET use the DataAdapter command for update, delete
or add modify to you record and when you create a datadapter the only
property you define is SelectCommand property.
With CommandBuilder you define the other property that are
updateCommand, deleteCommand and the property for add record (i dont
remember name)...
 
Back
Top