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

Question

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?

Are you running your code in Access? If not, then your question is not an Acccess question at all, since all you're using is a Jet database. — David-W-Fenton
– David-W-Fenton, Commented Dec 5, 2008 at 3:53

Stefan · Accepted Answer · 2008-12-03 18:02:02Z

2

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)

edited Dec 3, 2008 at 18:02

answered Dec 3, 2008 at 15:15

Stefan

11.5k8 gold badges53 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

seanyboy Over a year ago

I needed to add "UserDS.User.AddUserRow(NewUser)" before the Update Line to make that work.

Siddharth Rout · Accepted Answer · 2012-06-10 03:13:26Z

1

how to insert the string in vb.net only insert in the integer my pgm is

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()

edited Jun 10, 2012 at 3:13

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Apr 8, 2009 at 11:35

priya

Comments

Joel Coehoorn · Accepted Answer · 2008-12-16 15:41:40Z

-1

Unfortunately, the world doesn't re-arrange itself to suit how you think it should work. If you want to work with a database, it's a good idea to get at least somewhat comfortable with SQL, as that is generally how it's done.

On the bright side, there is a whole category of products that do kind of what you want. You can look for an Object-Relational Mappings (ORM) tool like ActiveRecord, NHibernate, or LINQ. But still learn some SQL.

answered Dec 16, 2008 at 15:41

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related