Fetching the same field

[Guest_imported]

I'm new to ASP and I'm trying my hands on Database. Can you please help me out with the following.
I have a table with two fields Order Number and Item. And the data in the table will be like:
OrderNumber Item
---------- ----
A001 Mouse
A001 Modem
A002 Monitor
A003 Speaker
What I would like to know is how can I code an ASP page where the Order Number A001, which is repeated twice, will be treated as the same Order Number. And it will show the following result based on a search form.
The Order Number A001 has Mouse, Modem
Instead of:
The Order Number A001 has Mouse
The Order Number A001 has Modem
I'm able to code but I cannot make my code understand both A001 is the same Order Number. My code takes them as different Order Numbers.
Any Help regarding this problem will be highly appreciated. Thanks in advance. Waiting eagerly for your reply.
Best regards,
Soumya Shankar Ghosal
ssghosal@caltiger.com
The code I tried is as follows: -
----------------------------------
<%@ Language=VBSCRIPT %>
<% Option Explicit %>
<!--include virtual=&quot;/adovbs.inc&quot;-->
<%
'Creating a connection to the Access database
Dim objConnection
Set objConnection = Server.CreateObject(&quot;ADODB.Connection&quot
objConnection.ConnectionString = &quot;DSN=My.dsn&quot;
objConnection.Open
Dim strSQL
strSQL = &quot;SELECT * FROM OrderInfo WHERE OrderNo='A001'&quot;
'Creating a recordset object instance and executing the SQL statement
Dim objRecordset
Set objRecordset = server.CreateObject(&quot;ADODB.Recordset&quot
objRecordset.Open strSQL, objConnection
'Trapping a Recordset field in a variable tmp
Dim tmp
objRecordset.MoveFirst
tmp = objRecordset.Fields(&quot;OrderNo&quot & &quot;<P>&quot;
Response.Write &quot;The number is : &quot;
Response.Write tmp
If objRecordset.EOF Then
'No records were returned
Response.Write &quot;No matches found based on your Search&quot;
Else
Do While Not objRecordset.EOF
'If (StrComp(objRecordset(&quot;OrderNo&quot, tmp, vbTextCompare)=1) Then
Response.Write objRecordset(&quot;OrderNo&quot & &quot;<BR>&quot;
Response.Write objRecordset(&quot;Item&quot & &quot;<BR>&quot;
Response.Write &quot;<P>&quot;
'objRecordset.MoveNext
'tmp = objRecordset.Fields(&quot;OrderNo&quot
'End If
objRecordset.MoveNext
'tmp = objRecordset.Fields(&quot;OrderNo&quot
Loop
End If
objRecordset.Close
Set objRecordset = Nothing
objConnection.Close
Set objConnection = Nothing
%>

 

[swilliams]

Doing this is usually handled by a &quot;cross-tab&quot; report in Access reports, or Crystal Reports, but it can be handled in HTML by using the following code.

<%
Dim con, rs, sSql, sConString, sOutput
Set con = Server.CreateObject(&quot;ADODB.Connection&quot
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
sConString = &quot;Data Source=&quot; & Server.Mappath(&quot;db/db1.mdb&quot & &quot;;Provider=Microsoft.Jet.OLEDB.4.0;&quot;
con.Open sConString

sSql = &quot;SELECT OrderNo, Item FROM OrderInfo WHERE OrderNo='A001'&quot; ' you may want an ORDER BY clause here
rs.Open sSql, con

' build output string
Do Until rs.EOF
If Len(sOutput) = 0 Then
sOutput = &quot;The Order Number has &quot; & rs(&quot;Item&quot
Else
sOutput = sOutput & &quot;, &quot; & rs(&quot;Item&quot
End If
rs.MoveNext
Loop

' write output
If Len(sOutput) = 0 Then
Response.Write &quot;No Items found for 'A001'&quot;
Else
Response.Write sOutput
End If

' housekeeping
rs.Close
con.Close
Set con = Nothing
%>



Simon