80040e14 Syntax error or access violation

Hi

Why does the following line throw up an 80040e14 error?

<%Set qry_items = Connect5.Execute("Select * from items_table WHERE
fld_ownerGUID = " & Session("UserGUID")) %>


I have a session variable (userGUID) that is set and is correct, and the
ownerGUID field is a 'uniqueidentifier'.

Is this because it is trying to use a string value (from the session
variable) to query a uniqueidentifier column? If so, how do I undertake the
necessary convert?

Thanks!

Phil

Re: 80040e14 Syntax error or access violation

news.microsoft.com wrote:
> Hi
>
> Why does the following line throw up an 80040e14 error?
>
> <%Set qry_items = Connect5.Execute("Select * from items_table WHERE
> fld_ownerGUID = " & Session("UserGUID")) %>
>
>
> I have a session variable (userGUID) that is set and is correct, and
> the ownerGUID field is a 'uniqueidentifier'.
>
> Is this because it is trying to use a string value (from the session
> variable) to query a uniqueidentifier column? If so, how do I
> undertake the necessary convert?
>
> Thanks!
>
> Phil
Assuming this is SQL Server (please don't make us guess about this :-) ),
you need to quote the guid. Better yet, use parameters, either via a Command
object:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

or via a stored procedure (my preference):
http://tinyurl.com/jyy0

Bob Barrows
--
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.

Re: 80040e14 Syntax error or access violation

news.microsoft.com wrote on 15 dec 2005 in
microsoft.public.inetserver.asp.db:

> Hi
>
> Why does the following line throw up an 80040e14 error?
>
> <%Set qry_items = Connect5.Execute("Select * from items_table WHERE
> fld_ownerGUID = " & Session("UserGUID")) %>
>
>
> I have a session variable (userGUID) that is set and is correct, and
> the ownerGUID field is a 'uniqueidentifier'.
>
> Is this because it is trying to use a string value (from the session
> variable) to query a uniqueidentifier column? If so, how do I
> undertake the necessary convert?

if fld_ownerGUID is a string field:

= '" & Session("UserGUID")&"'") %>

else if it's a number:

= " & Session("UserGUID")*1) %>

===========================

In your debugging mode, always first write your SQL strings:

<%

SQL="Select * from items_table WHERE fld_ownerGUID = " &_
Session("UserGUID")*1

response.write SQL:response.end

Set qry_items = Connect5.Execute(SQL)

%>



--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: 80040e14 Syntax error or access violation

Hi Bob

Yes it is SQL server; sorry for the omission!

The page we're developing is only as a proof of concept for a forthcoming
internal application, so for not I'd just be looking for the easiest
possible solution.

If I actually hard code the userGUID value into the select statement it all
works fine, but I've put it into a session variable so that multiple users
can run this page and pass the userGUID as a parameter for this query.

You suggest that it can be 'quoted' - do you mean hard-coded into the
statement (which I really don't want to do)? Or can it be easily done by a
CONVERT statement?

Thanks again!

Phil


"Bob Barrows [MVP]" wrote in message
news:uM6MRGYAGHA.356@TK2MSFTNGP12.phx.gbl...
> news.microsoft.com wrote:
>> Hi
>>
>> Why does the following line throw up an 80040e14 error?
>>
>> <%Set qry_items = Connect5.Execute("Select * from items_table WHERE
>> fld_ownerGUID = " & Session("UserGUID")) %>
>>
>>
>> I have a session variable (userGUID) that is set and is correct, and
>> the ownerGUID field is a 'uniqueidentifier'.
>>
>> Is this because it is trying to use a string value (from the session
>> variable) to query a uniqueidentifier column? If so, how do I
>> undertake the necessary convert?
>>
>> Thanks!
>>
>> Phil
> Assuming this is SQL Server (please don't make us guess about this :-) ),
> you need to quote the guid. Better yet, use parameters, either via a
> Command
> object:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> or via a stored procedure (my preference):
> http://tinyurl.com/jyy0
>
> Bob Barrows
> --
> 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.
>
>

Re: 80040e14 Syntax error or access violation

news.microsoft.com wrote:
> Hi Bob
>
> Yes it is SQL server; sorry for the omission!
>
> The page we're developing is only as a proof of concept for a
> forthcoming internal application, so for not I'd just be looking for
> the easiest possible solution.

:-)
Using parameters IS the easiest possible solution (IMO).

>
> If I actually hard code the userGUID value into the select statement
> it all works fine,
>

Ah! so you have a statement that works. Now it's simply a matter of
verifying that the synamic sql statement you've created by concatenation
matches the statement that works. The only way to do that is to
response.write the result of the concatenation. The easiest way to do this
is to assign it to a string variable which can be used in the response.write
and Execute statements:

dim sql
sql="Select * from items_table WHERE " & _
"fld_ownerGUID = " & Session("UserGUID"

response.write sql
Set qry_items = Connect5.Execute(sql,,1)

> but I've put it into a session variable so that
> multiple users can run this page and pass the userGUID as a parameter
> for this query.
>
> You suggest that it can be 'quoted' - do you mean hard-coded into the
> statement (which I really don't want to do)?
No, I meant that the value should be delimited with quotes. But again, using
parameters relieves you of the obligation to worry about delimiters:

dim arParms, sql, cmd
dim sql
sql="Select * from items_table WHERE fld_ownerGUID = ?"
'make sure Session("UserGUID" has a value
if len(Session("UserGUID") > 0 then
arParms=Array(Session("UserGUID")
set cmd=createobject("adodb.command")
cmd.commandtype=1 'adCmdText
cmd.commandtext=sql
set cmd.activeconnection=Connect5
set qry_items=cmd.execute(,arParms)
end if

HTH,
Bob Barrows
--
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.

Re: 80040e14 Syntax error or access violation

Hi Evertjan and Bob!

That worked a treat! Many thanks!

Phil


"Evertjan." wrote in message
news:Xns972D9BC705275eejj99@194.109.133.242...
> news.microsoft.com wrote on 15 dec 2005 in
> microsoft.public.inetserver.asp.db:
>
>> Hi
>>
>> Why does the following line throw up an 80040e14 error?
>>
>> <%Set qry_items = Connect5.Execute("Select * from items_table WHERE
>> fld_ownerGUID = " & Session("UserGUID")) %>
>>
>>
>> I have a session variable (userGUID) that is set and is correct, and
>> the ownerGUID field is a 'uniqueidentifier'.
>>
>> Is this because it is trying to use a string value (from the session
>> variable) to query a uniqueidentifier column? If so, how do I
>> undertake the necessary convert?
>
> if fld_ownerGUID is a string field:
>
> = '" & Session("UserGUID")&"'") %>
>
> else if it's a number:
>
> = " & Session("UserGUID")*1) %>
>
> ===========================
>
> In your debugging mode, always first write your SQL strings:
>
> <%
>
> SQL="Select * from items_table WHERE fld_ownerGUID = " &_
> Session("UserGUID")*1
>
> response.write SQL:response.end
>
> Set qry_items = Connect5.Execute(SQL)
>
> %>
>
>
>
> --
> Evertjan.
> The Netherlands.
> (Replace all crosses with dots in my emailaddress)
>

Re: 80040e14 Syntax error or access violation

news.microsoft.com wrote:
> Hi Evertjan and Bob!
>
> That worked a treat! Many thanks!
>

What did?
Did you have to pass the guid as a number or a string? Just for future
reference ...

Bob Barrows
--
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.