tonydeleon
Posts: 5
|
| Posted: 09/10/2011, 4:56 PM |
|
Hello , could you please help me with this ?
I'd like to display the return value on a grid recordset , maybe using loop or something.
The code is working ok, but only returns the value on the control of the first row.
This is the stored procedure :
USE [tonydb]
GO
/****** Object: StoredProcedure [tonyuser].[p5] Script Date: 09/10/2011 17:37:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [tonyuser].[p5]
@Checkin datetime ,
@Checkout datetime,
@IDHotel int,
@Sum numeric OUTPUT
AS
WITH
t1 AS (SELECT 0 n UNION ALL SELECT 0 n UNION ALL SELECT 0 UNION ALL SELECT 0)
,Calendario AS (SELECT DATEADD(day, (ROW_NUMBER() OVER (ORDER BY a.n)-1), @CheckIn) AS Fetcha
FROM t1 a, t1 b, t1 c, t1 d, t1 e, t1 f)
SELECT @Sum = SUM(dbl)
FROM hdinter
JOIN Calendario ON
Calendario.Fetcha BETWEEN Desde AND Hasta
WHERE
Calendario.Fetcha BETWEEN @CheckIn AND @CheckOut
AND hoteles = @IdHotel
RETURN @Sum
---This is the code on the grid ASP on Befor Show Row event :
Dim cn, cmd
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB.1;Password=????;Persist Security Info=True;User ID=????;Initial Catalog=tonydb;Data Source=_??.??.??.??"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "P5"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("Checkin", addate, _
adParamInput)
cmd.Parameters.Append cmd.CreateParameter("Checkout", addate, _
adParamInput)
cmd.Parameters.Append cmd.CreateParameter("IDHotel", adInteger, _
adParamInput)
cmd.Parameters.Append cmd.CreateParameter("Sum", adInteger, _
adParamReturnValue)
' Set value of Param1 of the default collection to 22
cmd("Checkin") = hdinter.Checkin.value
cmd("Checkout") = hdinter.Checkout.value
cmd("IDHotel") = hdinter.ID.value
cmd.Execute
hdinter.sp.Value = cmd.Parameters("Sum")
|
 |
 |
brause
Posts: 10
|
| Posted: 10/12/2011, 11:40 PM |
|
//----------------------------------------------------------------------------------------------------
// liefert die MST_ID zurück
public int Execute_qse_pr_ckeckStatus()
{
string sProcedure = "qsd_pr_ckeckStatus";
int nReturn = 1;
SqlConnection conn = new SqlConnection(getQSD_Connection());
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sProcedure, conn);
try
{
SqlParameter myParms = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
myParms.Direction = ParameterDirection.ReturnValue;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.ExecuteNonQuery();
nReturn = (int)cmd.Parameters["@RETURN_VALUE"].Value;
}
catch (Exception ex)
{
nReturn = -1;
this.ErrorText = "Fehler beim ausführen der SP qsd_pr_ckeckStatus";
this.ErrorCode = ex.ToString();
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
return nReturn;
}
|
|
|
|
andrewi
Posts: 162
|
| Posted: 10/16/2011, 4:10 AM |
|
Can you try a different approach? It looks like you're getting some rows for a grid from the database, then you want to loop through these rows, and call a stored procedure each time.
If you rewrite your stored procedure so that it returns the rows for the grid INCLUDING the extra caluclated column (number of bookings?) then codecharge can use this to fill the grid for you, with no coding required. And it will need fewer round trips to the database.
|
|
|
|
|
