Retrieve Record Count

[rn5a]

A SQL Server 2005 stored procedure expects a parameter 'UserID'
depending upon which it retrieves the no. of records & OrderIDs
corresponding to the 'UserID' from a DB table (note that OrderID &
UserID are two of the columns in the DB table). So for e.g. consider a
user whose UserID=6 & the DB table has 3 records whose UserID=6. In
other words, there are 3 OrderID records of the user whose UserID=6,
say, OrderID=8, OrderID=17 & OrderID=29. The stored procedure will
finally return 2 columns - the OrderCount (which is 3 for UserID=6) &
the OrderID (which will be 8, 17 & 29 for UserID=6). This is the stored
procedure:

ALTER PROCEDURE dbo.NETAllOrders
@UserID int
AS
DECLARE
@OrderCount int

SET @OrderCount = (SELECT COUNT(OrderID) FROM NETOrders WHERE UserID =
@UserID)

SELECT @OrderCount AS OrderCount, OrderID
FROM
NETOrders
WHERE
UserID = @UserID

In a VB class file, I am invoking the stored procedure in a function
named 'GetOrderCount' which returns a SqlDataReader back to the calling
ASPX page. I want the ASPX page to display the 3 OrderIDs of UserID=6
in a Label control. Unlike the DataBinding controls like DataList,
DataGrid, Repeater controls, the Label control will not automatically
loop through the resultset.

So I tried to accomplish this using a For...Next loop

Dim i As Integer
Dim sqlReader As SqlDataReader

iUserID = Request.Cookies("UserID").Value
'ShopCart is the name of the class in the VB class file
boShopCart = New ShopCart
sqlReader = boShopCart.GetOrderCount(iUserID)

While (sqlReader.Read)
If (sqlReader.GetValue(0) > 1) Then
pnlLinks.Visible = True
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text = sqlReader.GetValue(1)(i)
Next i
Else
pnlLinks.Visible = False
End If
End While

But this generates the following error:

No default member found for type 'Integer'.

pointing to the line

lblLinks.Text = sqlReader.GetValue(1)(i)

Can someone please correct me & suggest how do I loop through the
resultset so that I can display the records in a Label control?

 

[Mark Fitzpatrick]

You don't need to do it in the fashion you are. For one thing, when
assigning a variable to be a count, just use the select.

SELECT @OrderCount = COUNT(OrderID) FROM NETOrders WHERE UserID = @UserID

SELECT @OrderCount as OrderCount, OrderId FROM NETOrders WHERE UserID =
@UserID

The reason you're getting the error comes from the fact that @OrderCount
wasn't initialised to any sort of value. Then the set statement didn't
actually assign it a value since it needed to be done as part of a select,
so the integer still remained uninitialized.

If you're going to loop through records to add the values to a label
control, you can't assign them the way you're doing it. Label.Text =
somevalue will just keep setting the label's text to a new value each time.
You need to set the label's text property to be equal to the current label's
text property, plus the new value.

For example:

lblLinks.Text = (lblLinks.Text & " " & sqlReader.GetValue(1)(i))

That's roughly it, though I could be off as I'm used to C# syntax. I added a
space in there in the concatenation to make it a little easier to read.

 

[rn5a]

Well Mark.......I am sorry to say that I forgot to mention one very
important point in post #1. I want to display the 3 OrderIDs of
UserID=6 in the Label control but the Text of the Label shouldn't be
the OrderIDs themselves. In other words, the Label shouldn't display

8 17 29

which are the 3 OrderIDs of UserID=6. Rather the Text of the Label
control, which will reflect how many orders a user has placed, should
start from 1 & increment by 1 till the total number of orders a user
has placed. For e.g. since UserID=6 has 3 OrderIDs, it means that
UserID=6 has placed 3 orders - the OrderID of the first order being 8,
the OrderID of the second order being 17 & the OrderID of the third
order being 29. Hence the Label control should display

1 2 3

to UserID=6 since he has placed 3 orders. Similarly, if a user has
placed, say, 8 orders, the Label should display

1 2 3 4 5 6 7 8

& not the OrderIDs of the 8 orders. That's precisely the reason why I
used the For....Next loop in the code shown in post #1 but the problem
is since the For....Next loop has to be within a While loop to read the
SqlDataReader....something like this:

While (sqlReader.Read)
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text += i & " "
Next
End While

the Label control displays the value of 'i' more than once since its
looping inside a loop. So for UserID=6, the Label control displays the
following Text:

1 2 3 1 2 3 1 2 3

Any idea how do I resolve this problem or any other suggestiona?

Thanks