Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL syntax error - help needed

I'm trying to use SQL to access an MS Access DB and insert values in a table, and get a "syntax error in INSERT INTO" message, and I can't figure out what's wrong with my syntax... This is what I try to do:

Dim conAcdb As ADODB.Connection
Dim cmdAcdb As ADODB.Command

Set conAcdb = New ADODB.Connection

With conAcdb
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:DevSourcemiscatlas.mdb;Persist Security Info=False"
.Open
End With

Set cmdAcdb = New ADODB.Command
With cmdAcdb
Set .ActiveConnection = conAcdb
End With

[Some code to calculate values]

With cmdAcdb
.CommandText = " INSERT INTO Places (Place, AltName, LngDeg, LngMin, LngDir, LatDeg, LatMin, LatDir, Country, Zone) VALUES ('" & sName & "', '" & sAltName & "', '" & sLngDeg & "', '" & sLngMin & "', '" & sLngDir & "', '" & sLatDeg & "', '" & sLatMin & "', '" & sLatDir & "', '" & sCountry & "', '" & sZone & "'); "
.CommandType = adCmdText
.Execute
End With

I even try to hard code values like this:

.CommandText = "INSERT INTO Places (Place, AltName, LngDeg, LngMin, LngDir, LatDeg, LatMin, LatDir, Country, Zone) VALUES ('AachimAllgaeu', 'AachimAllgu', '9', '58', 'E', '47', '31', 'N', 'D', '4')"

and still get same error, also experimenting leaving out the ; at end of commandtext. All fields in db are text. I'm not an expert on SQL but afaik I can't see anything wrong with the syntax.

Thanks for any input,

Joakim

Comments

  • infidelinfidel Member Posts: 2,900
    : I'm trying to use SQL to access an MS Access DB and insert values in a table, and get a "syntax error in INSERT INTO" message, and I can't figure out what's wrong with my syntax... This is what I try to do:
    :
    : Dim conAcdb As ADODB.Connection
    : Dim cmdAcdb As ADODB.Command
    :
    : Set conAcdb = New ADODB.Connection
    :
    : With conAcdb
    : .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:DevSourcemiscatlas.mdb;Persist Security Info=False"
    : .Open
    : End With
    :
    : Set cmdAcdb = New ADODB.Command
    : With cmdAcdb
    : Set .ActiveConnection = conAcdb
    : End With
    :
    : [Some code to calculate values]
    :
    : With cmdAcdb
    : .CommandText = " INSERT INTO Places (Place, AltName, LngDeg, LngMin, LngDir, LatDeg, LatMin, LatDir, Country, Zone) VALUES ('" & sName & "', '" & sAltName & "', '" & sLngDeg & "', '" & sLngMin & "', '" & sLngDir & "', '" & sLatDeg & "', '" & sLatMin & "', '" & sLatDir & "', '" & sCountry & "', '" & sZone & "'); "
    : .CommandType = adCmdText
    : .Execute
    : End With
    :
    : I even try to hard code values like this:
    :
    : .CommandText = "INSERT INTO Places (Place, AltName, LngDeg, LngMin, LngDir, LatDeg, LatMin, LatDir, Country, Zone) VALUES ('AachimAllgaeu', 'AachimAllgu', '9', '58', 'E', '47', '31', 'N', 'D', '4')"
    :
    : and still get same error, also experimenting leaving out the ; at end of commandtext. All fields in db are text. I'm not an expert on SQL but afaik I can't see anything wrong with the syntax.


    Does the syntax error give any clues? What exactly is the error message?
  • joakimjoakim Member Posts: 2
    : : I'm trying to use SQL to access an MS Access DB and insert values in a table, and get a "syntax error in INSERT INTO" message, and I can't figure out what's wrong with my syntax... This is what I try to do:
    [snip snap]
    : : and still get same error, also experimenting leaving out the ; at end of commandtext. All fields in db are text. I'm not an expert on SQL but afaik I can't see anything wrong with the syntax.
    :
    :
    : Does the syntax error give any clues? What exactly is the error message?
    :

    The exact error VB 6 gives are:
    Run-time error '-2147217900(80040e14)':
    Syntax error in INSERT INTO statement.

    which of course isn't very helpful, but I managed to figure it out. For some reason table and field names have to be surounded by []'s when using the INSERT INTO, which don't seam to be the case with a SELECT statement. Anyhow I'm happy I can move on now as this have kept me hold for 2 days... Light side of the coin is I found this place :-)

    Joakim
  • Andre YoungAndre Young USAMember Posts: 0

    _____ { http://forcoder.org } free ebooks and video tutorials about < Java, Swift, Go, C++, C, Scratch, PHP, JavaScript, Assembly, C#, Visual Basic .NET, R, Ruby, Objective-C, MATLAB, Python, Delphi, Perl, Visual Basic, PL/SQL LabVIEW, Scheme, FoxPro, Transact-SQL, COBOL, Hack, Logo, Clojure, F#, Alice, Rust, Scala, Crystal, Lua, Bash, ABAP, ML, D, SAS, Ada, Erlang, Lisp, VBScript, Prolog, Apex, Julia, Dart, Kotlin, Awk, Fortran /> _

Sign In or Register to comment.