Syntax error in INSERT statement
| Author |
Message |
|
101665...
#1 / 7
|
 Syntax error in INSERT statement When trying to execute the following code: If snap.EOF And snap.BOF Then 'empty recordset
Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top,
Width, Height) VALUES (" & prj & ", '" & frm & "', " & lft & ", " & tp
& ", " & wth & ", " & hgt & ")"
...by
Dtbs.Execute Q
I get this error:
"Syntax error in INSERT statement"
All values are of type single except 'prj' (integer) and 'frm' which
is a textstring.
The constraints are checked, the textfield is large enough.
Any help is more than welcom
Robert
|
| Thu, 04 Sep 2003 01:52:33 GMT |
|
 |
|
Glenn Robbin
#2 / 7
|
Syntax error in INSERT statement Robert: the SQL syntax is not valid for DAO. Try Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top,
Width, Height) Select " & prj & " As Prj, '" & frm & "' as Frm, " & lft &
" as Lft, " & tp
& " as Tp, " & wth & " as wth, " & hgt & " as hgt FROM Windows"
Best,
Glenn Robbins
Code Craft Corporation
http://www.codecrafter.com
Quote: > When trying to execute the following code: > If snap.EOF And snap.BOF Then 'empty recordset
> Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top,
> Width, Height) VALUES (" & prj & ", '" & frm & "', " & lft & ", " & tp
> & ", " & wth & ", " & hgt & ")"
> ...by
> Dtbs.Execute Q
> I get this error:
> "Syntax error in INSERT statement"
> All values are of type single except 'prj' (integer) and 'frm' which
> is a textstring.
> The constraints are checked, the textfield is large enough.
> Any help is more than welcom
> Robert
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
|
| Thu, 04 Sep 2003 02:06:54 GMT |
|
|
|
101665...
#3 / 7
|
Syntax error in INSERT statement On Sat, 17 Mar 2001 13:06:54 -0500, "Glenn Robbins" Quote:
>Robert: the SQL syntax is not valid for DAO. Try > Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top,
> Width, Height) Select " & prj & " As Prj, '" & frm & "' as Frm, " & lft &
>" as Lft, " & tp
> & " as Tp, " & wth & " as wth, " & hgt & " as hgt FROM Windows"
Glenn, thanks for your help, but unfortunatly I'm getting exactly the same error string when using your code but changing the column names: Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top, Width,
Height) Select " & prj & " As Project_ID, '" & frm & "' as Windowname,
& lft & " as Left, " & tp & " as Top, " & wth & " as Width, " & hgt &
" as Height FROM Windows"
I'm sorry providing no exact error statement as VB does only yield
this message: "Syntax error in INSERT statement"
If you have some other suggestions - please let me know.
Robert
|
| Fri, 05 Sep 2003 00:19:26 GMT |
|
|
|
horva
#4 / 7
|
Syntax error in INSERT statement Robert! Your SQL syntax IS valid for DAO.
I think the problem is in notification of type single. Depending on the
international settings a decimal point could be sometimes a point and
sometimes a comma. I assumme, your 'lft' variable contains for example
number 23,56 instead of 23.56 requesting by SQL statement.
Therefore you have to convert all these numbers to appropriate format.
Try this way:
Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top,
Width, Height) VALUES (" & prj & ", '" & frm & "', " &
Replace(lft,",",".") & ...
Matjaz
Quote:
> When trying to execute the following code: > If snap.EOF And snap.BOF Then 'empty recordset
> Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top,
> Width, Height) VALUES (" & prj & ", '" & frm & "', " & lft & ", " & tp
> & ", " & wth & ", " & hgt & ")"
> ...by
> Dtbs.Execute Q
> I get this error:
> "Syntax error in INSERT statement"
> All values are of type single except 'prj' (integer) and 'frm' which
> is a textstring.
> The constraints are checked, the textfield is large enough.
> Any help is more than welcom
> Robert
|
| Fri, 05 Sep 2003 19:43:27 GMT |
|
|
|
101665...
#5 / 7
|
Syntax error in INSERT statement
Quote: >Robert! >Your SQL syntax IS valid for DAO.
>I think the problem is in notification of type single. Depending on the
>international settings a decimal point could be sometimes a point and
>sometimes a comma. I assumme, your 'lft' variable contains for example
>number 23,56 instead of 23.56 requesting by SQL statement.
>Therefore you have to convert all these numbers to appropriate format.
>Try this way:
> Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top,
>Width, Height) VALUES (" & prj & ", '" & frm & "', " &
>Replace(lft,",",".") & ...
Matjaz, your remark was very helpful in a different case, but unfortunatly the error message does not change when converting comma -> dot. Are you sure V3B mapped the right error string, could it be something
else? What I have ckecked was the field contraints and the DB is
closed (not accessed by anyone else).
Robert
|
| Fri, 05 Sep 2003 23:24:24 GMT |
|
|
|
horva
#6 / 7
|
Syntax error in INSERT statement Robert! So...
The problem was not in notification of type single but somewhere else.
In my opinion it could be the right error string, because there are so many
hidden syntax error using SQL. But I could be wrong, of course.
If I get this kind of error, I usually do one of two things:
- set breakpoint to SQL string and then watch this string (variable Q) where
I usually find syntax error
- copy this watched SQL string (variable Q) into Access query, run this
query (or even better - see it in Datasheet View) and Access shows more
precisely where syntax error could be.
If no one of these two steps results in syntax error solution then I'm
usually in big trouble.
Matjaz
Quote:
> >Robert!
> >Your SQL syntax IS valid for DAO.
> >I think the problem is in notification of type single. Depending on the
> >international settings a decimal point could be sometimes a point and
> >sometimes a comma. I assumme, your 'lft' variable contains for example
> >number 23,56 instead of 23.56 requesting by SQL statement.
> >Therefore you have to convert all these numbers to appropriate format.
> >Try this way:
> > Q = "INSERT INTO Windows (Project_ID, Windowname, Left, Top,
> >Width, Height) VALUES (" & prj & ", '" & frm & "', " &
> >Replace(lft,",",".") & ...
> Matjaz, your remark was very helpful in a different case, but
> unfortunatly the error message does not change when converting comma
> -> dot.
> Are you sure V3B mapped the right error string, could it be something
> else? What I have ckecked was the field contraints and the DB is
> closed (not accessed by anyone else).
> Robert
|
| Sat, 06 Sep 2003 16:10:00 GMT |
|
|
|
101665...
#7 / 7
|
Syntax error in INSERT statement
Quote: >If I get this kind of error, I usually do one of two things:
>- set breakpoint to SQL string and then watch this string (variable Q) where
>I usually find syntax error
Sorry Matjaz, I cannot follow you in this. The error only turnes up in the execute statement and not before that. As the query is one string, a concatenated sequence of SQL statements, VB should be only able to throw out one error for the entire string. Quote: >- copy this watched SQL string (variable Q) into Access query, run this
>query (or even better - see it in Datasheet View) and Access shows more
This seems to be a good idea. As having less experience in handling Access, I hope I will be sucessful. Robert
|
| Sat, 06 Sep 2003 16:55:46 GMT |
|
| |
|
