...

View Full Version : Passing ItemID from one page to next



MattiMan
07-03-2007, 09:40 AM
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 ...



<%
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:



<%
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 (http://http://www.thestoryworks.com/publishing/comics/collecting/default.asp)

The SQL statement that I know works elsewhere is:



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

Spudhead
07-03-2007, 12:48 PM
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"))

miranda
07-03-2007, 05:19 PM
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.

Spudhead
07-03-2007, 06:16 PM
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.

Daemonspyre
07-03-2007, 06:27 PM
Why not just use IsNumeric() instead? Solves the problem of integers > 32768 and doesn't allow anything but numbers?



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

MattiMan
07-03-2007, 09:12 PM
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 ...


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

MattiMan
07-03-2007, 09:31 PM
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:


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

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

Best,

Matti

MattiMan
07-03-2007, 10:00 PM
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 ...


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

Daemonspyre
07-03-2007, 10:28 PM
It's Request.Querystring You need to add a .

miranda
07-04-2007, 02:14 AM
So, if I do use a SQL statement, do I need to remove adCmdTable from the line:


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


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)

MattiMan
07-04-2007, 08:14 AM
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:


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:


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

MattiMan
07-04-2007, 09:39 AM
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

Spudhead
07-04-2007, 06:12 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum