Re: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSE

Bob,

Thanks for the tip on using Response.Write to help troubleshoot the syntax
error. As it turns out the <input name=""> didn't match my Request.Form("")
and so I was ending up with a null value.

As for the dynamic sql, I am a beginner who is learning by reading the code
of the person who created pages before me. I have read your Google Groups
page so lets see if I have this right. Instead of putting my queries into
ASP as:

Set qryAddProj = WERCConnect.Execute("INSERT Project(Proj_PI_Fk)
VALUES("&vPIPK&")")

I would create my query in Access and save it as qryAddProj. Then I would
run the query from ASP like this:

cn.qryAddProj "&vPIPK&"

I am understanding this correctly? Is there a reference or "bible" you
would recommnd for a beginner?

Thanks for your help,

Donovan

"Bob Barrows [MVP]" wrote:

Donovan wrote:
I am receiving the above message when I try to insert data from a
form on a previous page. Here are the details to minimize confusion.

I grab a number from a form using:

vPIPK = Request.Form("pipk")

I then make my database connection:

Set WERCConnect = Server.CreateObject("ADODB.Connection")
WERCConnect.Open "Products"

I then execute my insert statement:

Set qryAddProj = WERCConnect.Execute("INSERT Project(Proj_PI_Fk)
VALUES("&vPIPK&")")

This statement causes ADO to create a recordset to receive the results
of the query being executed and assign that recordset to your qryAddProj
variable. I'm sure you can see this is a waste of time and resources,
can't you? You should use the ExecuteOptions argument of the Execute
method to tell ADO that the query won't return any records so it should
execute it WITHOUT creating a recordset. You do this by using 128 in the
argument. 128 is enumerated by the adExecuteNoRecords constant. Also,
you should always specify the command type so that ADO does not have to
waste time guessing what the command type is. You do this by using 1,
which is enumerated by adCmdText, to tell ADO that you are passing a
string containing a sql statement to be executed. You combine these two
arguments by adding them to get 129. I will show below the corrected
statement.


I get this error when I try to open the page (from the linking page of
course):

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT
INTO statement.

/newsite/forms/add_projects.asp, line 92

I have another Insert query on a different page that is more complex
and does not have this problem.

Table name = Project
Field name = Proj_PI_Fk

This shouldn't be a reserved word issue, and because the data is a
simple whole number there shouldn't be any character violations
either. This has been driving me crazy for a day and half now. Any
help is greatly appreciated.

Thanks.

The first step in debugging a query's syntax error is to look at the
query! The only way to do that is to assign the string to a variable and
response.write that variable:

sql = "INSERT ... "

'***for debugging - comment out when problem solved***
response.write sql
response.end
'*************************************************************

WERCConnect.Execute(sql,,129)

Run the page and look at the sql statement. Does it look correct? If
not, make the correction. If the error is not obvious, open your
database in Access, create a query in Design View, switch to SQL View,
paste in the statement and try to run it. You will sometimes get a more
helpfule error message from Access. If you're still baffled, post the
statement back here and we'll look at it.

Bob Barrows

PS. Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.



.

Relevant Pages

  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)
  • Re: creating and executing queries in VBA.
    ... Execute it into what? ... You can't just "execute" a select query. ... When you execute sql it has to go into a report, or a form or a record set. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Loading recordset at runtime
    ... difference in loading time for the entire recordset and the default 10 ... query or build the SQL string in VBA. ... Ah, ok, then use the forms "filter" option. ...
    (microsoft.public.access.forms)