How To Populate a Label Control from SQL Server Database

[dwayned]

HI I am using
- ASP.Net
- C#
- SQL Server Express 2008 R2

I have a table called customers which contains all customer details and a stored procedure which gets:

- Total No Of Customers
- Total No Of Customers in Region1
- Total No Of Customers in Region2
etc etc...

I would like to then populate these results into a "statistics" page on my site/app using the label control (or any other method if easier).

So far I have the Stored Procedure:

CREATE PROCEDURE [usp_getStats]
	
AS

-- Count Region1 Accounts
SELECT COUNT(custID) FROM custDetail WHERE region = '1'

-- Count Southern Accounts
SELECT COUNT(custID) FROM custDetail WHERE region = '2'

--Count Total Accounts
SELECT COUNT(custID) FROM custDetail

--Count Total Pending Accounts
SELECT COUNT(custID) FROM custDetail WHERE accountCreated='1'

--Count Total Inactive Accounts
SELECT COUNT(custID) FROM custDetail WHERE accountLive = '1'

--Count Total backup Usage Region1
SELECT SUM(quota) FROM custDetail WHERE region='1'

--Count Total backup Usage Region2
SELECT SUM(quota) FROM custDetail WHERE region='2'

--Count Total Backup Usage Total
SELECT SUM(quota) FROM custDetail

Code Behind for page is;

public partial class backupstats : System.Web.UI.Page
{
    DataSet ds = new DataSet();
    SqlConnection con;
    SqlCommand cmd = new SqlCommand();

protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection("Data Source=SERVERNAME\\SQLEXPRESS2008;Initial Catalog=DBNAME;Integrated Security=SSPI");

        cmd = new SqlCommand("usp_getBackupStats", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet data = new DataSet();
        da.Fill(data);

        //To add code here that populates labels....
        label1.text = ???

    }
}

Do I need to create seperate variables in the Stored Proc and then call them from C# code?
If so could somebody give me an example or point me towards a tutorial, I have searched quite a bit on this.

thanks very much
Dwayne

[finito]

your code looks good but where is con.Open()

what error are you getting?

[Lusiphur]

your code looks good but where is con.Open()

Actually, I think that the adapter/fill will open the connection, what's missing is the close of the connection and the actual logic for populating his data.

That being said, in the stored proc on the SQL side it's performing several select statements without naming them individually. There are 5 counts and 3 sums being generated with no variable names associated to be called upon on the ASP.Net end of things. What this would generate is 8 return values without names that would be difficult to populate to a dataset as typed.

What is needed here is some output control on the SQL end to tie each of the SELECT statements to a return value variable such that it can be populated under that variable name (read: column name) in the population of the dataset. Once this is accomplished the dataset can be used to populate front-end fields by referencing the dataset line & column.

Alternately, if the set is to be populated using the existing method then as long as you know which row in the dataset represents which stored SELECT it may work but I still have some doubts over the lack of variable names/columns involved.

I'm thinking that if we were to add a variable to each SELECT line and then a RETURN line to return all the variables in one 'row' of data then you would be able to call on your results from row 0 of your dataset with the returned name matching the value you need. For example if you declared variables Region1, Southern, Total, Pending, Inactive, BackupR1, BackupR2, BackupTotal then you could on the C# end do this:

dataRow dr = data.Rows[0];
labReg1.Text = dr["Region1"].ToString();
labReg2.Text = dr["Southern"].ToString();

and so forth through the list. My specific code may be off in syntax because I didn't pop it into my compiler to test it or anything but the general concept should work.

Hope this helps :)

[dwayned]

Hi Lusiphur,

Thanks for your reply..

So far I have amended my stored procedure so that it looks like this...

ALTER PROCEDURE [dbo].[usp_getBackupStats]
	
		@custCountNorth int OUTPUT,
		@custCountSouth int OUTPUT,
		@totalAccounts int OUTPUT,
		@totalPending int OUTPUT,
		@totalInactive int OUTPUT,
		@backupUsageNorth float OUTPUT,
		@backupUsageSouth float OUTPUT,
		@backupUsageTotal float OUTPUT
		
AS

-- Count Northern Accounts
SET @custCountNorth = (SELECT COUNT(custID) FROM custDetail WHERE region = '1')

-- Count Southern Accounts
SET @custCountSouth = (SELECT COUNT(custID) FROM custDetail WHERE region = '2')

--Count Total Accounts
SET @totalAccounts = (SELECT COUNT(custID) FROM custDetail)

--Count Total backup Usage North
SET @backupUsageNorth = (SELECT SUM(quota) FROM custDetail WHERE region='1')

