I'm trying to return 4 variables from a stored procedure to a page in ASP.Net VB Script. It's only returning the first result then breaking. My knowledge of SQL is limited. I have tried the following:
我试图从存储过程返回4个变量到ASP中的页面。净VB脚本。它只返回第一个结果,然后中断。我的SQL知识是有限的。我试过以下方法:
SQL:
SQL:
Alter PROCEDURE [dbo].[spDashboardPaymentRequests]
@id integer
AS
SELECT COUNT(Receiptno) as requestsSent
FROM [demofeepay3].[dbo].[vwallrequests]
Where Orgid = @id
SELECT Sum(totamount) as requestTotal
FROM [demofee].[dbo].[vwallrequests]
Where Orgid = @id
SELECT Sum(totamount) as requestTotalPaid
FROM [demofee].[dbo].[vwallrequests]
Where Orgid = @id AND status = 'paid'
SELECT Sum(totamount) as requestTotalUnpaid
FROM [demo].[dbo].[vwallrequests]
Where Orgid = @id AND status = 'unpaid'
ASP.NET
ASP.NET
Function RequestsSent()
Dim objCmd2 As sqlCommand
Dim objRdr2 As sqlDataReader
objCmd2 = New SqlCommand("spDashboardPaymentRequests", objConn)
objCmd2.CommandType = CommandType.StoredProcedure
objCmd2.Parameters.AddWithValue("@orgid", Session("orgid"))
objConn.Open()
objRdr2 = objCmd2.ExecuteReader
objRdr2.Read()
Session("RequestsSent") = objRdr2("requestsSent")
Session("RequestsTotal") = objRdr2("requestTotal")
Session("RequestsTotalPaid") = objRdr2("requestTotalPaid")
Session("RequestsTotalUnpaid") = objRdr2("requestTotalUnpaid")
objConn.Close()
End Function
2 个解决方案
#1
5
You have multiple select
s which means multiple result-sets. So you have to move through them:
您有多个选择,这意味着多个结果集。所以你必须通过它们:
objRdr2.Read()
Session("RequestsSent") = objRdr2("requestsSent")
objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotal") = objRdr2("requestTotal")
objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotalPaid") = objRdr2("requestTotalPaid")
objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotalUnpaid") = objRdr2("requestTotalUnpaid")
Or, you can change the sproc to return one resultset with multiple columns:
或者,您可以更改sproc以返回一个具有多个列的resultset:
Alter PROCEDURE [dbo].[spDashboardPaymentRequests]
@id integer
AS
SELECT
(SELECT COUNT(Receiptno) FROM [demofeepay3].[dbo].[vwallrequests] Where Orgid = @id)
as requestsSent,
(SELECT Sum(totamount) FROM [demofee].[dbo].[vwallrequests] Where Orgid = @id)
as requestTotal,
(SELECT Sum(totamount) FROM [demofee].[dbo].[vwallrequests] Where Orgid = @id AND status = 'paid')
as requestTotalPaid,
(SELECT Sum(totamount) FROM [demo].[dbo].[vwallrequests] Where Orgid = @id AND status = 'unpaid')
as requestTotalUnpaid
#2
0
I don't know about asp , but you probably do, so here is a query to get all of them as the same row , adjust your asp code accordingly:
我不知道asp,但是你可能知道,所以这里有一个查询,把所有的这些都作为一行,相应地调整你的asp代码:
SELECT COUNT(Receiptno) as requestsSent,
(SELECT Sum(totamount)
FROM [demofee].[dbo].[vwallrequests]
Where Orgid = @id) as requestTotal,
--Query 3,
--Query 4
FROM [demofeepay3].[dbo].[vwallrequests]
Where Orgid = @id
#1
5
You have multiple select
s which means multiple result-sets. So you have to move through them:
您有多个选择,这意味着多个结果集。所以你必须通过它们:
objRdr2.Read()
Session("RequestsSent") = objRdr2("requestsSent")
objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotal") = objRdr2("requestTotal")
objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotalPaid") = objRdr2("requestTotalPaid")
objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotalUnpaid") = objRdr2("requestTotalUnpaid")
Or, you can change the sproc to return one resultset with multiple columns:
或者,您可以更改sproc以返回一个具有多个列的resultset:
Alter PROCEDURE [dbo].[spDashboardPaymentRequests]
@id integer
AS
SELECT
(SELECT COUNT(Receiptno) FROM [demofeepay3].[dbo].[vwallrequests] Where Orgid = @id)
as requestsSent,
(SELECT Sum(totamount) FROM [demofee].[dbo].[vwallrequests] Where Orgid = @id)
as requestTotal,
(SELECT Sum(totamount) FROM [demofee].[dbo].[vwallrequests] Where Orgid = @id AND status = 'paid')
as requestTotalPaid,
(SELECT Sum(totamount) FROM [demo].[dbo].[vwallrequests] Where Orgid = @id AND status = 'unpaid')
as requestTotalUnpaid
#2
0
I don't know about asp , but you probably do, so here is a query to get all of them as the same row , adjust your asp code accordingly:
我不知道asp,但是你可能知道,所以这里有一个查询,把所有的这些都作为一行,相应地调整你的asp代码:
SELECT COUNT(Receiptno) as requestsSent,
(SELECT Sum(totamount)
FROM [demofee].[dbo].[vwallrequests]
Where Orgid = @id) as requestTotal,
--Query 3,
--Query 4
FROM [demofeepay3].[dbo].[vwallrequests]
Where Orgid = @id