INSERT data into Access from VB .NET

.net , db Montreal, Canada
  • 9 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
    Try
               Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestInsertDataVB\DataBaseTestInsertVB.mdb;")
               Mycn.Open()
               
               SQLstr = "INSERT INTO TableTestEmployes VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & _
               TextBox3.Text & "','" & ComboBox1.Text & "')"

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

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

    end Sub
  • 9 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


    Code:


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



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

  • 9 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.

    Code:
    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.

    Code:
    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.

    Code:
           Catch ex As Exception

    If DEBUG Then


               'Will be compiled into a debug build.
               MessageBox.Show(ex.ToString())

    Else


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

    End If


           Finally
               Mycn.Close()
           End Try

  • 9 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


    then


    mycn.open()


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


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



    dr=dataset.tables("testemployee").newrow




    dr(0)=textbox1.text
    dr(1)=textbox2.text
    dr(3)=textbox3.text
    dr(4)=combobox1.selecteditem


    dataset.tables("testemployee").rows().add(dr)


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



    dim cb as new oledbcommndbuilder(adp)


    adp.fill(ds)
    adp.update(ds,"testemployee")


    mycn.close()

  • 9 years ago

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

  • 6 years ago

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

  • 6 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

    k

    Have a nice day

     

     

  • 4 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

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

    End Class

Post a reply

Enter your message below

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

Contribute

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.

“Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.” - Rich Cook