INSERT data into Access from VB .NET

• • Reply

  15 years ago

  by enzom

  Enzo Calcagno

  Montreal, Canada, Canada

  Joined 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
  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
  Report abuse
• • Reply

  15 years ago

  by Rollershade

  Colin Harman MACITP

  West Sussex, England, Australia

  Joined 19 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

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 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.

  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

  Report abuse
• • Reply

  15 years ago

  by waqasali767

  waqasali767

  , United States

  Joined 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
  

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

  Report abuse
• • Reply

  15 years ago

  by enzom

  Enzo Calcagno

  Montreal, Canada, Canada

  Joined 15 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.

  Report abuse
• • Reply

  12 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago

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

  Report abuse
• • Reply

  12 years ago

  by oeerd7

  oeerd7

  , Canada

  Joined 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
  

  k

  Have a nice day

   

   

  Report abuse
• • Reply

  11 years ago

  by Archit

  Archit Warghane

  Singapore, Singapore

  Joined 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

      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

  Report abuse