By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Syntax Error in SQL Statement

P: n/a
Hi. I am trying to run the following code when the user clicks a button,
but I am getting a syntax error in the SQL. I have a feeling it has to
do with brackets. Can anyone help?
here is the SQL(watch for word wrap:
StrSql = "insert into[Products](ProductName)" 'SQL string which executes
with the RunSQL statement
16980 StrSql = StrSql & "Values ('" & TxtProductName.Text &
"');"

thanks guys

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Hi Colin

I like to assign a variable that represents the value contained by a form
control.
Then I totally seperate any variables from the textual portion of the SQL
string when doing this kind of thing.

Assigning a variable to a form's control also allows you to inspect it's
contents while in break mode.
Set a breakpoint in your code, at say the "StrSql = " line . Then run the
procedure, and you'll see what I mean.
"Hover" your mouse cursor over the "MyProductName" variable, and it's
contents will displayed in a "tooltip"

I'd also suggest using the .Execute method ( including the dbFailOnError)
rather than RunSQL.
The problem with using RunSQL is that some (but not all) records will be
changed by the action query, until such time that an error occurs.
If an error occurs with RunSQL, you have to look through the table and
determine which records have (or have not) been changed.
The dbFailOnError is an "all-or-nothing" approach to updating records. If an
error should occur, all changes are "rolled back".

All of the above is *my understanding* of how things work.
That being said, try it like this:

*************************************************
....
Dim MyDB as DAO.Database
Set MyDB = CurrentDb

Dim MyProductName As String
MyProductName = Me![TxtProductName]

StrSql = ""
SrtSql = StrSql & "INSERT INTO [Products] (ProductName) " 'You were missing
a space between [Products] and (ProductName)
SrtSql = StrSql & "VALUES ("
SrtSql = StrSql & MyProductName 'No quotes here, JUST the variable name.
SrtSql = StrSql & ");"

Debug.Print StrSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight the
offending portion

MyDB.Execute StrSql, dbFailOnEror

Set MyDB = Nothing
....
*************************************************

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"ColinWard" <je*********@hotmail.com> wrote in message
news:40**********************@news.newsgroups.ws.. .
Hi. I am trying to run the following code when the user clicks a button,
but I am getting a syntax error in the SQL. I have a feeling it has to
do with brackets. Can anyone help?
here is the SQL(watch for word wrap:
StrSql = "insert into[Products](ProductName)" 'SQL string which executes
with the RunSQL statement
16980 StrSql = StrSql & "Values ('" & TxtProductName.Text &
"');"

thanks guys

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #2

P: n/a
Thanks for your help don. I will try this on Monday. Of course you know
that this means that I will have to change all my runSQL statements
arrgh!!! LOL

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a

Hi Don,

This is what I currently have but I get a 'Mising Operator' error with
the last part of the SQL statement.

************************************************** ******
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim strSql As String
Dim MyProductName As String
MyProductName = Me![TxtProductName]
strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & MyProductName 'No quotes here, JUST the variable name.
strSql = strSql & ");"

Debug.Print strSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug
Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight
the
'offending portion

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

*********************************************

thanks Don

Colin
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a
Hi Colin,

Sorry, I forgot to "wrap" the "MyProductName" variable in quotation marks
....
(notice that I did that using the Chr$(34) substitute for the ["] character
in the revised code below.)

I was getting:
INSERT INTO [Products] (ProductName) VALUES(Sunlight Dishwash Soap);

where it should have been:
INSERT INTO [Products] (ProductName) VALUES("Sunlight Dishwash Soap");
********************* Revised Code *****************************
Private Sub cmdAddProduct_Click()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim strSql As String
Dim MyProductName As String
MyProductName = Me![TxtProductName]

strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & Chr$(34) & MyProductName & Chr$(34)
strSql = strSql & ");"

'Debug.Print strSql

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

End Sub
********************* End Revised Code ***********************

Regards,
Don
__________________________________________________ ___________

"ColinWard" <je*********@hotmail.com> wrote in message
news:40**********************@news.newsgroups.ws.. .

Hi Don,

This is what I currently have but I get a 'Mising Operator' error with
the last part of the SQL statement.

************************************************** ******
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim strSql As String
Dim MyProductName As String
MyProductName = Me![TxtProductName]
strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & MyProductName 'No quotes here, JUST the variable name.
strSql = strSql & ");"

Debug.Print strSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug
Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight
the
'offending portion

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

*********************************************

thanks Don

Colin
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #5

P: n/a
On 05 Jul 2004 17:36:41 GMT, ColinWard <je*********@hotmail.com>
wrote:

Hi Don,

This is what I currently have but I get a 'Mising Operator' error with
the last part of the SQL statement.

************************************************* *******
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim strSql As String
Dim MyProductName As String
MyProductName = Me![TxtProductName]
strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & MyProductName 'No quotes here, JUST the variable name.
strSql = strSql & ");"

Debug.Print strSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug
Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight
the
'offending portion

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

*********************************************

thanks Don

Colin


Colin, this should be all you need.
================
Dim strSql As String

strSql = "INSERT INTO [Products] (ProductName) VALUES('" _
& Me![TxtProductName] & "');"
'Still need to wrap a string with single quotes.

CurrentDb.Execute strSql, dbFailOnError
================

Notes:
You still need to wrap the string in single quotes, and if the Product
name has any quotes single or other, they will have to be managed.
BTW, the product usually has an ID number that is much easier to deal
with.

You don't really need an object variable to use the execute method -
just do it directly from CurrentDb - unless you need the object for
something else. Which you don't here.

For small strings like this I find that line continuation character is
much easier to read than continuous concatenating - and less typing.
My 0.02

- Jim
Nov 13 '05 #6

P: n/a
Hi Jim,

That "_" line-continuation character issue is definitely a matter of
personal preference then ...
I for one loathe and despise that stupid thing! <grin>

I admit that I must just be a very methodical thinker, and can only deal
with "sequential steps" while writing code.
For me, the way that the SQL strings "line up" with each other make it
easier for me to follow.
It also allows me to quicky and easily "inspect" the value of a variable (in
break mode), right inside of the SQL string.
It also all but eliminates those annoying "double-quote, single-quote,
double-quote" and the "Forms!MainForm.SubForm.Form!Controls.ControlN ame"
syntax nightmares.

As far as the "less typing" aspect goes, I have a little utility program
called SQL2VAR.EXE that does 99% of that repetitious typing for me!
I just paste in a sample SQL string from the SQL view of a query that I have
designed and tested, then hit the [Generate] button! (For short strings like
this one, though, I generally just "Copy/Paste" MySQL = MySQL & "" 4 or 5
times and then "fill in the blanks".)
After pasting the generated string inside my code window, I simply replace
the "literal" values with variables, and I'm "good to go".

I will agree with what you said about the CurrentDB thing, though. It saves
having to "Set" and "UnSet" the object. I'll likely adopt that method.
That's what I like about this forum ... you get to see a lot of alternative
methods, and learn a lot doing so.

There's my $0.02 worth. <grin>
Have a great day,
Don
"Jim Allensworth" <Ji****@NOTdatacentricsolutions.com> wrote in message
news:40***************@netnews.comcast.net...
On 05 Jul 2004 17:36:41 GMT, ColinWard <je*********@hotmail.com>
For small strings like this I find that line continuation character is
much easier to read than continuous concatenating - and less typing.
My 0.02

- Jim

Nov 13 '05 #7

P: n/a

Hi Don. This works great. I am now trying to modify it so that I can add
a second value (ProductDescription) to the same record but when I do it
as below I get an error saying that the number of values and the number
of destination fields are not the same.

Thanks Don

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #8

P: n/a
Hi Don I forgot to post the code tht i am trying to use for two values:
******CODE START**************************************
Private Sub cmdAddProduct_Click()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim strSql As String
Dim MyProductName As String
Dim MyProductDescription As String
MyProductName = Me![TxtProductName]
MyProductDescription = Me![txtProductDescription]

strSql = ""
strSql = strSql & "INSERT INTO [Products]
(ProductName,ProductDescription)"
strSql = strSql & "VALUES("
strSql = strSql & Chr$(34) & MyProductName & Chr$(34)
strSql = strSql & Chr$(34) & MyProductDescription & Chr$(34)
strSql = strSql & ");"
'Debug.Print strSql
MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing
End Sub
*******CODE END*****************************************

Thanks Don

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #9

P: n/a
Hi Don,

On Mon, 05 Jul 2004 19:56:15 GMT, "Don Leverton"
<le****************@telusplanet.net> wrote:
Hi Jim,

That "_" line-continuation character issue is definitely a matter of
personal preference then ...
I for one loathe and despise that stupid thing! <grin>
Personal preference, absolutely. So much of this stuff is.

I admit that I must just be a very methodical thinker, and can only deal
with "sequential steps" while writing code.
For me, the way that the SQL strings "line up" with each other make it
easier for me to follow. I use the ampersand to "line up" the continued string. When I see that
at the beginning of a line I immediately know is continued.
It also allows me to quicky and easily "inspect" the value of a variable (in
break mode), right inside of the SQL string. That can also be done with continuation.
It also all but eliminates those annoying "double-quote, single-quote,
double-quote" and the "Forms!MainForm.SubForm.Form!Controls.ControlN ame"
syntax nightmares.
AFAICS, those have to go somewhere. And if the text value has single
or double quotes then you still have to deal with those.
As far as the "less typing" aspect goes, I have a little utility program
called SQL2VAR.EXE that does 99% of that repetitious typing for me!
I just paste in a sample SQL string from the SQL view of a query that I have
designed and tested, then hit the [Generate] button! (For short strings like
this one, though, I generally just "Copy/Paste" MySQL = MySQL & "" 4 or 5
times and then "fill in the blanks".)
After pasting the generated string inside my code window, I simply replace
the "literal" values with variables, and I'm "good to go".

I will agree with what you said about the CurrentDB thing, though. It saves
having to "Set" and "UnSet" the object. I'll likely adopt that method.
That's what I like about this forum ... you get to see a lot of alternative
methods, and learn a lot doing so.
Absolutely! The exchange of ideas is invaluable.

There's my $0.02 worth. <grin>
Have a great day,
Don


I hope your day is good also,
- Jim
Nov 13 '05 #10

P: n/a
I still havent got this one sorted. I swear SQL is going to drive me
bonkers!!!!!

here is the SQL as it currently stands but Access is telling me there is
a syntax error. Anyway, here is the entire procedure. Everyone's help is
much appreciated.

*************CODE START************************************
Private Sub cmdAddProduct_Click()
'define local variables
Dim strSql As String, MyDB As DAO.Database, MyProductName As
String, MyProductDescription As String
'SQL string which gets executed with the MyDB.Execute strSql,
dbFailOnError statement.
15140 On Error GoTo cmdAddProduct_Click_Error
MyProductName = TxtProductName.Value
MyProductDescription = txtProductDescription.Value
Set MyDB = CurrentDb
15150 strSql = "insert into [Products]
(ProductName,ProductDescription)"
15160 strSql = strSql & " Values (" & MyProductName & ", " &
MyProductDescription & ");"
'makes sure that the user filled in both textboxes
15170 If (TxtProductName.Value) = "" Or IsNull(TxtProductName.Value)
Then
15180 FormattedMsgBox ("You must enter a value into this text
box."), vbInformation + vbOKOnly, "Information Missing"
15190 txtProductDescription.SetFocus
15200 Else
15210 If (txtProductDescription.Value) = "" Or
IsNull(txtProductDescription.Value) Then
15220 FormattedMsgBox ("You must enter a value into this
text box."), vbInformation + vbOKOnly, "Information Missing"
15230 Else 'run the SQL to insert the values
15240 DoCmd.SetWarnings False
15250 MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing
15260 DoCmd.SetWarnings True
15270 End If
15280 DoCmd.Close
15290 End If
15300 On Error GoTo 15140
15310 Exit Sub
cmdAddProduct_Click_Error:
15320 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cmdAddProduct_Click of VBA Document Form_Add Events Form"
End Sub
****************CODE END***********************************

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #11

P: n/a
> 15150 strSql = "insert into [Products]
(ProductName,ProductDescription)"
15160 strSql = strSql & " Values (" & MyProductName & ", " &
MyProductDescription & ");"

You still need to put the values in single parentheses, like this snippet:

Values ("' & MyProductName & '", "' & MyProductDescription & '")
Nov 13 '05 #12

P: n/a
I FINALLY GOT IT!!!

I still am not quite sure where I was going wrong but Oh well I got
it.!!!

thank you to everyone who helped with this one.

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.