Only 1 entry from SQL table gets returned onto the ASP page

[dreamaz]

Hi folks,

When submitting a query, only 1 entry seems to be returned from the SQL table. WHen i perform a 'test' (in dreamweaver) i get all entries matching the query. (hope this makes sense!)

I have an ASP page connecting to an SQL database table (with inventory) I would like ALL entries matching the users query to be returned instead of just the top entry


Any ideas?

 

[onpnt]

can you post your SQL statement _______________________________________________
[sub]{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }[/sub]
_______________________________________________
for the best results to your questions: FAQ333-2924
has you're questio

 

[dreamaz]

SELECT *
FROM dbo.Inventory

thats the most i could get since dreamweaver uses simple sql.

 

[Tarwn]

That would return what your looking for, the problem must be located in your code, could we see the portion where you are looping throuigh the recordset?

-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[dreamaz]

Here it is..

well, all i have is (this will look messy)

Dim Inventory
Dim Inventory_numRows

Set Inventory = Server.CreateObject("ADODB.Recordset&quot
Inventory.ActiveConnection = MM_test_STRING
Inventory.Source = "SELECT * FROM dbo.Inventory"
Inventory.CursorType = 0
Inventory.CursorLocation = 2
Inventory.LockType = 1
Inventory.Open()

Inventory_numRows = 0
%>
<%
Dim rs2__MMColParam
rs2__MMColParam = &quot;1&quot;
If (Request.Form(&quot;textfield&quot <> &quot;&quot Then
rs2__MMColParam = Request.Form(&quot;textfield&quot
End If
%>



<%=(Inventory.Fields.Item(&quot;ProductMake&quot.Value)%></font></font><font color=&quot;#990000&quot; size=&quot;3&quot; face=&quot;Tahoma&quot;><%=(Inventory.Fields.Item(&quot;ProductDescription&quot.Value)%>

Thats the code to display the results..


Thanks for the help!!

 

[Tarwn]

I don't see a loop.

The reason it's only displaying one row is because you only ask for one row, what you weill nee to do in order to display all the rows is to put the display code in a loop and keep incrementing forward in the recordset, like so:

<%
Do Until Inventory.EOF
   Response.Write (Inventory.Fields.Item(&quot;ProductMake&quot;).Value)
   Response.Write &quot;</font></font><font color=&quot;&quot;#990000&quot;&quot; size=&quot;&quot;3&quot;&quot; face=&quot;&quot;Tahoma&quot;&quot;>&quot; & Inventory.Fields.Item(&quot;ProductDescription&quot;).Value
   Inventory.MoveNext
Loop
%>

Basically this says keep going until you hit the EOF (End Of File), on each run print out the ProductMake value and ProductDescription value then move to the next row.

One thing you will need to fix (from a display standpoint) is the two end font tags. I don't see where you are starting those two font tags or where you are ending the last font tag. This will probably play havoc with your look and feel. My suggestion would be to get rid of them all together, then go back and add them in after it is at least printing the results out. Another problem will be the lack of line breaks and the lack of a space between our two field values.

Hope this helped,
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[dreamaz]

Hi Thanks for the update.

I made those changes and it seems to be working now EXCEPT, its returning ALL rows inthe database instead of the ones that were part of the query (ie. the search if for HDs, it returns all HDs and CPUS and everything in the database?)

Thanks for your help

 

[Mdiaz]

You need to filter only the records you want like:

Inventory.Source = &quot;SELECT * FROM dbo.Inventory WHERE ProductMake ='&quot; & strTypeProduct & &quot;'&quot;

Where strProductType = to the field that contains the type product.

Mike Diaz...