SQL Statement 2

[mot98]

Hi All,

Is it possible to use the SELECT SQL statement to compare 2 things at once??

Something like

SELECT * FROM * WHERE Customer=name AND IdNumber=5

If this is possible can someone give me some example code?

Thanks,
mot98..

"Where's the beer?"

 

[KyleS]

Yes it is, are you looking for something like this?

SELECT tblProjects*
FROM tblProjects
WHERE (((tblProjects.ID)>100) AND ((tblProjects.[Date Completed]) Between #10/1/01# And #10/1/02#));

This example is (obviously) for only one table, but as far as multiple criteria, it's a decent example. Kyle

 

[mot98]

Thanks for that example Kyle...

Here is another problem I am having.. I have 3 ASP pages..on the first page it is just a simple drop down box populated from a database.

<%
Do While NOT oRSa.EOF
Response.Write &quot;<OPTION VALUE='&quot; & oRSa(&quot;VendorId&quot & &quot;'>&quot;
Response.Write oRSa(&quot;VendorName&quot & &quot;</OPTION>&quot;
oRSa.MoveNext
Loop
oRSa.Close
Set oRSa=Nothing
%>

Then on my second page I am getting another variable from a drop down box populated from the database.

<%
Dim ItemVendor
parmvendor = Request.Form (&quot;VendorChoice&quot
Dim oRSb
Set oRSb=Server.CreateObject(&quot;ADODB.Recordset&quot
sqltext= &quot;SELECT Distinct ItemType FROM Items WHERE ItemVendor= &quot; & parmvendor & &quot;;&quot;
oRSb.Open sqltext , &quot;DSN=Clothier&quot;
oRSb.MoveFirst
%>

<center>
<Form Method=&quot;post&quot; Action=&quot;results.asp&quot; Size=&quot;1&quot;>
<P><Select>
<%
Do While NOT oRSb.EOF
Response.Write &quot;<OPTION VALUE='&quot; & oRSb(&quot;ItemType&quot & &quot;'>&quot;
Response.Write oRSb(&quot;ItemType&quot & &quot;</OPTION>&quot;
oRSb.MoveNext
Loop
oRSb.Close
Set oRSb=Nothing
%>
</Select> </P>
<Input Type=hidden Name=VendorName Value=<%=Request.Form(&quot;VendorChoice&quot%>>
<Input Type=hidden Name=ItemChoice Value=<%'&quot;&ItemType&&quot;'%>>
<P><Input Type=&quot;Submit&quot;> </P>
</Form>

Now I am having problems with the third page.
When I try to set the hidden variables I am getting &quot;Object doesn't support this property or method: 'Form' &quot; error.

I am trying to get the variable set in the second page with the following code..

parmVendor = Response.Form (&quot;VendorName&quot
parmItem = Response.Form (&quot;ItemChoice&quot


Can someone tell me what I am doing wrong here??
Thanks,

mot98..

&quot;Where's the beer?&quot;

 

[JuanitaC]

parmVendor = Response.Form (&quot;VendorName&quot
parmItem = Response.Form (&quot;ItemChoice&quot

should be

parmVendor = Request.Form (&quot;VendorName&quot
parmItem = Request.Form (&quot;ItemChoice&quot

 

[mot98]

Thanks Juanita...

Now one last problem (I think

I am getting errors on this SQL text...

sqltext= &quot;Select ItemName, ItemDepartment, ItemPriceBuy, ItemDateRelease FROM Items WHERE ((ItemType = (&quot;& parmItem &&quot) AND (ItemVendor = ('&quot;& parmVendor & &quot;'));&quot;


Any help would be great...

mot98..

&quot;Where's the beer?&quot;

 

[KyleS]

Try:

sqltext= &quot;Select Items.ItemName, Items.ItemDepartment, Items.ItemPriceBuy, Items.ItemDateRelease FROM Items WHERE ((Items.ItemType = (&quot;& parmItem &&quot) AND (Items.ItemVendor = ('&quot;& parmVendor & &quot;'));&quot;
Kyle

 

[mot98]

Hey Kyle,

It is giving me a syntax error on that code...

Syntax error (missing operator) in query expression '((Items.ItemType = ()) AND (Items.ItemVendor = ('2'));'.


Not sure what it means by missing operator?? mot98..

&quot;Where's the beer?&quot;

 

[KyleS]

Oh, sorry, you're actually missing a
Parenthasis (<--Spelling?)

sqltext= &quot;Select Items.ItemName, Items.ItemDepartment, Items.ItemPriceBuy, Items.ItemDateRelease FROM Items WHERE ((Items.ItemType = (&quot;& parmItem &&quot) AND (Items.ItemVendor = ('&quot;& parmVendor & &quot;')));&quot;

should fix that problem. Kyle

 

[mot98]

Thanks Kyle..worked great...However..

I am now getting an error saying :

Data type mismatch in criteria expression


I assume that means that it is trying to match it to something that is not defined?? But I can't figure out where.

I have defined my variables at the top reffering them back to the hidden fields..

parmVendor = Request.Querystring (&quot;VendorName&quot
parmItem = Request.Querystring (&quot;ItemChoice&quot


So I am not sure why it is erroring out??
mot98..

&quot;Where's the beer?&quot;

 

[KyleS]

That error is a SQL error which means you're trying to use the wrong data type ie, text for an integer, or vice-versa.

You're probably getting this because parmitem is null. Try this:

If isnull(parmitem) AND Not isnull(parmvendor) then
sqltext= &quot;Select Items.ItemName, Items.ItemDepartment, Items.ItemPriceBuy, Items.ItemDateRelease FROM Items WHERE ((Items.ItemVendor = ('&quot;& parmVendor & &quot;')));&quot;
ElseIf isnull(parmvendor) AND Not isnull(parmitem) then
sqltext= &quot;Select Items.ItemName, Items.ItemDepartment, Items.ItemPriceBuy, Items.ItemDateRelease FROM Items WHERE ((Items.ItemType = (&quot;& parmItem &&quot));&quot;
ElseIf isnul(parmvendor) AND isnull(parmitem) then
sqltext= &quot;Select Items.ItemName, Items.ItemDepartment, Items.ItemPriceBuy, Items.ItemDateRelease FROM Items;&quot;
Else
sqltext= &quot;Select Items.ItemName, Items.ItemDepartment, Items.ItemPriceBuy, Items.ItemDateRelease FROM Items WHERE ((Items.ItemType = (&quot;& parmItem &&quot) AND (Items.ItemVendor = ('&quot;& parmVendor & &quot;')));&quot;
End If
You can't pass a NULL value as a parameter, this should help you avoid that. Kyle

 

[mot98]

parmItem can't be Null...
On the previous page the code is like this..

<%
Do While NOT oRSb.EOF
Response.Write &quot;<OPTION VALUE='&quot; & oRSb(&quot;ItemType&quot & &quot;'>&quot;
Response.Write oRSb(&quot;ItemType&quot & &quot;</OPTION>&quot;
oRSb.MoveNext
Loop
oRSb.Close
Set oRSb=Nothing
%>
</Select> </P>
<Input Type=hidden Name=VendorName Value=<%=Request.Form(&quot;VendorChoice&quot%>>
<Input Type=hidden Name=ItemChoice Value=<%'&quot;&ItemType&&quot;'%>>
<P><Input Type=&quot;Submit&quot;> </P>

So it gives both VendorName and ItemChoice a value..

Then on the last page it looks like this...

<%
Dim ItemVendor
parmVendor = Request.Querystring (&quot;VendorName&quot
parmItem = Request.Querystring (&quot;ItemChoice&quot
Dim oRSb
Set oRSb=Server.CreateObject(&quot;ADODB.Recordset&quot
sqltext= &quot;Select ItemName, ItemDepartment, ItemPriceBuy, ItemDateRelease FROM Items WHERE (ItemType =('&quot;& parmItem &&quot;')) AND (ItemVendor =('&quot;& parmVendor & &quot;'));&quot;
oRSb.Open sqltext , &quot;DSN=Clothier&quot;
'Response.Write sqltext
oRSb.MoveFirst

So I can't see why a Null would be effecting anything here.

Although on my first page I am not passing the value through hidden, I am just getting the value from the Response.Form method..Then I am trying to pass that value on again to the last page.. Maybe I should be using a hidden value on my first page as well??


mot98..

&quot;Where's the beer?&quot;

 

[KyleS]

The reason I suggested it was null was because of the first error message you got:

Syntax error (missing operator) in query expression '((Items.ItemType = ()) AND (Items.ItemVendor = ('2'));'.

If you notice (besides being short on closing parenthasis) there is no value for parmitem. Kyle

 

[mot98]

Hey Kyle..

If the value is an integer, then it needs to be surrounded by single quotes right?? So if it is just text then it doesn't need the single quotes??

I am wondering, because parmItem is text, and parmVendor is a number?
mot98..

&quot;Where's the beer?&quot;

 

[KyleS]

Actually, it's the other way, text needs the single quotes and integer needs nothing.

so actually, just by switching that you should fix your Data Type Mismatch issue... Kyle