473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Syntax Error in SQL Statement

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
12 2854
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
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

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
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
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
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

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
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
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
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
> 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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Robert Mark Bram | last post by:
Hi All! I have the following two methods in an asp/jscript page - my problem is that without the update statement there is no error, but with the update statement I get the following error: ...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
7
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a...
7
by: kosta | last post by:
hello! one of my forms communicates with a database, and is supposed to add a row to a table using an Insert statement... however, I get a 'oledb - syntax error' exception... I have double...
3
by: Nathan Sokalski | last post by:
When trying to submit data to an Access database using ASP.NET I recieve the following error: System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41...
1
by: amitbadgi | last post by:
HI i am getting the foll error while conv an asp application to asp.net Exception Details: System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement. Source Error: Line...
5
by: amitbadgi | last post by:
Hi guys, I am getting the following error in teh insert statement , I am converting this asp application to asp.net, here is teh error, Exception Details:...
7
by: Csaba Gabor | last post by:
I feel like it's the twilight zone here as several seemingly trivial questions are bugging me. The first of the following three lines is a syntax error, while the last one is the only one that...
12
by: Brad Baker | last post by:
I am trying to write a simple ASP.net/C# page which allows users to select some values and produce a report based on a SQL query. I have a self posting dropdown form which allows users to select...
1
by: ajos | last post by:
hi evrybdy, the problem is:- i had this running before in jsp but when i changed the jsp page using struts tags there occoured a problem... when i enter values in the 2 text boxes and click enter...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.