Using a DataGrid with a Stored Procedure with multiple selects

[apwestgarth]

Hello,
I am trying to use a datagrid to display the results of a stored procedure. The Stored procedure has multiple select statements. I am only able to extract the results of the first query - what do I need to do to display the results of all in one query??

the stored procedure code:

CREATE PROCEDURE che_wsales_total AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @period varchar(10), @startp varchar(50), @finp varchar(50), @starty varchar(50)
SET @period= 'PERIOD 10'
set @startp='2003-09-28'
set @finp='2003-11-01'
set @starty='2002-12-29'
set datefirst 1

select
@period,
left(getdate()-(datepart(dw,getdate())),12),
sum(SOP_ORDER_TOTAL_CURRENCY)
from SALES_ORDER_HEADERS
where ORDER_DATE
between getdate()-datepart(dw,getdate()) -1
and getdate()-datepart(dw,getdate())+6
and SALES_ORDER_TYPE <> 'XFER'

select
convert(decimal(10,0),
sum(c.ORDER_QUANTITY))
from
dbo.SALES_ORDER_HEADERS as a
JOIN SALES_ORDER_LINE_QTYS as c
ON a.COMPANY_CODE=c.COMPANY_CODE
and a.DIVISION=c.DIVISION
and a.ORDER_NUMBER=c.ORDER_NUMBER
where a.ORDER_DATE
between getdate()-datepart(dw,getdate())-1
and getdate()-datepart(dw,getdate())+6
and a.SALES_ORDER_TYPE <> 'XFER'

select
sum(SOP_ORDER_TOTAL_CURRENCY)
from
SALES_ORDER_HEADERS
where ORDER_DATE
between @startp
and @finp
and SALES_ORDER_TYPE <> 'XFER'

select
convert(decimal(10,0),sum(c.ORDER_QUANTITY))
from
dbo.SALES_ORDER_HEADERS as a
JOIN
SALES_ORDER_LINE_QTYS as c
ON
a.COMPANY_CODE=c.COMPANY_CODE
and a.DIVISION=c.DIVISION
and a.ORDER_NUMBER=c.ORDER_NUMBER
where a.ORDER_DATE
between @startp and @finp
and a.SALES_ORDER_TYPE <> 'XFER'

select
sum(SOP_ORDER_TOTAL_CURRENCY)
from
SALES_ORDER_HEADERS
where ORDER_DATE
between @starty
and @finp
and SALES_ORDER_TYPE <> 'XFER'

select
convert(decimal(10,0),sum(c.ORDER_QUANTITY))
from
dbo.SALES_ORDER_HEADERS as a
JOIN
SALES_ORDER_LINE_QTYS as c
ON
a.COMPANY_CODE=c.COMPANY_CODE
and a.DIVISION=c.DIVISION
and a.ORDER_NUMBER=c.ORDER_NUMBER
where a.ORDER_DATE
between @starty and @finp
and a.SALES_ORDER_TYPE <> 'XFER'

GO

my vb.net code

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As New SqlConnection(ConfigurationSettings.AppSettings(&quot;rossTest&quot)
Dim comm As New SqlCommand(&quot;che_wsales_total&quot;, conn)
comm.CommandType = CommandType.StoredProcedure
conn.Open()
dgSales.DataSource = comm.ExecuteReader(CommandBehavior.Default)
dgSales.DataBind()
conn.Close()


End Sub

Thanks

Andrew


Andrew Westgarth
Web Developer
ICQ: 14419001
MSN: mail@hawaythelads.co.uk

 

[hf28]

Hi Andrew,

First, I think you should say in your SP
BEGIN after setting your variables and before your Select's

then say END before GO

Second, in my VB.NET code, even if I imported SQLClient, I still say:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Create SQL Command to execute the SP
Dim connSQl As New SqlClient.SqlConnection(&quot;data source=NJ1-1183\VSdotNET;initial catalog=NSRCG;TRUSTED_CONNECTION=TRUE&quot

Dim cmdSQL As New SqlClient.SqlCommand (&quot;spx_SummaryStatusUpdate&quot;, connSQl)
connSQl.Open()
cmdSQL.CommandType = CommandType.StoredProcedure
Try
cmdSQL.ExecuteNonQuery()
Catch exSQL As SqlException
Stop
End Try

connSQl.Close()
cmdSQL.Dispose()

End Sub

Check if it helps

 

[BG12424]

Try using the reader's NextResult() method.

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.