SQL statement error

[Wayne...]

when I use the following select statment and code:


<% blah blah etc etc.....

Set rsCodevault = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM asp WHERE id = " & ID
rsCodevault.Open strSQL, adoCon
%>

I get the following error..........

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/ShowMessage.asp, line 9

strSQL = "SELECT * FROM asp WHERE id = " & ID;
---------------------------------------------^
I know it's something simple but I been awake 36 hours striaght and this
might jusdt enable me to get some sleep.
Thanks in advance

Wayne...

[Aaron [SQL Server MVP]]

VBScript does not use ; as a statement terminator.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Wayne..." <wayne@secretwebdesign.com> wrote in message
news:40be2f49@212.67.96.135...

> when I use the following select statment and code:
>
>
> <% blah blah etc etc.....
>
> Set rsCodevault = Server.CreateObject("ADODB.Recordset")
> strSQL = "SELECT * FROM asp WHERE id = " & ID
> rsCodevault.Open strSQL, adoCon
> %>
>
> I get the following error..........
>
> Microsoft VBScript compilation error '800a0401'
>
> Expected end of statement
>
> /ShowMessage.asp, line 9
>
> strSQL = "SELECT * FROM asp WHERE id = " & ID;
> ---------------------------------------------^
> I know it's something simple but I been awake 36 hours striaght and this
> might jusdt enable me to get some sleep.
> Thanks in advance
>
> Wayne...
>
>

[Wayne...]

ok I tried that now get this:
Error Type:
Provider (0x80004005)
Unspecified error
/codevault/ShowMessage.asp, line 7


wayne...


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uztFerNSEHA.2000@TK2MSFTNGP11.phx.gbl...

> VBScript does not use ; as a statement terminator.
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>
>
>
> "Wayne..." <wayne@secretwebdesign.com> wrote in message
> news:40be2f49@212.67.96.135...

> > when I use the following select statment and code:
> >
> >
> > <% blah blah etc etc.....
> >
> > Set rsCodevault = Server.CreateObject("ADODB.Recordset")
> > strSQL = "SELECT * FROM asp WHERE id = " & ID
> > rsCodevault.Open strSQL, adoCon
> > %>
> >
> > I get the following error..........
> >
> > Microsoft VBScript compilation error '800a0401'
> >
> > Expected end of statement
> >
> > /ShowMessage.asp, line 9
> >
> > strSQL = "SELECT * FROM asp WHERE id = " & ID;
> > ---------------------------------------------^
> > I know it's something simple but I been awake 36 hours striaght and this
> > might jusdt enable me to get some sleep.
> > Thanks in advance
> >
> > Wayne...
> >
> >

>
>

[Bob Barrows [MVP]]

Do a Response.Write strsql so we can see the query being sent to the
database.

Bob Barrows
Wayne... wrote:

> ok I tried that now get this:
> Error Type:
> Provider (0x80004005)
> Unspecified error
> /codevault/ShowMessage.asp, line 7
>
>
> wayne...
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:uztFerNSEHA.2000@TK2MSFTNGP11.phx.gbl...

>> VBScript does not use ; as a statement terminator.
>>
>> --
>> [url]http://www.aspfaq.com/[/url]
>> (Reverse address to reply.)
>>
>>
>>
>>
>> "Wayne..." <wayne@secretwebdesign.com> wrote in message
>> news:40be2f49@212.67.96.135...

>>> when I use the following select statment and code:
>>>
>>>
>>> <% blah blah etc etc.....
>>>
>>> Set rsCodevault = Server.CreateObject("ADODB.Recordset")
>>> strSQL = "SELECT * FROM asp WHERE id = " & ID
>>> rsCodevault.Open strSQL, adoCon
>>> %>
>>>
>>> I get the following error..........
>>>
>>> Microsoft VBScript compilation error '800a0401'
>>>
>>> Expected end of statement
>>>
>>> /ShowMessage.asp, line 9
>>>
>>> strSQL = "SELECT * FROM asp WHERE id = " & ID;
>>> ---------------------------------------------^
>>> I know it's something simple but I been awake 36 hours striaght and
>>> this might jusdt enable me to get some sleep.
>>> Thanks in advance
>>>
>>> Wayne...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Wayne...]

ok I've made a page with the following code: (this is gonnna be long but
thought it best to givce all the info, btw the code is in memo feild types)

HTML page [index2.asp]
-----------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Code Vault </title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" type="text/css" href="codevault.css">
</head>
<body>
<h1>Latest Code </h1>
<h2>ASP.net</h2>
<p>Title<br />
Author</p>
<h2>ASP</h2>
<p>
<!--#include file="asp2.asp" -->
</p>
<h2>CSS</h2>
<p>Title<br />
Author</p>
<h2>PHP</h2>
<p>Title<br />
Author</p>
<h2>Tips</h2>
<p>Title<br />
Author</p>
<h2>Layout</h2>
<p>Title<br />
Author</p>
</body>
</html>
-----------------
ASP2.asp code:

