Announcement

Collapse
No announcement yet.

Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement



    Hi,
    I am having issues with the following code, it is returning "Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement" Error, I am trying to Insert data entered into a form (which contains textboxes and comboboxes ) to Access database which is having both text and numerical fields, following is the code for the form( Im a beginner to this so the code is a simple one, please help)
    Code:
    Private Sub fesbmt_Click()
    Dim res As VbMsgBoxResult
    Dim en, ef, md, fe, loo, pr, ms, intr, rp, pb As String
    Dim s2, p2, t2, e2, m2 As Integer
    If cboen.Value = "" Or cboef.Value = "" Or ComboBox3.Value = "" Or ComboBox6.Value = "" Or s1.Value = "" Or p1.Value = "" Or t1.Value = "" Or e1.Value = "" Or m1.Value = "" Or loocb.Value = "" Or practn.Value = "" Or mstrcb.Value = "" Or intrvl.Value = "" Or respp.Value = "" Or prby.Value = "" Then
    res = MsgBox("All fields are required!", vbOKOnly)
    If res = vbOK Then Exit Sub
    End If
    If s1.Value > 4 Or s1.Value < 0 Or p1.Value > 4 Or p1.Value < 0 Or t1.Value > 4 Or t1.Value < 0 Or e1.Value > 4 Or e1.Value < 0 Or m1.Value > 4 Or m1.Value < 0 Then
    res = MsgBox("Warning!-S,P,E,M values should be 1,2 or 3!", vbOKOnly)
    If res = vbOK Then Exit Sub
    End If
    en = cboen.Text
    ef = cboef.Text
    md = ComboBox3.Text
    fe = ComboBox6.Text
    s2 = s1.Value
    p2 = p1.Value
    t2 = t1.Value
    e2 = e1.Value
    m2 = m1.Value
    loo = loocb.Text
    pr = practn.Text
    ms = mstrcb.Text
    intr = intrvl.Text
    rp = respp.Text
    pb = prby.Text
    Dim Com As New ADODB.Connection
    Dim Constr As String
    Dim Sql As String
    Constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\fmeca1.mdb;User Id=Admin;Password=;"
    Com.Open Constr
    Sql = "Insert into fmea(Eqname, eqfunc, eqflmd, fncfe, s, p, t, e, m,locc, pract, maintstr, interval, resp, prepby) values ('" & en & "', '" & ef & "', '" & md & "', '" & fe & "', s2, p2, t2, e2, m2, '" & loo & "', '" & pr & "', '" & ms & "', '" & intr & "', '" & rp & "', '" & pb & "')"
    Com.Execute Sql
    Com.Close
    Set Com = Nothing
    MsgBox "Success!", vbInformation
    ComboBox6.Text = ""
    s1.Value = ""
    p1.Value = ""
    t1.Value = ""
    e1.Value = ""
    m1.Value = ""
    loocb.Text = ""
    practn.Text = ""
    mstrcb.Text = ""
    intrvl.Text = ""
    respp.Text = ""
    prby.Text = ""
    On Error Resume Next
    End Sub
    Thanx in advance,
    Seth
    Last edited by sbv; June 5th, 2012, 15:55.

  • #2
    Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

    You're trying to insert string values into numeric fields....

    See s2 & p2 (and others) in the SQL statement.

    Comment


    • #3
      Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

      Thank you for reply,
      yes, i tried everything like plain s2, p2 still dont work, and even tried inserting delimiters ('" &) but still getting same error,
      can you please provide a sample code for this?

      Comment


      • #4
        Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

        If no-one else comes in on this, will do later - using a mobile phone at the moment.
        Last edited by cytop; June 5th, 2012, 16:30.

        Comment


        • #5
          Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

          Code:
              Sql = "Insert into fmea(Eqname, eqfunc, eqflmd, fncfe, s, p, t, e, m,locc, pract, maintstr, interval, resp, prepby) values ('" & en & "', '" & ef & "', '" & md & "', '" & fe & "'," &  s2 & "," &  p2 & "," &  t2 & "," &  e2 & "," &  m2 & ", '" & loo & "', '" & pr & "', '" & ms & "', '" & intr & "', '" & rp & "', '" & pb & "')"

          Totally untested as I do not have access to your data table. The only changes were to the numeric variables (removed the ' delimiters), the remainder seems to be correct. If it still errors, attach an anonymous/cut-down copy of the table this is supposed to insert into.

          Comment


          • #6
            Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

            I tried your code, but still no luck , it is returning the same error, i'm attaching a cutdown access database containing table,

            Comment


            • #7
              Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

              Waiting on the table...

              Comment


              • #8
                Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

                Originally posted by cytop View Post
                Waiting on the table...
                I was trying to upload but was not working, I have uploaded the file to skydrive the following is the link
                http://sdrv.ms/JKqZot

                Comment


                • #9
                  Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

                  You cannot insert null values into numeric fields, you must edit your SQL statement to pass 0 for those fields or remove them from the SQL statement.

                  Also, it seems there may be a mismatch between the number of fields and the number of values in the SQL statement.

                  I'll leave you to investigate further.

                  Comment


                  • #10


                    Re: Runtime error'-2147217900(80040e14)':Syntax Error in INSERT INTO Statement

                    Originally posted by cytop View Post
                    You cannot insert null values into numeric fields, you must edit your SQL statement to pass 0 for those fields or remove them from the SQL statement.

                    Also, it seems there may be a mismatch between the number of fields and the number of values in the SQL statement.

                    I'll leave you to investigate further.
                    Thank you for your input,
                    The default value for numerical fields are set to 0 in access table
                    After two days of banging head on the desk, I got the solution to the problem
                    The problem was actually traced back to name of a field in access table,
                    I had a field in access table called "interval", I just changed it to "intrval" and now it is working!
                    the SQL statement was mis interpreted at this point.

                    Comment

                    Working...
                    X