• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

ASP VBScript SQL SELECT query

ASP VBScript
MS Access

Hello, all.

I have, what I thought was, a simple SELECT statement that works just fine inside of MS Access; however, it is not working when I paste it into my ASP script.

Here is the statement inside Access:
=====
select * from cp_parts where cp_image is not null and cp_image <>""""
=====

Here it is inside my ASP script/connection area:
=====
<%

OpenDb()
Set objRs=Server.CreateObject("ADODB.RecordSet")

strsql="select * from cp_parts where cp_image is not null and cp_image <>"""" "
objRs.Open strsql, objConn, 0, 1, 1
%>
=====


I simply pasted it inside the quotes and it give me a message that it is "... expecting 1 or more parameters...".

What am I doing wrong?


Thank you!
Shane
0
lshane
Asked:
lshane
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
there looks to be an extra space in the sql, was that just a typo?

strsql="select * from cp_parts where cp_image is not null and cp_image <>"""" "
                                                                                                                            ^
0
 
lshaneAuthor Commented:
No, there is just a single space there.

Here is the actual message I get when I run the page:
=====
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/pmcct2/copyimages.asp, line 11
=====

Line 11 is this line:  objRs.Open strsql, objConn, 0, 1, 1


It works fine if I just leave it as:  select * from cp_parts where cp_image is not null

... but... there are other fields that are blank, but considered "empty", I guess.  (Something to do with Access)... thus the "AND" portion of it.


Like I said - it runs fine inside of Access.
Doesn't seem to be translating outside of Access.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
did you try putting brackets around the field names?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lshaneAuthor Commented:
Just tried that... same problem.  No effect.
0
 
SharathData EngineerCommented:
Did you try this?

strsql="select * from cp_parts where cp_image is not null and cp_image <>'' "
0
 
lshaneAuthor Commented:
Hi, Sharath.

Bummer - no go, either.

I have no idea what the deal could be.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
let's try another approach, as what you have looks like it should work...

try this:

strsql="select * from cp_parts where cp_image is not null and Len( [cp_image] & """" ) = 0"
0
 
hieloCommented:
Try:
strsql="select * from cp_parts where (cp_image is not null) and (cp_image <>'') "

Open in new window

0
 
lshaneAuthor Commented:
Hi, hielo.

Yours seemed to do the trick.


Thanks!
Shane
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now