Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    3

    Lightbulb Unanswered: Insert into Access table

    Hello :
    I am testing the Insert statemet as follow:
    "INSERT INTO eststutbl (Estuid,estuname,estumname,estulname,estugender," &
    "estudob,estugrade,estuschool,estuphone1,estuphone 2,estuaddress1,estucity,estustate,Estuzip)" &
    " Values(" & "253253122" & "," & "MATIASA" & "," & "H" &
    "," & "HUNDERSA" & "," & "M" & "," & "12/24/1960" &
    "," & "11" & "," & "722" & "," & "303-632-9999" &
    "," & "303-524-9878" & "," & "2222 NE " & "," & "MIAMI" &
    "," & "FL" & "," & "33145" & ")"

    at command.ExecuteNonQuery() I get the error message :


    ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '2222 NE'.

    I checked the Syntax and could not find the mistake. Could you help me?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this an access problem or a .NET problem?

    my first guess is that you shouldn't be using the command.ExecuteNonQuery method but another method entirely
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    3

    Lightbulb

    It is an ACCESS Problem.
    When I Test with the Values:
    Values('2598533326','MATIASA','H','HUNDERSA','M',' 12/24/1960','11','722','303-632-9999','303-524-9878','2222 NE','MIAMI','FL','33145')" I got not Problem.

    When I use the Collection then I have the Problem:
    Values(" & estuinfoColl.Item(1) & "," & estuinfoColl.Item(2) & "," & estuinfoColl.Item(3) &
    "," & estuinfoColl.Item(4) & "," & estuinfoColl.Item(5) & "," & estuinfoColl.Item(6) &
    "," & estuinfoColl.Item(7) & "," & estuinfoColl.Item(8) & "," & estuinfoColl.Item(9) &
    "," & estuinfoColl.Item(10) & "," & estuinfoColl.Item(11) & "," & estuinfoColl.Item(12) &
    "," & estuinfoColl.Item(13) & "," & estuinfoColl.Item(14) & ")"

    In my Code:
    Using connection As New OdbcConnection(myconnectString)
    command.Connection = connection
    connection.Open()
    command.ExecuteNonQuery() ' Here is the Error
    End Using

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. In your first post, you did not used single quotes (') around text values:
    Code:
    " Values(" & "253253122" & "," & "MATIASA" & "," & "H" &
    "," & "HUNDERSA" & "," & "M" & "," & "12/24/1960" &
    "," & "11" & "," & "722" & "," & "303-632-9999" &
    "," & "303-524-9878" & "," & "2222 NE " & "," & "MIAMI" &
    "," & "FL" & "," & "33145" & ")"
    Which obviously result in errors when trying to execute the query.

    2. In your second post, you use single quotes is the first example, which is correct:
    Code:
    Values('2598533326','MATIASA','H','HUNDERSA','M',' 12/24/1960','11','722','303-632-9999','303-524-9878','2222 NE','MIAMI','FL','33145')"
    However, you're talking about (and using) a Collection. How was that Collection populated what does it contain? If you populated the Collection from controls (Textboxes for instances: estuinfoColl.Add Me.estuname), the collection holds the references to these controls, not their values (the default property cannot be used in this case) and you must explicitly use:
    Code:
    ... estuinfoColl.Item(4).Value ...
    Have a nice day!

  5. #5
    Join Date
    May 2012
    Posts
    3

    Solved

    I just want to thanks Healdem and Sinndho for your collaboration and my best regards for both of you.

    I got the solution thanks to you guys,,

    Estrada

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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