Duplicate values 1

[ReportingAnalyst]

I am testing my asp page against the Northwind database. I would like to see all the categories in the categories table. Why do I see 8 rows of the value "Beverages"

My code is below:

<%

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CategoryID, CategoryName FROM Categories order by CategoryName"

rs.open sSQL, Conn

If not rs.EOF then
	    aCategory = rs.GetRows()

		'Close the Recordset object
		rs.Close
		'Delete the Recordset Object
		Set rs = Nothing

		'Declare Constants for the above SQL columns for better readability
		'Use these Constants instead of referring to the array numeric indexes
		Const c_Cat_ID = 0
		Const c_CatName = 1

		CatID = aCategory(c_CatID,iRowLoop)

		Dim iRows

		For iRows = 0 to UBound(aCategory, 2)
		%>
		<ul id="menu">
			<li>
				<input type="radio" checked name="catid" id="catid<%=aCategory(c_Cat_ID, 0)%>" value="<%=aCategory(c_Cat_ID, 0)%>">
				<a href="#" class="a_style"><%=aCategory(c_CatName, 0)%></a>
				<ul id="UI_<%=aCategory(c_Cat_ID, 0)%>" style="display: none">
				<%
					'Call GetProducts (CatID, Counter)
				%>
				</ul>
			</li>
		</ul>
		<%
		Next 'iRows
End If 'rs.EOF
%>

 

[Chopstik]

It does not appear that you are paging through your dataset, you're simply using your loop to go through your written list. If it were a recordset, you'd need to use .movenext. Not sure of the exact syntax for the GetRows off the top of my head...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[ReportingAnalyst]

I just narrowed down the recordset with a where clause in the SQL query.

Now why do I get this error:

Error Type:
Microsoft VBScript runtime (0x800A01FB)
An exception occurred: 'Open'
/scripts/testing2/categories.asp, line 104

Where line 104 is the bold line below.

I have a valid connection which works fine. I have the connection code at the top of the page:

<%
Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=ServerName;UID=username;PWD=password;DATABASE=Northwind"

%>

My ASP script showing the category 1

<%

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CategoryID, CategoryName FROM Categories Where CategoryID = 1 order by CategoryName"

[b]rs.open sSQL, Conn[/b]

If not rs.EOF then
	    aCategory = rs.GetRows()

		'Close the Recordset object
		rs.Close
		'Delete the Recordset Object
		Set rs = Nothing

		'Declare Constants for the above SQL columns for better readability
		'Use these Constants instead of referring to the array numeric indexes
		Const c_Cat_ID = 0
		Const c_CatName = 1

		Dim iRows

		CatID = aCategory(c_CatID,0)

		For iRows = 0 to UBound(aCategory, 2)
		%>
		<ul id="menu">
			<li>
				<input type="radio" checked name="catid" id="catid<%=aCategory(c_Cat_ID, 0)%>" value="<%=aCategory(c_Cat_ID, 0)%>">
				<a href="#" class="a_style"><%=aCategory(c_CatName, 0)%></a>
				<ul id="UI_<%=aCategory(c_Cat_ID, 0)%>" style="display: none">
				<%
					'Call GetProducts (CatID, Counter)
				%>
				</ul>
			</li>
		</ul>
		<%
		Next 'iRows
End If 'rs.EOF
%>

 

[Sheco]

The array is like this: aCategory(<column>,<row>)

but the code is like this: aCategory(c_Cat_ID, 0)

So the row is hard-coded to zero

Try this instead: aCategory(c_Cat_ID, iRows)

 

[ReportingAnalyst]

Thanks for the tip. That corrected it.