Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: Database Query

  1. #1
    Join Date
    Aug 2004
    Posts
    14

    Unanswered: Database Query

    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")

  2. #2
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    Ok first do a Debug >>>

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

    response.write SQL

    and see what you get
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  3. #3
    Join Date
    Aug 2004
    Posts
    14

    Printout Results

    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

  4. #4
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    ok then try this and see if it collects the record

    SQL = "SELECT * FROM Exceptions WHERE recordID = " & CLng(request("ID"))
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  5. #5
    Join Date
    Aug 2004
    Posts
    14

    No Luck

    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.

  6. #6
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    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") & "'"
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  7. #7
    Join Date
    Aug 2004
    Posts
    14

    More Info

    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.

  8. #8
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    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")
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  9. #9
    Join Date
    Aug 2004
    Posts
    14

    Code Segment

    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

  10. #10
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    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
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  11. #11
    Join Date
    Aug 2004
    Posts
    14

    Answer

    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?

  12. #12
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    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 & " "
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  13. #13
    Join Date
    Aug 2004
    Posts
    14

    Results

    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.

  14. #14
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    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?
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  15. #15
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    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"))
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •