|
 |
asp_databases thread: sql string doesn't return any records in recordset object even though it tests out in access itself
Message #1 by "Ben Lew" <formula1@b...> on Sun, 25 Jun 2000 3:6:18
|
|
I'm having a puzzling problem...I'm putting together a search engine in ASP
for a database. The sql query string I've generated (through the post
method) is:
SELECT product_id, product_picture, product_name, product_briefDesc FROM
Products WHERE Products.product_status = 1 AND ((Products.product_name)
LIKE '*abel*') OR ((Products.product_briefDesc) LIKE '*abel*') OR
((Products.product_fulldesc) LIKE '*abel*') ORDER BY product_name;
now when I run Access and run it through the Query Grid in SQL view by
pasting it in, and running it, it returns the correct fields. However,
when I run it from an ASP page it always returns empty. These are the
relevant lines of code I use to setup the objects:
<!-- #Include file="adovbs.inc" -->
<head>
<title>SQL SWStore Query</title>
</head>
<body>
<%
' Open Database Connection
Set objCon = Server.CreateObject( "ADODB.Connection" )
objCon.Open "DSN=SWStore"
Set objRS = Server.CreateObject( "ADODB.Recordset" )
objRS.ActiveConnection = objCon
'some code here
SET objRS = objCon.Execute(sqlString)
where sqlString is the sql query string I posted above...and I've tested to
make sure the sqlString is correct by using response.write to print it out
on my browser, then cutting and pasting it into Access's query grid to test
it. As I said it works fine there, but does not work at all when I'm
sending via the objCon.Execute from my ASP pages. Any suggestions?
Thanks,
Ben
Message #2 by sml.lewis@v... on Sun, 25 Jun 2000 23:46:58
|
|
Ben
If you are returning records ( display recordset record count to prove it )
then it must be a problem within the loop you are using to display the
records. Obviously, you will have to show us this part of the code to get
a response from the boards.
Message #3 by "Ken Schaefer" <ken.s@a...> on Mon, 26 Jun 2000 11:18:46 +1000
|
|
Use %. not *
* is JET SQL, it is not part of the ANSI SQL 92 standard. If you want to do
LIKE searches through ADO, you'll need to use % instead.
Cheers
Ken
----- Original Message -----
From: "Ben Lew"
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, June 25, 2000 12:00 AM
Subject: [asp_databases] sql string doesn't return any records in recordset
object even though it tests out in access itself
> I'm having a puzzling problem...I'm putting together a search engine in
ASP
> for a database. The sql query string I've generated (through the post
> method) is:
>
> SELECT product_id, product_picture, product_name, product_briefDesc FROM
> Products WHERE Products.product_status = 1 AND ((Products.product_name)
> LIKE '*abel*') OR ((Products.product_briefDesc) LIKE '*abel*') OR
> ((Products.product_fulldesc) LIKE '*abel*') ORDER BY product_name;
>
> now when I run Access and run it through the Query Grid in SQL view by
> pasting it in, and running it, it returns the correct fields. However,
> when I run it from an ASP page it always returns empty. These are the
> relevant lines of code I use to setup the objects:
>
> <!-- #Include file="adovbs.inc" -->
> <head>
> <title>SQL SWStore Query</title>
> </head>
>
> <body>
> <%
> ' Open Database Connection
> Set objCon = Server.CreateObject( "ADODB.Connection" )
> objCon.Open "DSN=SWStore"
>
> Set objRS = Server.CreateObject( "ADODB.Recordset" )
> objRS.ActiveConnection = objCon
>
> 'some code here
>
> SET objRS = objCon.Execute(sqlString)
>
> where sqlString is the sql query string I posted above...and I've tested
to
> make sure the sqlString is correct by using response.write to print it out
> on my browser, then cutting and pasting it into Access's query grid to
test
> it. As I said it works fine there, but does not work at all when I'm
> sending via the objCon.Execute from my ASP pages. Any suggestions?
>
> Thanks,
>
> Ben
Message #4 by ANURAGG <ANURAGG@i...> on Mon, 26 Jun 2000 09:41:39 +0530
|
|
hi,
Try using % as wild card character instead of *.
Cheers
Anurag
Message #5 by "Ben Lew" <formula1@b...> on Tue, 27 Jun 2000 22:41:56 -0400
|
|
The loop works for whenever I use Select * from Products, so I don't think
it's that...but instead I migrated the Access Database over to SQL Server
7.0 and it works fine there.
Ben
----- Original Message -----
From: sml.lewis
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, June 25, 2000 11:46 PM
Subject: [asp_databases] Re: sql string doesn't return any records in
recordset object even though it tests out in access itself
> Ben
>
> If you are returning records ( display recordset record count to prove
it )
> then it must be a problem within the loop you are using to display the
> records. Obviously, you will have to show us this part of the code to get
> a response from the boards.
>
Message #6 by "Ben Lew" <formula1@b...> on Tue, 27 Jun 2000 22:42:46 -0400
|
|
Tried that, didn't work. I ended up migrating the Access Database over to
SQL Server 7.0 and it works fine there. I'm puzzled as to why though...
Ben
----- Original Message -----
From: "Ken Schaefer"
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, June 25, 2000 9:18 PM
Subject: [asp_databases] Re: sql string doesn't return any records in
recordset object even though it tests out in access itself
> Use %. not *
>
> * is JET SQL, it is not part of the ANSI SQL 92 standard. If you want to
do
> LIKE searches through ADO, you'll need to use % instead.
>
> Cheers
> Ken
>
> ----- Original Message -----
> From: "Ben Lew"
> To: "ASP Databases" <asp_databases@p...>
> Sent: Sunday, June 25, 2000 12:00 AM
> Subject: [asp_databases] sql string doesn't return any records in
recordset
> object even though it tests out in access itself
>
>
> > I'm having a puzzling problem...I'm putting together a search engine in
> ASP
> > for a database. The sql query string I've generated (through the post
> > method) is:
> >
> > SELECT product_id, product_picture, product_name, product_briefDesc FROM
> > Products WHERE Products.product_status = 1 AND ((Products.product_name)
> > LIKE '*abel*') OR ((Products.product_briefDesc) LIKE '*abel*') OR
> > ((Products.product_fulldesc) LIKE '*abel*') ORDER BY product_name;
> >
> > now when I run Access and run it through the Query Grid in SQL view by
> > pasting it in, and running it, it returns the correct fields. However,
> > when I run it from an ASP page it always returns empty. These are the
> > relevant lines of code I use to setup the objects:
> >
> > <!-- #Include file="adovbs.inc" -->
> > <head>
> > <title>SQL SWStore Query</title>
> > </head>
> >
> > <body>
> > <%
> > ' Open Database Connection
> > Set objCon = Server.CreateObject( "ADODB.Connection" )
> > objCon.Open "DSN=SWStore"
> >
> > Set objRS = Server.CreateObject( "ADODB.Recordset" )
> > objRS.ActiveConnection = objCon
> >
> > 'some code here
> >
> > SET objRS = objCon.Execute(sqlString)
> >
> > where sqlString is the sql query string I posted above...and I've tested
> to
> > make sure the sqlString is correct by using response.write to print it
out
> > on my browser, then cutting and pasting it into Access's query grid to
> test
> > it. As I said it works fine there, but does not work at all when I'm
> > sending via the objCon.Execute from my ASP pages. Any suggestions?
> >
> > Thanks,
> >
> > Ben
>
>
|
|
|
View Cart
