p2p.wrox.com: Archives - access

access_asp thread: ASP/Access Parameterized Query Not Working?

Message #1 by "AJ" <electrasketch@y...> on Wed, 6 Nov 2002 16:23:14

I'm trying to run a query that i created in access via asp.  I have two queries that i want to run.  One
of them works.  It has one parameter -> name.  The other query that isn't working properly has two parameters -> name,mydate. 
I don't receive an error but i get 0 results back even though  I know i get results back when i enter the same parameter info in
access.  I am using access2000.  I think it has something to do with the date parameter since my other query works.  I've tried
using 'adDate' and 'adVarChar' as well... same thing happens.
name="Joe"
mydate="11/06/02"

	Set QueryCmd=Server.CreateObject("ADODB.Command")
	Set QueryRS=Server.CreateObject("ADODB.Recordset")
	QueryRS.CursorLocation=adUseClient
	QueryRS.CursorType=adOpenForwardOnly
	QueryRS.LockType=AdLockReadOnly
	set QueryCmd.ActiveConnection=newConnection
	QueryCmd.CommandText="qryTopPoinain"
	QueryCmd.CommandType=adCmdStoredProc
	Set QueryParam = QueryCmd.CreateParameter("@name", adVarChar,adParamInput, 50 , name)
	QueryCmd.Parameters.Append QueryParam
	Set QueryParam = QueryCmd.CreateParameter("@mydate", adDate,adParamInput, 8, mydate)
	QueryCmd.Parameters.Append QueryParam
	QueryRS.Open QueryCmd

	response.write("RecordCount: " & QueryRS.RecordCount & "<br>")

Also, I've tried doing this below and i get the same thing:
 Set QueryParam = QueryCmd.CreateParameter("@mydate", adVarChar,adParamInput, 50, mydate)


Any help would be greatly appreciated.. thanks!!!

Message #2 by "Rob Parkhouse" <rparkhouse@o...> on Wed, 6 Nov 2002 23:25:48

> I'm trying to run a query that i created in access via asp.  I have two 
queries that i want to run.  One of them works.  It has one parameter -> 
name.  The other query that isn't working properly has two parameters -> 
name,mydate.  I don't receive an error but i get 0 results back even 
though  I know i get results back when i enter the same parameter info in 
access.  I am using access2000.  I think it has something to do with the 
date parameter since my other query works.  I've tried using 'adDate' 
and 'adVarChar' as well... same thing happens.
n> ame="Joe"
m> ydate="11/06/02"

> 	Set QueryCmd=Server.CreateObject("ADODB.Command")
	> Set QueryRS=Server.CreateObject("ADODB.Recordset")
	> QueryRS.CursorLocation=adUseClient
	> QueryRS.CursorType=adOpenForwardOnly
	> QueryRS.LockType=AdLockReadOnly
	> set QueryCmd.ActiveConnection=newConnection
	> QueryCmd.CommandText="qryTopPoinain"
	> QueryCmd.CommandType=adCmdStoredProc
	> Set QueryParam = QueryCmd.CreateParameter("@name", 
adVarChar,adParamInput, 50 , name)
	> QueryCmd.Parameters.Append QueryParam
	> Set QueryParam = QueryCmd.CreateParameter("@mydate", 
adDate,adParamInput, 8, mydate)
	> QueryCmd.Parameters.Append QueryParam
	> QueryRS.Open QueryCmd

> 	response.write("RecordCount: " & QueryRS.RecordCount & "<br>")

> Also, I've tried doing this below and i get the same thing:
 > Set QueryParam = QueryCmd.CreateParameter("@mydate", 
adVarChar,adParamInput, 50, mydate)

> 
A> ny help would be greatly appreciated.. thanks!!!




Try:

Set QueryRS = QueryCmd.Execute

Message #3 by "Larry Woods" <larry@l...> on Wed, 6 Nov 2002 16:53:32 -0700

Rob,

You are dealing with a date, not a string.  Set:

	mydate=#2/1/2002#

without quotes.

Larry Woods


