Display DB Records In Label?

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

ALTER PROCEDURE dbo.OrderCount
@.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 namedGetOrderCount which returns aSqlDataReader back to the calling ASPX page. I want the ASPX page to display the 3 OrderIDs ofUserID=6 in aLabel control. Unlike the DataBinding controls likeDataList,DataGrid,Repeater controls, theLabel control will not automatically loop through the recordset.

So I tried to accomplish this using aFor...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)

in the above shown ASPX code. Can someone please correct me & suggest how do I loop through the recordset so that I can display the records in aLabel control?

Hi,

Replace these lines:
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text = sqlReader.GetValue(1)(i)
Next i

with this line where your column id is 1:
lblLinks.Text &= sqlReader.GetValue(1)

|||

RN5A:

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

ALTER PROCEDURE dbo.OrderCount
@.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

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

If I understand your issue correctly, the stored procedure will return several rows with 2 columns--the first column is identical for all rows, and the number of returned rows (let's say N) is determined by the qualified records, so there are N OrderIDs. Then when using SqlDataReader, you only need a single loop to go through all records, no matter what's the value of OrderCount; and you can just use SqlDataReader.GetValue(1) to access the OrderID field in each returned row, not SqlDataReader.GetValue(1)(i). So the OrderCount column seems to be redundant in this case.

|||Well mates......I am sorry to say that I forgot to mention one very important point in post #1. I want to display the 3OrderIDs ofUserID=6 in theLabel control but theText of theLabel should not be theOrderIDs themselves. In other words, theLabel should not display

8 17 29

which are the 3OrderIDs ofUserID=6. Rather theText of theLabel, 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. sinceUserID=6 has 3OrderIDs, it means thatUserID=6 has placed 3 orders - theOrderID of the first order being 8, theOrderID of the second order being 17 & theOrderID of the third order being 29. Hence theLabel control should display

1 2 3

toUserID=6 since he has placed 3 orders. Similarly, if a user has placed, say, 8 orders, theLabel should display

1 2 3 4 5 6 7 8

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

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

theLabel control displays the value ofi more than once since its looping inside a loop. So forUserID=6, theLabel control displays the followingText:

1 2 3 1 2 3 1 2 3

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

Hi,

You can try doing this:
Declare a counter variable before your while loop
dim counter as integer = 1

Replace these lines:
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text += i & " "
Next

with these line
lblLinks.Text &= counter & " "
counter++

|||Thanks a lot, Anjin....that's exactly what I was looking out for. It was really stupid of me to ask the follow-up question which you answered. I should have done that myself but the day I was working on it, it was one of those days when the brain just refuses to work!!|||

hi,

not to argue with, though your code will work fine but still better would be to user stringbuilder and append text to it rather using string i.e. lbl.text +="";

like

StringBuilder sb = new StringBuilder();

sb.append(whatever you like);

appending to stringbuilder has performance advantage.

regards,

satish

|||Thanks, Satish, for the alternative but simply using

sb.Append(i)

won't display anything on the ASPX page for the users to view. To display the output produced by using theAppend method of theStringBuilderclass, something like

lblLinks.Text += sb.Append(i)

has to be used, isn't it? So why unnecessarily useStringBuilder as shown in the preceding line when the following line will generate exactly the same output?

lblLinks.Text += i

So I doubt whether using StringBuilder (atleast in my case) will indeed have any performance advantage. Correct me if I am wrong.|||

errrr

sorry mate

first you keep appending the text/values etc in stringbuilder in loop or whatever, then after loop finishes use

lable.text = sb.ToString();

regards,

satish