syntax error in insert into statement

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • dhel
    New Member
    • Oct 2012
    • 1

    syntax error in insert into statement

    i cant add info to my ms access 2003 :( im using vb6.0 with listview ..please help .. i dont know the error .. when im trying to add a codes to add a info to my access .. the error occurs ..


    Code:
    Private Sub cmdAdd_Click()
      On Error GoTo errtrap
    
      If Me.txtName.Text = "" Or Me.txtAdd.Text = "" Or Me.Text1.Text = "" Or Me.Text2.Text = "" Or Me.Text3.Text = "" Or Me.Text4.Text = "" Or Me.Text5.Text = "" Or Me.Text6.Text = "" Or Me.Text7.Text = "" Or Me.Text8.Text = "" Or Me.txteduc(0).Text = "" Then
        MsgBox "All fields are required!", vbExclamation, "Error"
        Exit Sub
      End If
      
      Call dbConnect
        Conn.Execute "Insert into tbl_info(info_name,info_address,membership,birthdate,age,sex,status,religion,dialect,studying,education) values('" & Me.txtName.Text & "','" & Me.txtAdd.Text & "','" & Me.Text1.Text & "','" & Me.Text2.Text & "','" & Me.Text3.Text & "','" & Me.Text4.Text & "','" & Me.Text5.Text & "','" & Me.Text6.Text & "','" & Me.Text7.Text & "','" & Me.Text8.Text & "','" & Me.txteduc(0).Text & "' )"
      Conn.Close
      Set Conn = Nothing
      Unload Me
      Call loadNew
      
      Exit Sub
    errtrap:
      Select Case Err.Number
        Case -2147467259
          MsgBox "The name already exists in the database", vbCritical, "Error"
      
        Case Else
          MsgBox Err.Description, vbCritical, "The system encountered an error"
      End Select
    End Sub
    
    Public Sub loadNew()
      frmLoading.Show
      frmLoading.lblSub.Caption = "Saving your entry...."
      With viewlist.ListView.ListItems
        Call dbConnect
        SQL = "SELECT tbl_info.* FROM tbl_info order by id_info asc;"
        RS.Open SQL, Conn, adOpenDynamic
          If Not RS.EOF Then
            RS.MoveLast
            Set Item = .Add(, , RS!id_info)
              Item.SubItems(1) = RS!info_name
              Item.SubItems(2) = RS!info_address
              Item.SubItems(3) = RS!membership
              Item.SubItems(4) = RS!birthdate
              Item.SubItems(5) = RS!age
              Item.SubItems(6) = RS!sex
              Item.SubItems(7) = RS!Status
              Item.SubItems(8) = RS!religion
              Item.SubItems(9) = RS!dialect
              Item.SubItems(10) = RS!studying
              Item.SubItems(11) = RS!education
              
              Item.EnsureVisible
          End If
        RS.Close
        Conn.Close
        Set Conn = Nothing
      End With
      Unload frmLoading
      MsgBox "New entry was added successfully", vbInformation, "Save"
    End Sub
    
    Private Sub Label10_Click()
    loans.Show
    frmNew.Hide
    End Sub
    
    Private Sub Label11_Click()
    viewlist.Show
    frmNew.Hide
    End Sub
    
    Private Sub Label4_Click(Index As Integer)
    home.Show
    frmNew.Hide
    End Sub
    
    Private Sub Label9_Click()
    Form1.Show
    frmNew.Hide
    
    End Sub
    Last edited by Rabbit; Oct 6 '12, 09:53 PM. Reason: Please use code tags when posting code.
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    Please use code tags when posting code.

    We would need to know the table structure and the SQL string that's trying to be inserted.

    Comment

    • zmbd
      Recognized Expert Moderator Expert
      • Mar 2012
      • 5501

      #3
      additionally... what error are you refering to and on what line of the code.

      Comment

      • VanessaMeacham
        New Member
        • Sep 2012
        • 31

        #4
        Hi ! your error in age field of insert Query syntax.
        if you take name is numeric data type than you have to write " & Me.Text3.Text & ". i think it is problem in your query.

        Comment

        • Killer42
          Recognized Expert Expert
          • Oct 2006
          • 8429

          #5
          If I read that correctly, the significant point that VanessaMeacham was making is that you are putting quotes around a numeric value in the query. You should only have quotes around strings.
          So for example rather than this:
          Insert into tbl_info(info_n ame, age) values('Fred Nurk', '18')
          you would have this:
          Insert into tbl_info(info_n ame, age) values('Fred Nurk', 18)

          Comment

          Working...