<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsCodevault 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("codevault.mdb")
'Create an ADO recordset object
Set rsCodevault = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
with
strSQL = "SELECT * FROM asp;"
'Open the recordset with the SQL query
rsCodevault.Open strSQL, adoCon
'Loop through the recordset


Do While not rsCodevault.EOF
'Write the HTML to display the contents of the recordset
'testing out an idea of putting the id into a variable and passiong it to
new page
Response.Write ("<br>")
Response.Write ("<b>Title: </b>")
response.write ("<A HREF='test.asp?ID=" & rsCodevault("id") & "'>" &
rsCodevault("title") & "</A>")
Response.Write ("<br>")
Response.Write ("<b>Author: </b>")
Response.Write (rsCodevault("author"))
Response.Write ("<br>")
'Move to the next record in the recordset
rsCodevault.MoveNext

Loop
'Reset server variables
rsCodevault.Close
Set rsCodevault = Nothing
Set adoCon = Nothing

%>



-----------------------
ShowMessage.asp code:

<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsCodevault 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &

Server.MapPath("codevault.mdb")
Set rsCodevault = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM asp WHERE id = " & ID
rsCodevault.Open strSQL, adoCon
%>
<HTML>
<HEAD>
<TITLE>Codevault </TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
<link href="codecss.css" rel="stylesheet" type="text/css">
</HEAD>
<BODY BGCOLOR = "#FFFFFF">
<CENTER><H2>ASP Code</H2></CENTER>
Response.Write strsql
%>
<%
rsCodevault.Close
Set rsCodevault = Nothing
Set adoCon = Nothing
%>
</body>
</html>

---------------------
this is the error I get:

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'id ='.

/test.asp, line 10



thanks for taking the time to help : )

Wayne...

[Bob Barrows [MVP]]

Give me a break. :-)
I asked you to show us the result of Response.Write strSQL and you give us
the whole flipping page? No, 3 pages! HTML and all??! And nowhere do I see
the result of Response.Write strSQL, unless it's hidden somewhere in that
morass of code!

Look, you're having a vbscript/ado/asp problem. The only thing that's
relevant to show us is vbscript code :-)
More below:

Wayne... wrote:

> ok I've made a page with the following code: (this is gonnna be long
> but thought it best to givce all the info, btw the code is in memo
> feild types)

<snippety-snip>

> <%
> 'Dimension variables
> Dim adoCon 'Holds the Database Connection Object
> Dim rsCodevault 'Holds the recordset for the records in the
> database Dim strSQL 'Holds the SQL query to query the
> database
> Set adoCon = Server.CreateObject("ADODB.Connection")
> adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
>
> Server.MapPath("codevault.mdb")

Let's start here. Don't use ODBC when there's a perfectly good OLEDB
provider for Jet:

adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("codevault.mdb")

> Set rsCodevault = Server.CreateObject("ADODB.Recordset")
> strSQL = "SELECT * FROM asp WHERE id = " & ID

You have not assigned a value to that ID variable anywhere that I can see.
Where is the value supposed to come from?

Incidently, that Response.Write statement should be here. It's purely for
debugging purposes: once you have everything working you can delete or
comment it out.

Also, don't be lazy! Don't use selstar (Select *) in production code.
Explicitly list the names of the fields you want your query to return. When
you use selstar, you are retrieving all the fields when you probably only
need a subset of the fields. And even if you do need all the fields, you are
hurting performance because you are forcing ado to make an extra trip to the
database to figure out what * means.

> rsCodevault.Open strSQL, adoCon,,,1

should be:
rsCodevault.Open strSQL, adoCon,,,1

Again, this is a performance improvement suggestion.

> %>

<snip> <HTML>

> <HEAD>
> <TITLE>Codevault </TITLE>
> <META HTTP-EQUIV="Content-Type" CONTENT="text/html;
> charset=ISO-8859-1"> <link href="codecss.css" rel="stylesheet"
> type="text/css"> </HEAD>
> <BODY BGCOLOR = "#FFFFFF">
> <CENTER><H2>ASP Code</H2></CENTER>
> Response.Write strsql

Oh! Here it is!! Ummm - this needs to be in your server-side code block
(Response is a server-side object), preferably right after the line that
assigns the sql statement to the strsql variable.

> ---------------------
> this is the error I get:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in query expression 'id ='.
>
> /test.asp, line 10
>

Now, it's obvious that the value for id is not making its way to this query
statement.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Jeff Cochran]

On Thu, 3 Jun 2004 00:05:48 +0100, "Wayne..."
<wayne@secretwebdesign.com> wrote:

