Get Value

[bbawa1]

Hi,

I have following stored Procedure. I am writng following ado.net code
to get return value from stored procedure but it doesn't work.

String constr =
System.Configuration.ConfigurationManager.Connecti onStrings["twcsanConnectionString"].ConnectionString;
SqlConnection objConn = new SqlConnection(constr);
string sqlstmt = "usp_Update_tbtickets";
SqlCommand objCmd = new SqlCommand(sqlstmt, objConn);
objConn.Open();

//objCmd.ExecuteNonQuery();
SqlDataReader sqlDr;
sqlDr = objCmd.ExecuteReader();
while (sqlDr.Read())
{
Session["TCKid"] = Convert.ToInt32(sqlDr.GetValue(0));
}
sqlDr.Close();
objConn.Close();

CREATE PROCEDURE [twcsan].[usp_Update_tbtickets]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @id INT

SET NOCOUNT ON;
SET @id = (select top 1 ticketid from tbtickets where TcktStatusid
= 1)

update tbtickets
Set tcktopened = GetDate(), TcktAudited = GetDate(),TcktStatusid =
2
where ticketid = @id and TcktStatusid = 1

Return @id
END

[sloan]

You want ExecuteScalar I believe.

Or you can declare an output parameter , and get it.

ExecuteReader is for a resultSET. (Select a, b, c from MyTable) , not a
single item.

<bb****@yahoo.comwrote in message
news:11**********************@e9g2000prf.googlegro ups.com...

Hi,

I have following stored Procedure. I am writng following ado.net code
to get return value from stored procedure but it doesn't work.

String constr =

System.Configuration.ConfigurationManager.Connecti onStrings["twcsanConnectio
nString"].ConnectionString;

SqlConnection objConn = new SqlConnection(constr);
string sqlstmt = "usp_Update_tbtickets";
SqlCommand objCmd = new SqlCommand(sqlstmt, objConn);
objConn.Open();

//objCmd.ExecuteNonQuery();
SqlDataReader sqlDr;
sqlDr = objCmd.ExecuteReader();
while (sqlDr.Read())
{
Session["TCKid"] = Convert.ToInt32(sqlDr.GetValue(0));
}
sqlDr.Close();
objConn.Close();

CREATE PROCEDURE [twcsan].[usp_Update_tbtickets]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @id INT

SET NOCOUNT ON;
SET @id = (select top 1 ticketid from tbtickets where TcktStatusid
= 1)

update tbtickets
Set tcktopened = GetDate(), TcktAudited = GetDate(),TcktStatusid =
2
where ticketid = @id and TcktStatusid = 1

Return @id
END

[bruce barker]

you want the proc return value, while you are trying to fetch the first
column of the resultset. as your resultset is empty there is nothing to
read, so you should use ExecuteNonQuery

add a parameter of ParameterDirection ReturnValue to the SqlCommand
parameter list, then you can read its value after calling ExecuteNonQuery.

-- bruce (sqlwork.com)

bb****@yahoo.com wrote:

Hi,

I have following stored Procedure. I am writng following ado.net code
to get return value from stored procedure but it doesn't work.

String constr =
System.Configuration.ConfigurationManager.Connecti onStrings["twcsanConnectionString"].ConnectionString;
SqlConnection objConn = new SqlConnection(constr);
string sqlstmt = "usp_Update_tbtickets";
SqlCommand objCmd = new SqlCommand(sqlstmt, objConn);
objConn.Open();

//objCmd.ExecuteNonQuery();
SqlDataReader sqlDr;
sqlDr = objCmd.ExecuteReader();
while (sqlDr.Read())
{
Session["TCKid"] = Convert.ToInt32(sqlDr.GetValue(0));
}
sqlDr.Close();
objConn.Close();

CREATE PROCEDURE [twcsan].[usp_Update_tbtickets]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @id INT

SET NOCOUNT ON;
SET @id = (select top 1 ticketid from tbtickets where TcktStatusid
= 1)

update tbtickets
Set tcktopened = GetDate(), TcktAudited = GetDate(),TcktStatusid =
2
where ticketid = @id and TcktStatusid = 1

Return @id
END