update an original database from ADO.net

cmcm

New member
Joined
Feb 18, 2015
Messages
2
Programming Experience
Beginner
Hello, I am starting to develop using VB.net with Microsoft Visual Studio 2008.

I have a database in Access and I?m doing a windows form application in Vb.net. From this application I want to do some operations over one table of the database.

I can to connect to the DB and using a datatable and a datadapter I catch the records from de table. After that, I add 3 new text type columns in the datatable and show it in a gridview.

I don?t have problems just here, but when I try to update the DB, the new 3 champs there aren?t in the DB design.

I don?t have any message of error. Please I need some help over that problems because I haven?t any idea about why the DB is not updated. I don?t know if there is another way to do the operations over the DB directly.

Thank

here the code:

VB.NET:
Private Sub Modifica_campos()

Dim sSel = " Select * from import;"
Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & txRuta.Text & ""

Try

dt = New DataTable
da = New OleDbDataAdapter(sSel, sConn)
da.Fill(dt)

dt.Columns.Add("CODIGO_PBC", Type.GetType("System.String"))
dt.Columns.Add("PRODUCT1", Type.GetType("System.String"))
dt.Columns.Add("LOTEXACT", Type.GetType("System.String"))

dt.AcceptChanges()
da.Update(dt)
dt.AcceptChanges()

Me.dgvContenido.DataSource = dt


Catch ex As Exception

    MessageBox.Show(ex.Message)

End Try

End Sub
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,400
Location
Sydney, Australia
Programming Experience
10+
If you want to modify data in a database table, i.e. insert, update and delete rows, then you can do that with a data adapter and a DataTable. You first call Fill on the data adapter and pass the DataTable as an argument. That will build the schema in the DataTable and populate it with data. You then modify the data in the DataTable and, finally, call Update on the data adapter and pass the DataTable as an argument to save the changes back to the database. There are no calls to AcceptChanges. I suggest that you do some research on what that method actually does.

If you want to modify the schema of a database table then you do NOT do it like that. To change the schema, you must execute an ALTER TABLE statement. You do that the same way you execute any other SQL statement that doesn't produce a result set: by calling ExecuteNonQuery on a command object. You should do some research on the Jet SQL ALTER TABLE statement to learn how to add columns.

That said, why exactly are you adding columns anyway? It's certainly not unheard of but it is rare that it would be done in an application and most times that I've seen someone say that they need to do it it has been misguided.
 

cmcm

New member
Joined
Feb 18, 2015
Messages
2
Programming Experience
Beginner
OK, I understand your explanation, so I will look for about the ALTER TABLE statement y I?ll modify the code without dt.acceptchanges. Thanks you for your reply.
 
Top Bottom