Loop through DBase until it finds matching..?!

[cyprus106]

I've got a database with a list of specific parts. The list of parts is displayed on my 'automotive.asp' page and then if you click the 'more info' link on the page, it loads up 'moreinfo.asp?Item= and the name of the item.
Then in moreinfo, I load up the database and I want it to say loop through until the table items name = the Request("Item&quot name.

I tried a 'do...until' but I know I'm doing this wrong somehow. I'll post the code I can but I think I need a new way of doing this.

;note: the strConnect is, of course, my connection string

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.Open strConnect

Dim strSQL
Dim rsItem

set rsItem = Server.CreateObject("ADODB.Recordset&quot
strSQL = "SELECT * FROM Items;"

rsItem.Open strSQL, objConn


do
if Request("Item&quot = "R-1 Strut Bars" then
response.write _
"writes lots of stuff..."
rsItem.MoveNext
end if
Loop until Request("Item&quot = rsItem("Name&quot


I'm fairly new with ASP. If there's another way I should do this or you need something else let me know.

Thanks a lot, I've been working for a full week on this stupid page and it's the absolute last little piece I have left. Cyprus

 

[rtshort]

Try &quot;Select * from Items where Name = '&quot; & <%=Request.Form(&quot;Item&quot%> & &quot;'&quot;
(or some variation there of)


That way it will only select the part names that match, then do what you want with it. Doing it the way you were trying will stop the search when 1 item is matched. If there are more of the same name, they will not be retreived. Rob
Just my $.02.

 

[khazmir]

I would ask for the item in the query and then display the results.

Dim objConn
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open strConnect

Dim strSQL
Dim rsItem
Dim sName

sName = Request(&quot;Item&quot

set rsItem = Server.CreateObject(&quot;ADODB.Recordset&quot
strSQL = &quot;SELECT * FROM Items where rsItem(&quot;Name&quot = sName;&quot;

rsItem.Open strSQL, objConn


While Not rsItem.EOF
response.write _
&quot;writes lots of stuff...&quot;
rsItem.MoveNext
Wend

 

[khazmir]

Noticed I was having a very bad variable syntax moment in my previous post:

strSQL = &quot;SELECT * FROM Items where rsItem(&quot;Name&quot = sName;&quot;

should have been

strSQL = &quot;SELECT * FROM Items where Name = '&quot; & sName & &quot;'&quot;

 

[Tarwn]

I would make an additional suggestion, if the parts have id's as well as names, I would pass the id and use that in your query. Usually id's are much easier for the database to work with, especially if it is an incremental numeric id (ie autonumber for access users). This is also a good way to define the relationships for your database, si it cvuts down on the possibility of error when reusing a text field as a foreign key and it cuts down on the amount of data that the database has to store.

Something along the lines of:

Table PartType:
partTypeId   primary key, autonumber or seeded incremental id
partTypeName  text field description

Table Items
itemId   primary ey, autonumber or seeded incremental id
partTypeNum   foreign key to PartType table
itemName    text field for item name
etc...

This way to list the part types for the user you could select everything from the PartType table and create links (or options in a select box):

<a href=&quot;itemview.asp?parttype=<%=rs(&quot;partTypeId&quot;)%>&quot;><%=rs(&quot;partTypeName&quot;)%></a>

and then to select all the items that are of the part type the user clicks on you could:

sqlStr = "SELECT * FROM Items WHERE partTypeNum = " & Request.QueryString("parttype")

This allows the database to create the recordset based on an indexed field, plus you don't have any issues with the possibility that an item or part-type has non-standard characters that will mess up the query. Plus you have the added benefit that your only storing the text data for part-type in a single location, instead of redundantly storing that text with every item that belongs to that category.

Sorry I got carried away, feel free to ignore this advice, although hopefully someone will find it useful

-Tarwn [sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[cyprus106]

Thanks, I think I will use the autonumber, (I'm using Access). I was hesitant to at first because originally I was going to create some of the rows for the tables on thesite itself, and, for some reason, when I use ASP to add a row of data to a table, it doesn't create an autonumber for me. This may be a whole other question entirely, though... Cyprus