Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Question Unanswered: MS ACCESS - WHY INSERT SQL QUERY don't work

    Pls see the code below:
    I have tried to run the SQL insert query here but it gives me error.
    Syntax error in INSERT INTO statement.
    err.Number -2147217900
    I have No idea WHY? All the fields are of text type.
    I am using MS ACCESS (XP)



    'variables
    Dim conntest As New ADODB.Connection

    Private Sub Command1_Click()
    Dim sqlJobOrder, cntr%

    Dim rstest As New ADODB.Recordset

    'openDb conntest
    conntest.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;"
    conntest.Open

    rstest.Open "select * from test", conntest, adOpenDynamic, adLockOptimistic

    For i = 0 To 1000
    'sqlJobOrder = ""
    cntr = findMaxJobOrderID
    With rstest
    .AddNew
    .Fields("name") = "name" & cntr
    .Fields("age") = cntr
    .Fields("count") = i
    .Update
    End With
    'sqlJobOrder = "INSERT INTO test(name,age,count)" & _
    " VALUES('name', '" & cntr & "','" & i & "')"

    'conntest.Execute sqlJobOrder
    'Debug.Print sqlJobOrder
    Next

    If rstest.State = adStateOpen Then rstest.Close
    Set rstest = Nothing
    Set conntest = Nothing
    MsgBox "done"

    End Sub

    Private Function findMaxJobOrderID()
    Dim rsmaxID As ADODB.Recordset
    Set rsmaxID = New ADODB.Recordset
    rsmaxID.Open "select max(id) from test", conntest, adOpenDynamic, adLockOptimistic

    If IsNull(rsmaxID(0)) Then
    maxnum = 0
    Else
    maxnum = rsmaxID(0)
    End If

    '' To start the job order from 14000
    If Val(maxnum) < 14000 Then
    maxnum = maxnum + 14000
    End If

    findMaxJobOrderID = maxnum + 1
    If rsmaxID.State = adStateOpen Then rsmaxID.Close
    Set rsmaxID = Nothing
    End Function

  2. #2
    Join Date
    Aug 2009
    Posts
    2

    Hi

    To solve the issue/error (Syntax error in insert query) occuring while inserting a row into Access database table using VB.net form please follow the below steps.

    Its better if you use parameters which will avoid syntax errors.I have faced the same issue, so to solve the issue i used parameters.Please have a look at the below example which explains you in a better way

    Sample code:

    Public Class frmSeo

    Dim Connection As OleDbConnection
    Dim Command As OleDbCommand

    Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

    Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=(Mdb file path);")

    Connection.Open()

    Command = New OleDbCommand("INSERT INTO table name (column1,colume2) VALUES (@variable1,@varaible2)", Connection)

    Command.Parameters.Add("@variable1", OleDbType.varchar).Value = (value to be inserted)
    Command.Parameters.Add("@variable2", OleDbType.VarChar).Value = (value to be inserted)

    Connection.Close()

    End sub

    End class


    In the above code, i am trying to insert a row under click event.

    Try to use the code according to your requirement, i hope it will solve your problem.

    Cheers
    Rohini...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That looks like a good solution. A word to the wise though - check the date on threads - that question is over six years old I'm afraid
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2009
    Posts
    2

    Hi

    Thanks for your information

    To be frank i didn't check the thread date.
    I am facing the same issue and when i search for a solution, i found the same question but no answer...so updated the solution for the thread.

    Cheers

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No harm there, just don't expect a response from the OP ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •