How would I insert data into an Access table using vb.net?
I want to insert a new row into an Access database. I'm looking at doing something like:
oConnection = new Connection("connectionstring") oTable = oCennection.table("Orders") oRow = oTable.NewRow oRow.field("OrderNo")=21 oRow.field("Customer") = "ABC001" oTable.insert
Which seems to be a sensible way of doing things to me.
However, All examples I look for on the net seem to insert data by building SQL statements, or by creating a "SELECT * From ...", and then using this to create a multitude of objects, one of which appears to allow you to ... - populate an array with the current contents of the table. - insert a new row into this array. - update the database with the changes to the array.
What's the easiest way of using vb.net to insert data into an Access database? Is there a method I can use that is similar to my pCode above?
This is one way:
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;") cn.Open() str = "insert into table1 values(21,'ABC001')" cmd = New OleDbCommand(str, cn) cmd.ExecuteNonQuery
I would make a dataset, add a tableadapter connected to the Access database, then let the tableadapter create update/delete/modify for me. Then you just could do like this (assuming your database has a usertable and you mapped that up in the dataset):
Dim UserDS As New UserDS Dim UserDA As New UserDSTableAdapters.UsersTableAdapter Dim NewUser As UserDS.UsersRow = UserDS.Users.NewUsersRow NewUser.UserName = "Stefan" NewUser.LastName = "Karlsson" UserDS.User.AddUserRow(NewUser) UserDA.Update(UserDS.Users)