> -----Original Message-----
> From: Rob Parkhouse [mailto:rparkhouse@o...]
> Sent: Wednesday, November 06, 2002 11:26 PM
> To: Access ASP
> Subject: [access_asp] Re: ASP/Access Parameterized
> Query Not Working?
>
>
> > I'm trying to run a query that i created in access
> via asp.  I have two
> queries that i want to run.  One of them works.  It
> has one parameter ->
> name.  The other query that isn't working properly has
> two parameters ->
> name,mydate.  I don't receive an error but i get 0
> results back even
> though  I know i get results back when i enter the
> same parameter info in
> access.  I am using access2000.  I think it has
> something to do with the
> date parameter since my other query works.  I've tried
> using 'adDate'
> and 'adVarChar' as well... same thing happens.
> n> ame="Joe"
> m> ydate="11/06/02"
>
> > 	Set QueryCmd=Server.CreateObject("ADODB.Command")
> 	> Set QueryRS=Server.CreateObject("ADODB.Recordset")
> 	> QueryRS.CursorLocation=adUseClient
> 	> QueryRS.CursorType=adOpenForwardOnly
> 	> QueryRS.LockType=AdLockReadOnly
> 	> set QueryCmd.ActiveConnection=newConnection
> 	> QueryCmd.CommandText="qryTopPoinain"
> 	> QueryCmd.CommandType=adCmdStoredProc
> 	> Set QueryParam = QueryCmd.CreateParameter("@name",
> adVarChar,adParamInput, 50 , name)
> 	> QueryCmd.Parameters.Append QueryParam
> 	> Set QueryParam = QueryCmd.CreateParameter("@mydate",
> adDate,adParamInput, 8, mydate)
> 	> QueryCmd.Parameters.Append QueryParam
> 	> QueryRS.Open QueryCmd
>
> > 	response.write("RecordCount: " &
> QueryRS.RecordCount & "<br>")
>
> > Also, I've tried doing this below and i get the same thing:
>  > Set QueryParam = QueryCmd.CreateParameter("@mydate",
> adVarChar,adParamInput, 50, mydate)
>
> >
> A> ny help would be greatly appreciated.. thanks!!!
>
>
>
>
> Try:
>
> Set QueryRS = QueryCmd.Execute
>

Message #4 by "AJ" <electrasketch@y...> on Thu, 7 Nov 2002 16:20:36

Hey guys... I tried both of these solutions but it still doesn't work :(  Thanks for your help though! 
Let me know if u have any other suggestions!

> Rob,

You are dealing with a date, not a string.  Set:

	mydate=#2/1/2002#

without quotes.

Larry Woods


> -----Original Message-----
> From: Rob Parkhouse [mailto:rparkhouse@o...]
> Sent: Wednesday, November 06, 2002 11:26 PM
> To: Access ASP
> Subject: [access_asp] Re: ASP/Access Parameterized
> Query Not Working?
>
>
> > I'm trying to run a query that i created in access
> via asp.  I have two
> queries that i want to run.  One of them works.  It
> has one parameter ->
> name.  The other query that isn't working properly has
> two parameters ->
> name,mydate.  I don't receive an error but i get 0
> results back even
> though  I know i get results back when i enter the
> same parameter info in
> access.  I am using access2000.  I think it has
> something to do with the
> date parameter since my other query works.  I've tried
> using 'adDate'
> and 'adVarChar' as well... same thing happens.
> n> ame="Joe"
> m> ydate="11/06/02"
>
> > 	Set QueryCmd=Server.CreateObject("ADODB.Command")
> 	> Set QueryRS=Server.CreateObject("ADODB.Recordset")
> 	> QueryRS.CursorLocation=adUseClient
> 	> QueryRS.CursorType=adOpenForwardOnly
> 	> QueryRS.LockType=AdLockReadOnly
> 	> set QueryCmd.ActiveConnection=newConnection
> 	> QueryCmd.CommandText="qryTopPoinain"
> 	> QueryCmd.CommandType=adCmdStoredProc
> 	> Set QueryParam = QueryCmd.CreateParameter("@name",
> adVarChar,adParamInput, 50 , name)
> 	> QueryCmd.Parameters.Append QueryParam
> 	> Set QueryParam = QueryCmd.CreateParameter("@mydate",
> adDate,adParamInput, 8, mydate)
> 	> QueryCmd.Parameters.Append QueryParam
> 	> QueryRS.Open QueryCmd
>
> > 	response.write("RecordCount: " &
> QueryRS.RecordCount & "<br>")
>
> > Also, I've tried doing this below and i get the same thing:
>  > Set QueryParam = QueryCmd.CreateParameter("@mydate",
> adVarChar,adParamInput, 50, mydate)
>
> >
> A> ny help would be greatly appreciated.. thanks!!!
>
>
>
>
> Try:
>
> Set QueryRS = QueryCmd.Execute
>

Message #5 by "Larry Woods" <larry@l...> on Thu, 7 Nov 2002 11:27:52 -0700

Don't know what to tell you, AJ, but THIS WORKS!!!!

