Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing ItemID from one page to next

    Hope someone can help ... I'm adapting the Classified application in BEGINNING ASP 3.0 (Wrox) to a comic-book collection tracker. Consequently, I'm making some changes to the way the application works. I've run into a couple of problems, that ought to be easy, but are giving me sleepless nights. Here's the first problem.

    When selecting an item in the database to edit the details for the use clicks a linked ID number on a table and this brings up a details form with fields filled in from corresponding fields in the database. Pretty, standard, right? Except that it's not working for me and I can't see why.

    Here's the relevant code from the ViewMyComics.asp page ...


    Code:
    <%
      Dim rsItems
      Set rsItems = Server.CreateObject("ADODB.Recordset")
    
      rsItems.Open "Item", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
      
      If Not rsItems.EOF Then                    ' current user has items for sale
        Response.Write _
          "<table border=""1"" cellspacing=""2"" cellpadding=""2"">" & _
          "<tr>" & _
          "  <th>ID</th>" & _
          "  <th>Issue</th>" & _
          "  <th>Condition</th>" & _
          "  <th>Month</th>" & _
          "  <th>Year</th>" & _
          "</tr>"
        Do While Not rsItems.EOF
          Response.Write _
            "<tr align=center>" & _
            "  <td><a href=""item.asp?Action=Edit&Item=" & rsItems("idItem") & """>" & _
                       rsItems("idItem") & "</a></td>" & _
            "  <td>" & rsItems("issue") & "</td>" & _
            "  <td>" & rsItems("cond") & "</td>" & _
            "  <td>" & rsItems("month") & "</td>" & _
            "  <td>19" & rsItems("year") & "</td>" & _
            "</tr>"
          rsItems.MoveNext
        Loop
        Response.Write "</table>"
      Else                                          ' user has no items in the database
        Response.Write "<center><h2>No comics saved in database</h2></center>"
      End If
      rsItems.close
    %>

    My DB field changes the fieldname from "ItemID" to "idItem" but that shouldn't affect the function ..

    The code on the Item.asp page should receive the idItem value and display the relevant data in the form fields. But it doesn't. It just displays the data from the first row of the DB table "Item" ... Here's the Item.asp code:


    Code:
    <%
      If blnNew Then %>
        <input type="Hidden" name="idItem" value=""> <%
      Else %>
        <input type="Hidden" name="idItem" value="<%= Request("Item") %>"> <%
      End If
    %>

    Subsequent to this problem, I have the names of the comic titles ("Amazing Spider-Man", "Journey into Mystery") stored in a separate table, "Title". This is good DB practice, right? But when I try to substitute the Table name "Item" in the ViewMyComics.asp page, I get a SQL error message complaining about the syntax. This is a SQL statement that works just fine on the page that displays the actual table of comics, which can be seen here:

    http://www.thestoryworks.com/publishing/comics/collecting/default.asp

    The SQL statement that I know works elsewhere is:


    Code:
    Dim rsItem
            Set rsItem = Server.CreateObject("ADODB.Recordset")
            strSQL = "SELECT Title.titleName, Item.idItem, Item.issue, Item.[month], Item.[year], " & _
          " Item.cond, Item.gcdb, Item.image, Item.cents, Item.have, Item.want, Item.note " & _
          " FROM Title INNER JOIN Item ON Title.idTitle = Item.idTitle " & _
          " ORDER BY titleName,month;"
    
            rsItem.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic, adCmdText

    So how do I retrieve the comic book title to include in the ViewMyComics.asp page, and how do I pass the correct ID to the Item.asp page?

    Any help would be hugely appreciated ...

    Best,

    MattiMan

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Ok: when you view source on ViewMyComics.asp, is there an ID number in the link it's generating? (ie: after item.asp?Action=Edit&Item=)

    If not, there's your problem. There's definitely a field in your database called "idItem"? And it's got ID numbers in it?

    The second bit - pulling out the title for a given Item ID:

    strSQL = "SELECT Title.titleName, Item.idItem, Item.issue, Item.[month], Item.[year], " & _
    " Item.cond, Item.gcdb, Item.image, Item.cents, Item.have, Item.want, Item.note " & _
    " FROM Title INNER JOIN Item ON Title.idTitle = Item.idTitle " & _
    " WHERE Item.idItem= " & cInt(RequestQuerystring("Item"))

  • #3
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    I personally recommend against using the cInt function in Spudhead's code as once the database grows in size it WILL throw an error once the DB hits record number 32768, and for every subsequent record after that. Besides, it isn't needed in the SQL statement.

    Do definately use SQL to open the Table. Opening the Table directly using adCmdTable works but slows down as more records are added and eventually crawls as it has to go through each and every record to find the correct record. Once the table gets more than a few thousand records in it, users will think the page is not responding.

  • #4
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Quote Originally Posted by miranda View Post
    I personally recommend against using the cInt function in Spudhead's code as once the database grows in size it WILL throw an error once the DB hits record number 32768, and for every subsequent record after that. Besides, it isn't needed in the SQL statement.
    True that cInt is not the ideal function - but having no function at all to clean the input is even worse. Otherwise you're allowing people to execute arbitrary code against your database; otherwise known as a SQL injection attack.

  • #5
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Why not just use IsNumeric() instead? Solves the problem of integers > 32768 and doesn't allow anything but numbers?

    Code:
    if IsNumeric(request.querystring("Item")) then
      strSQL = "SELECT Title.titleName, Item.idItem, Item.issue, Item.[month], Item.[year], " & _
        " Item.cond, Item.gcdb, Item.image, Item.cents, Item.have, Item.want, Item.note " & _
        " FROM Title INNER JOIN Item ON Title.idTitle = Item.idTitle " & _
        " WHERE Item.idItem= " & RequestQuerystring("Item")
    else
        'throw error message, use a default value, or response.redirect to another page.
    end if
    Just another set of eyes
    Last edited by Daemonspyre; 07-03-2007 at 09:40 PM. Reason: Corrected Misspelling
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #6
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Spudhead View Post
    Ok: when you view source on ViewMyComics.asp, is there an ID number in the link it's generating? (ie: after item.asp?Action=Edit&Item=)

    If not, there's your problem. There's definitely a field in your database called "idItem"? And it's got ID numbers in it?
    Yes, there is an ID number in the link generated by the ViewMyComics.asp page, but it doesn't seem to do anything when it reaches the Item.asp page ... so either the syntax of the link is wrong or the syntax of the hidden fields in the Item.asp page is wrong ...

    Quote Originally Posted by Spudhead View Post
    The second bit - pulling out the title for a given Item ID:

    strSQL = "SELECT Title.titleName, Item.idItem, Item.issue, Item.[month], Item.[year], " & _
    " Item.cond, Item.gcdb, Item.image, Item.cents, Item.have, Item.want, Item.note " & _
    " FROM Title INNER JOIN Item ON Title.idTitle = Item.idTitle " & _
    " WHERE Item.idItem= " & cInt(RequestQuerystring("Item"))
    OK, thanks, I'll try this ...

    Best,

    MattiMan

  • #7
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by miranda View Post
    I personally recommend against using the cInt function in Spudhead's code as once the database grows in size it WILL throw an error once the DB hits record number 32768, and for every subsequent record after that. Besides, it isn't needed in the SQL statement.

    Do definately use SQL to open the Table. Opening the Table directly using adCmdTable works but slows down as more records are added and eventually crawls as it has to go through each and every record to find the correct record. Once the table gets more than a few thousand records in it, users will think the page is not responding.
    So, if I do use a SQL statement, do I need to remove adCmdTable from the line:

    Code:
    rsItems.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
    ?

    Could this be what's giving me the syntax error of that very same line?

    Best,

    Matti

  • #8
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Spudhead View Post
    Ok: when you view source on ViewMyComics.asp, is there an ID number in the link it's generating? (ie: after item.asp?Action=Edit&Item=)

    If not, there's your problem. There's definitely a field in your database called "idItem"? And it's got ID numbers in it?

    The second bit - pulling out the title for a given Item ID:

    strSQL = "SELECT Title.titleName, Item.idItem, Item.issue, Item.[month], Item.[year], " & _
    " Item.cond, Item.gcdb, Item.image, Item.cents, Item.have, Item.want, Item.note " & _
    " FROM Title INNER JOIN Item ON Title.idTitle = Item.idTitle " & _
    " WHERE Item.idItem= " & cInt(RequestQuerystring("Item"))
    H'mm ... the server doesn't much like this SQL string as it throws up an error ...

    Code:
    Microsoft VBScript runtime  error '800a000d'
    
    Type mismatch: 'RequestQuerystring'
    
    /publishing/comics/collecting/admin/viewmycomics3.asp, line 41
    line 41 being the first line of the SQL statement ...

    Best,

    Mattiman

  • #9
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    It's Request.Querystring You need to add a .
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #10
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by MattiMan View Post
    So, if I do use a SQL statement, do I need to remove adCmdTable from the line:

    Code:
    rsItems.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
    ?

    Could this be what's giving me the syntax error of that very same line?

    Best,

    Matti


    ABSOLUTELY!!! AdCmdTable Is used only when you are opening a table Directly with no SQL Query

    And Spudhead is very correct you need to do something to prevent SQL interjection attacks

    here is a small function that will do just that
    Code:
    Public Function preventInjection(theString) 
       ' replace each character with it's ascii equivalent
        Replace(theString, ";", "&# 59;")  'removes semicolon 
        Replace(theString, "'", "&# 39;") 'removes lone apostrophe's ' 
        Replace(theString, "--", "&# 45;&# 45;") 'removes double dash sql comment 
    End Function 
    
    'REmove the spaces after each of the # signs    it is only there to prevent the forum from displaying the item
    Another way to prevent interjection attacks is to do as little as possible in the SQL statements. for example in a login check instead of the query checking to see if the UserID exists and that the password entered matches the password field, Check for the userid only, then grab the record and pass the password column value into a variable and then compare the variable to the value of the info entered by the user. As far as entering data goes, you can use an ADO insert to enter the data or a Saved Query(Access)/Stored Procedure(SQL Server)
    Last edited by miranda; 07-04-2007 at 02:20 AM.

  • #11
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Daemonspyre View Post
    It's Request.Querystring You need to add a .
    Right, that gets me a bit further down the page before throwing up a new error message:

    Code:
    Microsoft VBScript compilation  error '800a03f6'
    
    Expected 'End'
    
    /publishing/comics/collecting/admin/item3.asp, line 141
    And this is line 141, which is the value of the Submit button:

    Code:
    value="<% If blnNew Then %>Add New Item<% Else %>Update Item<% End If %>">

  • #12
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, sorry, sorry ... that's the result I get when I add that SQL string to the Item.asp page.

    When I use it on the viewmycomics.asp page it returns a page saying there are no comics in the database, which I know isn't true because when using the AdCmdTable version with a direct ref to the table name ("Item"), the page returns a list of comics ...

    -> sigh <-

    MattiMan

  • #13
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    response.write() your SQL string to the page, copy it, and run it directly in the SQL pane of your Access database. You'll then know if it's a problem with your SQL or with your ASP.


  •  

    Posting Permissions

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