--Count Total backup Usage South
SET @backupUsageSouth = (SELECT SUM(quota) FROM custDetail WHERE region='2')

--Count Total Backup Usage Total
SET @backupUsageTotal = (SELECT SUM(quota) FROM custDetail)

--Count Total Pending Accounts
SET @totalPending = (SELECT COUNT(custID) FROM custDetail WHERE accountCreated='1')

--Count Total Inactive Accounts
SET @totalInactive = (SELECT COUNT(custID) FROM custDetail WHERE accountLive = '1')

And my C# codebehind now looks like this...

public partial class backupstats : System.Web.UI.Page
{
    DataSet ds = new DataSet();
    SqlConnection con;
    SqlCommand cmd = new SqlCommand();

    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection("Data Source=SERVERNAME\\SQLEXPRESS2008;Initial Catalog=OBCust;Integrated Security=SSPI");

        cmd = new SqlCommand("usp_getBackupStats", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataTable dt = new DataTable();
        DataRow dr = dt.Rows[0];
        
        lblNorthCust.Text = dr["@custCountNorth"].ToString();
        lblSouthCust.Text = dr["@custCountSouth"].ToString();
        lblTotalCust.Text = dr["@totalAccounts"].ToString();
        lblNthBackup.Text = dr["@backupUsageNorth"].ToString();
        lblSthBackup.Text = dr["@backupUsageSouth"].ToString();
        lblTotalBackup.Text = dr["@backupUsageTotal"].ToString();
        lblPendingTotal.Text = dr["@totalPending"].ToString();
        lblInactiveTotal.Text = dr["@totalInactive"].ToString();


    }
}

System.I
Error Message I am getting when trying to run page is indexOutOfRangeException was unhandled by user code
Message="There is no row at position 0."
Source="System.Data"

[finito]

Which line are you getting this error?

DataRow dr = dt.Rows[0]; // ???

did you try to generate your stored procedure in the SQL Browser? Do they all give you a result?

[Lusiphur]

So, basically what I'm seeing here is "there's no row at position 0" which is translating to "your DataTable is not being populated or it would have a row at position 0".

Now, I'm seeing where you supplied variables for the results of each SELECT statement that was previously part of your stored proc... but I'm not seeing anywhere where you're returning those variables in any way to the user. This could be your issue :)

[dwayned]

Ok, should i be using a datareader to get the data or is the Stored Procedure incorrect and requires RETURN??

Sorry, I have very little experience with C#.

Even if you could point me in the right direction, I have been searching everything I can think of on google

[dnanetwork]

there is no row at position 0 means your dataset is not populated..

execute the store procedure manually and let me know the o/p

[dwayned]

Procedure or function 'usp_getBackupStats' expects parameter '@custCountNorth', which was not supplied.

[dwayned]

See below for the updated stored proc...

Any more suggestions would be welcome, thanks

ALTER PROCEDURE [dbo].[usp_getBackupStats]
	
		@custCountNorth int OUTPUT,
		@custCountSouth int OUTPUT,
		@totalAccounts int OUTPUT,
		@totalPending int OUTPUT,
		@totalInactive int OUTPUT,
		@backupUsageNorth float OUTPUT,
		@backupUsageSouth float OUTPUT,
		@backupUsageTotal float OUTPUT
		
AS

-- Count Northern Accounts
SET @custCountNorth = (SELECT COUNT(custID) FROM custDetail WHERE region = '1')

-- Count Southern Accounts
SET @custCountSouth = (SELECT COUNT(custID) FROM custDetail WHERE region = '2')

--Count Total Accounts
SET @totalAccounts = (SELECT COUNT(custID) FROM custDetail)

--Count Total backup Usage North
SET @backupUsageNorth = (SELECT SUM(quota) FROM custDetail WHERE region='1')

--Count Total backup Usage South
SET @backupUsageSouth = (SELECT SUM(quota) FROM custDetail WHERE region='2')

--Count Total Backup Usage Total
SET @backupUsageTotal = (SELECT SUM(quota) FROM custDetail)

--Count Total Pending Accounts
SET @totalPending = (SELECT COUNT(custID) FROM custDetail WHERE accountCreated='1')

--Count Total Inactive Accounts
SET @totalInactive = (SELECT COUNT(custID) FROM custDetail WHERE accountLive = '1')

[suzaine]

Message="There is no row at position 0."
I got this error too.. please help me my program is like there should be checkbox in the data grid and the label of the chekbox should rely on what is on the database of a specific field on a specific table. per label of the chekbox would be per entry on that specific field on the database. I dont know if you got me but please help if have any idea. Please.