<%
set cn=server.createobject("ADODB.Connection")
set rs=server.createobject("ADODB.Recordset")
set cm=server.createobject("ADODB.Command")
cn.open "dsn=TestDB"
cm.activeconnection=cn
cm.commandtype=4
cm.commandtext="Query2"
d=#3/1/2002#
cm.parameters.append cm.createparameter("@T",7,1,,d)
set rs=cm.execute
%>
<html><head/><body>
<p><%=rs("ID")%>-<%=rs("Value")%></P>
</body>
</html>

Larry Woods


> -----Original Message-----
> From: AJ [mailto:electrasketch@y...]
> Sent: Thursday, November 07, 2002 4:21 PM
> To: Access ASP
> Subject: [access_asp] Re: ASP/Access Parameterized
> Query Not Working?
>
>
> Hey guys... I tried both of these solutions but it
> still doesn't work :(  Thanks for your help though!
> Let me know if u have any other suggestions!
>
> > Rob,
>
> You are dealing with a date, not a string.  Set:
>
> 	mydate=#2/1/2002#
>
> without quotes.
>
> Larry Woods
>
>
> > -----Original Message-----
> > From: Rob Parkhouse [mailto:rparkhouse@o...]
> > Sent: Wednesday, November 06, 2002 11:26 PM
> > To: Access ASP
> > Subject: [access_asp] Re: ASP/Access Parameterized
> > Query Not Working?
> >
> >
> > > I'm trying to run a query that i created in access
> > via asp.  I have two
> > queries that i want to run.  One of them works.  It
> > has one parameter ->
> > name.  The other query that isn't working properly has
> > two parameters ->
> > name,mydate.  I don't receive an error but i get 0
> > results back even
> > though  I know i get results back when i enter the
> > same parameter info in
> > access.  I am using access2000.  I think it has
> > something to do with the
> > date parameter since my other query works.  I've tried
> > using 'adDate'
> > and 'adVarChar' as well... same thing happens.
> > n> ame="Joe"
> > m> ydate="11/06/02"
> >
> > > 	Set QueryCmd=Server.CreateObject("ADODB.Command")
> > 	> Set QueryRS=Server.CreateObject("ADODB.Recordset")
> > 	> QueryRS.CursorLocation=adUseClient
> > 	> QueryRS.CursorType=adOpenForwardOnly
> > 	> QueryRS.LockType=AdLockReadOnly
> > 	> set QueryCmd.ActiveConnection=newConnection
> > 	> QueryCmd.CommandText="qryTopPoinain"
> > 	> QueryCmd.CommandType=adCmdStoredProc
> > 	> Set QueryParam = QueryCmd.CreateParameter("@name",
> > adVarChar,adParamInput, 50 , name)
> > 	> QueryCmd.Parameters.Append QueryParam
> > 	> Set QueryParam = QueryCmd.CreateParameter("@mydate",
> > adDate,adParamInput, 8, mydate)
> > 	> QueryCmd.Parameters.Append QueryParam
> > 	> QueryRS.Open QueryCmd
> >
> > > 	response.write("RecordCount: " &
> > QueryRS.RecordCount & "<br>")
> >
> > > Also, I've tried doing this below and i get the same thing:
> >  > Set QueryParam = QueryCmd.CreateParameter("@mydate",
> > adVarChar,adParamInput, 50, mydate)
> >
> > >
> > A> ny help would be greatly appreciated.. thanks!!!
> >
> >
> >
> >
> > Try:
> >
> > Set QueryRS = QueryCmd.Execute
> >

Message #6 by "Ken Schaefer" <ken@a...> on Fri, 8 Nov 2002 14:24:32 +1100

www.adopenstatic.com/faq/dateswithaccess.asp
you may be running into a date interpretation issue. Try setting the date to
be #2002/1/2# (for 2nd Janauary)

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "AJ" <electrasketch@y...>
Subject: [access_asp] Re: ASP/Access Parameterized Query Not Working?

: Hey guys... I tried both of these solutions but it still doesn't work
:(  Thanks for your help though!  Let me know if u have any other
suggestions!
:
: > Rob,
:
: You are dealing with a date, not a string.  Set:
:
: mydate=#2/1/2002#
:
: without quotes.
:
: Larry Woods
:
:
: > -----Original Message-----
: > From: Rob Parkhouse [mailto:rparkhouse@o...]
: > Sent: Wednesday, November 06, 2002 11:26 PM
: > To: Access ASP
: > Subject: [access_asp] Re: ASP/Access Parameterized
: > Query Not Working?

Return to Index

P2P Archives