INSERT data into Access from VB .NET

.net , db Montreal, Canada
  • 15 years ago
    I need help, I developed a simple Access database with some textBoxes and I create a VB .Net user interface.
    I can't INSERT data into the Table.  Can you INSERT data  without loading it ?  This is the code!
    I've just got the MessageBox that cannot insert the data....Thank you!

    Dim Mycn As OleDbConnection
    Dim Command As OleDbCommand
    Dim icount As Integer
    Dim SQLstr As String

    Private Sub ButtonAdd_Clic
               Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestInsertDataVB\DataBaseTestInsertVB.mdb;")
               SQLstr = "INSERT INTO TableTestEmployes VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & _
               TextBox3.Text & "','" & ComboBox1.Text & "')"

               Command = New OleDbCommand(SQLstr, Mycn)
               icount = cmd.ExecuteNonQuery

           Catch ex As Exception
               MessageBox.Show("could not insert record")
           End Try

    end Sub
  • 15 years ago

    First off, use the catch statment properly ie in the catch put this so you can find out the problem, this will help instantly solve the problem


          Catch ex As Exception
              MessageBox.Show(ex.Message & " - " & ex.Source)
          End Try

    also remeber when inserting numbers you dont need a ' either side only stings, let us know the error message now

  • 15 years ago

    When inserting a new row, technically it is not required in all cases but it is good practice to always include a field list as well as a value list, e.g.

    INSERT INTO Table1 (field1, field2, field3) VALUES (value1, value2, value3)
    Otherwise it is just assumed that the first value goes in the first column, the second value does in the second column, etc.  This method can be error-prone, so it is better to be explicit to be sure you are inserting the correct value into the correct column.

    I highly recommend using the String.Format method when building an SQL statement like this.  It is no more correct than the way you are doing it but it makes your code more readable, in my opinion.

    SQLstr = String.Format("INSERT INTO TableTestEmployes VALUES('{0}','{1}','{2}','{3}')", TextBox1.Text, TextBox2.Text, TextBox3.Text, ComboBox1.Text)

    The way you are using the Catch block is not necessarily wrong.  You should put a call to OleDbConnection.Open in a Try block and you probably don't want to give the user too technical a message if it fails.  You do want to get the technical message yourself while you're debugging though.  You might combine what you are already doing and what Rollershade suggests by using conditional compilation, which many people are unaware that VB.NET supports.  Also, you should close the connection in a Finally block, so that it gets done whether an exception is thrown or not.  If execution simply continues after the Try...Catch block anyway, the Finally block is unnecessary and you can just close the connection after the End Try.  The finally is only needed if one of either the Try or Catch block exits the current method.

           Catch ex As Exception

    If DEBUG Then

               'Will be compiled into a debug build.


               'Will be compiled into a release build.
               MessageBox.Show("The new row could not be inserted.")

    End If

           End Try

  • 15 years ago

    if u dont want to insert data using query then try this

    dim cmd as new oledbcommand
    dim adp as new oledbdataadaptor
    dim ds as new dataset
    'declare a new datarow
    dim dr as new datarow


    Cmd.Connection = mycn
    Cmd.CommandText = "select * from testemployee"
    Cmd.CommandType = CommandType.Text

    Adp.SelectCommand = Cmd
    Adp.Fill(Ds, "testemloyee")




    this will insert the data and then to save it into database try this code

    dim cb as new oledbcommndbuilder(adp)



  • 15 years ago

    Thank you guys...using the catch correctly I noticed that the path connecting to the dataBase was now I have full access to my table.

  • 12 years ago

    Also you can use OleDbException, which may give you better insight into actual sql errors.

  • 12 years ago

    Hai this is Shra1,

                            I had seen ur problem.The main problem is that ur using access database is it.the database will have keywords like 'id'.   I think the error "Syntax error in INSERT INTO Statement" is raised from the database.So,u check it once.It is not reteieved from .net.Try it once


    Have a nice day



  • 11 years ago

    here is working code

    Imports System.Data.OleDb Public Class Form1 Dim Mycn As OleDbConnection Dim Command As OleDbCommand Dim icount As Integer Dim SQLstr As String Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Emp.mdb;")
            SQLstr = "INSERT INTO Table1 VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"
            Command = New OleDbCommand(SQLstr, Mycn)
            icount = Command.ExecuteNonQuery
        Catch ex As Exception
        End Try
    End Sub

    End Class

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight.” - Bill Gates