>ok I've made a page with the following code: (this is gonnna be long but
>thought it best to givce all the info, btw the code is in memo feild types)

Yet you missed answering the question we asked. Show us the strSQL
output.

This page is probably generating your error, even though it doesn't
match the error you posted (none do, you renamed the code you posted
so it doesn't match the error message...):

><%
>'Dimension variables
>Dim adoCon 'Holds the Database Connection Object
>Dim rsCodevault 'Holds the recordset for the records in the database
>Dim strSQL 'Holds the SQL query to query the database
>Set adoCon = Server.CreateObject("ADODB.Connection")
>adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
>
>Server.MapPath("codevault.mdb")
>Set rsCodevault = Server.CreateObject("ADODB.Recordset")

Here's your SQL query:

>strSQL = "SELECT * FROM asp WHERE id = " & ID

When you do a

Response.Write strSQL

And show us the code, we'll see:

SELECT * FROM asp WHERE id =

Which results in the error:

>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
>query expression 'id ='.

Which is because the SQL Query has a syntax error, since you're
comparing id to... Well, there's nothing there, is there?

Now, go back and do the Responnse.Write yourself to prove it, then
tell us where you think that the value "ID" is coming from?

Hint: Add a "Response.Write ID" in your code and you'll find you
never populate the variable ID. I can tell this since the code above
your query being executed has absolutely no reference to the variable.

Jeff

[dave]

At least the code is in there - now you need to run the page ;-)

<BODY BGCOLOR = "#FFFFFF">
<CENTER><H2>ASP Code</H2></CENTER>
Response.Write strsql
%>



"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O4XG11PSEHA.3848@TK2MSFTNGP09.phx.gbl...

> Give me a break. :-)
> I asked you to show us the result of Response.Write strSQL and you give us
> the whole flipping page? No, 3 pages! HTML and all??! And nowhere do I see
> the result of Response.Write strSQL, unless it's hidden somewhere in that
> morass of code!
>
> Look, you're having a vbscript/ado/asp problem. The only thing that's
> relevant to show us is vbscript code :-)
> More below:
>
> Wayne... wrote:

> > ok I've made a page with the following code: (this is gonnna be long
> > but thought it best to givce all the info, btw the code is in memo
> > feild types)

> <snippety-snip>

> > <%
> > 'Dimension variables
> > Dim adoCon 'Holds the Database Connection Object
> > Dim rsCodevault 'Holds the recordset for the records in the
> > database Dim strSQL 'Holds the SQL query to query the
> > database
> > Set adoCon = Server.CreateObject("ADODB.Connection")
> > adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> >
> > Server.MapPath("codevault.mdb")

>
> Let's start here. Don't use ODBC when there's a perfectly good OLEDB
> provider for Jet:
>
> adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("codevault.mdb")
>
>

> > Set rsCodevault = Server.CreateObject("ADODB.Recordset")
> > strSQL = "SELECT * FROM asp WHERE id = " & ID

>
> You have not assigned a value to that ID variable anywhere that I can see.
> Where is the value supposed to come from?
>
> Incidently, that Response.Write statement should be here. It's purely for
> debugging purposes: once you have everything working you can delete or
> comment it out.
>
> Also, don't be lazy! Don't use selstar (Select *) in production code.
> Explicitly list the names of the fields you want your query to return.

When

> you use selstar, you are retrieving all the fields when you probably only
> need a subset of the fields. And even if you do need all the fields, you

are

> hurting performance because you are forcing ado to make an extra trip to

the

> database to figure out what * means.
>

> > rsCodevault.Open strSQL, adoCon,,,1

>
> should be:
> rsCodevault.Open strSQL, adoCon,,,1
>
> Again, this is a performance improvement suggestion.
>

> > %>

> <snip> <HTML>

> > <HEAD>
> > <TITLE>Codevault </TITLE>
> > <META HTTP-EQUIV="Content-Type" CONTENT="text/html;
> > charset=ISO-8859-1"> <link href="codecss.css" rel="stylesheet"
> > type="text/css"> </HEAD>
> > <BODY BGCOLOR = "#FFFFFF">
> > <CENTER><H2>ASP Code</H2></CENTER>
> > Response.Write strsql

>
> Oh! Here it is!! Ummm - this needs to be in your server-side code block
> (Response is a server-side object), preferably right after the line that
> assigns the sql statement to the strsql variable.
>
>

> > ---------------------
> > this is the error I get:
> >
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> > operator) in query expression 'id ='.
> >
> > /test.asp, line 10
> >

>
> Now, it's obvious that the value for id is not making its way to this

query

> statement.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Wayne...]

thanks to all I've managed to sort out the mess and everything now works
fine. Turned out that as well as not getting the id variable that was
passed the connection was flakey. would drop if you tried to refresh the
page....
So I changed the connection to the type recommended - problem solved!

Wayne...