Not able to pull info from MS Access Query

[cottontop]

Here's something...

IIS, MS Access 2k

Searchit.asp
User enters telephone number:
<FORM action="telephn_results.asp" name="info" method=POST>

telephn_results.asp
And it's suppose to bring up the recordset based upon "info" [the phone number]:
cmdTemp.CommandText = "SELECT * from qry_search4tele where phnnum = '" & Request.Form("Find") & "'"

It brings up nothing! Errors or otherwise!!!

The MS Access query works fine. Running it in Access displays all the records. It is a simple select query inner joining several tables.

I modified the query to create a table. Copied the original ASP file and modified it. At first it didn't work, but if I use:
<% Response.Write DataCommand1(0)'phnnum %>
instead of
<% Response.Write DataCommand1("phnnum") %>
it will work. [Slowly, since this is a large table!]

I want it to work using the query.

Any ideas???

TIA

 

[onpnt]

test using these guidlines
Development practices when using SQL/Interfaces faq333-4896

also
'" & Trim(Request.Form("Find")) & "'"

and data type conversions should always be performed on form objects in SQL statements. because
this
" 222-2222"
is not this
"222-2222"


___________________________________________________________________
[sub]
onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811
[/sub]

 

[cottontop]

Thanks onpnt

I did read that link prior to submitting the question. I really don't think it's in the SQL statement. I think it's in the ASP scripting.

Thanks for pointing out the trim(). That will help in case someone wants to add a space!

Before I actually try it, can you copy the MS SQL statement from MS Access and use it in ASP?

Maybe that will prove me wrong in my statement above.

 

[Chopstik]

Just as a another point from which to start, could you response.write out the Request.Form("Find") variable to ensure that it is returning what you think it should be? Also, you could try to response.write your entire SQL statement before you attempt to submit it and then copy/paste it into Access to ensure that it works. I know these are in onpnt's link, but if you haven't tried them yet, it would be better to eliminate the possible, even if not the probable.

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[amorous]

Try this:

"SELECT * from qry_search4tele where phnnum = '" &trim( Request.Form("Find")) & "'"

-VJ

 

[DreXor]

also unless you're using a recordset object to update, you may find things more responsive if you use :

set RSobj = Connection.Execute(YourSQL)

then cycle the RSobj

in your instance it would be :

Set DataCommand1 = cmdTemp.Execute("SELECT * from qry_search4tele where phnnum = '" & Request.Form("Find") & "'")

but i'm not sure if you're not already doing this, it is an assumption on my part since i saw the use of .CommandText