here is my problem. From page1.asp I build links and set ID to the recordID to pass to page2.asp. The problem is that when I use the Request("ID) field in the sql string, it breaks. If I set a tmp var equal to a number and replace the requset("ID") with it in the sql string it works. Can anyone help?

From Page1.asp
Response.Write vbTab & vbTab & "<td width=""25%"" tr bgcolor=""#FFFF00""><strong><a href=""record.asp?ID=" & rs.fields("recordID") & "</a>"

Page2.asp SQL statement:

SQL = "SELECT * FROM Exceptions WHERE recordID = " & request("ID")

Ok first do a Debug >>>

SQL = "SELECT * FROM Exceptions WHERE recordID = " & request("ID")

response.write SQL

and see what you get

Here is what I get with the Response.Write: SELECT * FROM Exceptions WHERE recordID = 3

Thats whats killing me, it passes the correct record index.

Thanks

ok then try this and see if it collects the record

SQL = "SELECT * FROM Exceptions WHERE recordID = " & CLng(request("ID"))

The page will not display when I tried the Clng. I also tried CInt and storing it into a temp variable and printing that. All three do the same thing. For more background, I am usin Frontpage 2002.

OK Now I Assumed that recordID in your DB is a numeric field

try doing the query in the db it'self and see what you get

If it is a String field then you need
SQL = "SELECT * FROM Exceptions WHERE recordID = '" & request("ID") & "'"

You assumed correct. The field is a bigint (SQL Server 2000). Using the Query analyzer with the following: Select * from Hippa.dbo.Exceptions where recordID = '3'

It returns the record correctly. Curious, when a dim'd a tmp field, tried setting tmp = CLng(request("ID"), the page won't even display.

Maybe just maybe it's the User problem.

See how it performs if you detail the database user


SQL = "SELECT * FROM Hippa.dbo.Exceptions where recordID = " & request("ID")

If you mean a user problem as in me, I would agree with that comment. Here is the entire layout. I have search.htm that have a text box that takes an entry and builds a table with the database results in submit.asp. This part works fine. This is where I have the reference links with the ?ID field set to the record id. When you click on a link, it loads record.asp. Here is the problem code in record.asp:
The way it is written now works because the field record is hard coded to 2. if I replace it with request("ID") in the SQL statement, it blows up.
BTW - Thanks for the help you are giving.....

<%
If Request("ID") <> "" Then


Response.Write "<strong> Search Results</strong>" & vbCrLf

DIM myConn
DIM rs
DIM SQL
DIM record

record = 2
set myConn = CreateObject("ADODB.Connection")

sConnString = "Provider=SQLOLEDB.1;user id=xxx;password=xxx;Initial Catalog=hippa;Data Source = 5801-A55SQL-F7;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"


myConn.Open sConnString


set rs = CreateObject("ADODB.Recordset")

SQL = "SELECT * FROM Exceptions WHERE recordID = " & record & " "




rs.Open SQL, myConn,1,3

Ok now I'm going to ask a really stupid question -- (i'm noted for them!)

You do have a record with recordId of 2 in the Table right?

No I was not meaning you when I said user problem

Try it without a parameter and see if it returns all records

Yes there is a record in the table with an id of 2. Using just the basic SQL statement Select * from Exceptions, it displays the first record in the table. Displaying the value of request("ID") everytime shows me the correct record index. For some reason, it doesn't like it in the sql statement. Even when I replace record = 2 with record = request("ID) it fails. Could it be a type conversion error?

Add the bolded lines in and lets see what Var Type we have

<%
If Request("ID") <> "" Then


Response.Write "<strong> Search Results</strong>" & vbCrLf

DIM myConn
DIM rs
DIM SQL
DIM record

record = request("ID")
Record have vartype of " & VarType(record) & "<br>"


record = Clng(request("ID"))
Record have vartype of " & VarType(record) & "<br>"

set myConn = CreateObject("ADODB.Connection")

sConnString = "Provider=SQLOLEDB.1;user id=xxx;password=xxx;Initial Catalog=hippa;Data Source = 5801-A55SQL-F7;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"


myConn.Open sConnString


set rs = CreateObject("ADODB.Recordset")

SQL = "SELECT * FROM Exceptions WHERE recordID = " & record & " "

the vartype is 8 on the first reference. I cannot use CInt or CLng on the field request("ID"), it blows up. I have also tried to store request("ID") into a tmp field and then try to convert it and the same happens.

ok vartype of 8 is a string

Weird! you should be able to convert it using Clng or Cint

What type is the field in the DB?

ok lets rewind a bit here

Where is the request comming from ? is that correctly formatted?

try

response.write "[" & request("ID") & "]"

to see if there are spurious spaces then if there are

Clng(Trim(request("ID"))