Placing a Stored Procedure Expression in .Net

[edb]

Hi again

I have a stored procedure that looks like this.
CREATE PROCEDURE usp_GetCustomers AS

SELECT customer_id, customer, COUNT(*)
FROM dbo.Customer
GROUP BY customer_id, customer
UNION ALL
SELECT NULL, NULL, COUNT(*) AS Expr1
FROM dbo.Customer
GO

It works great in Query Analyzer and Syntax is successful
in SQL 2000. The expression in Query Analyzer is read as a
field with no name. The total comes up 33 which is Expr1,
what I need.

How do I grab this Stored Procedure and place it into a
label in .Net?

I really appreciate your input. I am not too smart.

Thx bunches

edb

[William Ryan]

You need to create a connection object and give it a valid connection
string. You'll need a command object and set it's connection to the
aforementioned connection. Set the CommandText of the Command to
"usp_GetCustomers". Declare a Datareader object. Make sure the connection
is open...
if cn.State <> ConnectionState.Open then cn.Open

then seet the commandtype to StoredProcedure.

finally, hit myDataReader = cmd.ExecuteReader, enlcose this in a try catch
block, same for your connection.Open statement.

After that,...

While dr.Read
myLabel.text &= dr(0)
End While

finally, close the reader dr.Close

However, I don't see how you can set a set like this to a Label's text. It
will be unreadable. Whatever you want to do with it, remember that DR(0)
will be customer_id, dr(1) will be customer and dr(3) will be Count.

HTH,

Bill
"edb" <m1**@dm2000.com> wrote in message
news:b4****************************@phx.gbl...

Hi again

I have a stored procedure that looks like this.
CREATE PROCEDURE usp_GetCustomers AS

SELECT customer_id, customer, COUNT(*)
FROM dbo.Customer
GROUP BY customer_id, customer
UNION ALL
SELECT NULL, NULL, COUNT(*) AS Expr1
FROM dbo.Customer
GO

It works great in Query Analyzer and Syntax is successful
in SQL 2000. The expression in Query Analyzer is read as a
field with no name. The total comes up 33 which is Expr1,
what I need.

How do I grab this Stored Procedure and place it into a
label in .Net?

I really appreciate your input. I am not too smart.

Thx bunches

edb

[Thys Brits]

Hi,

I'd like to propose a different solution. I assume you are looking to retrieve a list of clients, and the display the total at the
bottom of the page? The code William put forth will work for listing the clients, but then I'd remove the Union All from the stored
procedure, and do the following:

CREATE PROCEDURE usp_GetCustomers AS

SELECT customer_id, customer, COUNT(*)
FROM dbo.Customer
GROUP BY customer_id, customer

SELECT COUNT(*) AS Customers
FROM dbo.Customer
GO

Then in your code, you can iterate through the list as follows:

While dr.Read
myLabel.text &= dr(0)
End While

Then, to get to the total:

If (dr.NextResult())
If (dr.Read())
totalLable.Text = dr(0).ToString()
End If
End If

HTH

Thys Brits
MCSD/MCSD.Net

"edb" <m1**@dm2000.com> wrote in message news:b4****************************@phx.gbl...
Hi again

I have a stored procedure that looks like this.
CREATE PROCEDURE usp_GetCustomers AS

SELECT customer_id, customer, COUNT(*)
FROM dbo.Customer
GROUP BY customer_id, customer
UNION ALL
SELECT NULL, NULL, COUNT(*) AS Expr1
FROM dbo.Customer
GO

It works great in Query Analyzer and Syntax is successful
in SQL 2000. The expression in Query Analyzer is read as a
field with no name. The total comes up 33 which is Expr1,
what I need.

How do I grab this Stored Procedure and place it into a
label in .Net?

I really appreciate your input. I am not too smart.

Thx bunches

edb