How to insert data into an Access table using vb.net?

Question

I want to insert a new row into an Access database. I am looking at 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 reasonable way to do something for me.

However, all the examples I'm looking for on the network seem to insert data by creating SQL queries or by creating "SELECT * From ..." and then using this to create many objects, one of which appears to allow you ...
- fill the array with the current contents of the table.
- insert a new line into this array.
- update the database with changes in the array.

What is the easiest way to use vb.net to insert data into an Access database?
Is it possible to use a method similar to my pCode above?

Answer 1

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, and then let the tableadapter create an update / delete / change for me. Then you can just do it (provided that your database has the ability to use, and you displayed it in the data set):

  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) 

Answer 2

how to insert a line in vb.net only insert into an integer my pgm

 Try cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\User\My Documents\db1.mdb;") cn.Open() str = "insert into table1 values(" & CInt(t2.Text) & ",'" & (t1.Text) & ") " cmd = New OleDbCommand(str, cn) icount = cmd.ExecuteNonQuery MessageBox.Show("stored") Catch End Try cn.Close() 

Answer 3

Unfortunately, the world does not reorganize itself as it seems to you that it should work. If you want to work with a database, it would be nice to at least work a little with SQL, as this is usually done.

On the bright side, there is a whole category of products that do what you want. You can find an object relational mapping (ORM) tool such as ActiveRecord, NHibernate, or LINQ. But still learn SQL.