Extract value from stored procedure that takes a parameter
Hi
I have a stored procedure, which I cannot change (it is used by older programs)
It is passed an int of 15 and then needs to return the created ID which is written to table.
I'm not getting the created ID value from my code, I am getting the value I passed to the procedure. Also the INSERT does not seem to be firing as well.
I'm just starting in C# so I appreciate any help. Below the stored procedure and my code
/***************Stored Procedure***********************/
USE [testDataBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MakeValue]
(
@timeLive smallint
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CookieID varchar(50)
SET @CookieID = NEWID()
INSERT INTO dbo.Sessions (CookieID, TTL,LastVisit) VALUES (@CookieID, @timeLive,CURRENT_TIMESTAMP)
IF @@ERROR = 0 AND @@ROWCOUNT = 1
BEGIN
SELECT @CookieID AS '@@CookieID'
RETURN 0
END
ELSE
BEGIN
RAISERROR('Something went wrong :-(', 16, 1)
RETURN -1
END
END
/************************CODE**************************/
int sessionTimeOut = 15;
string sessionID = "";
if (StdVars.sqlConn1.State == ConnectionState.Closed)
{
StdVars.sqlConn1.Open();
}
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = StdVars.sqlConn1;
IDbDataParameter sessionProcedureID = sqlcmd.CreateParameter();
sqlcmd.CommandText = "MakeValue";
sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
sessionProcedureID.ParameterName = "@timeLive";
sessionProcedureID.Value = sessionTimeOut;
sessionProcedureID.Direction = System.Data.ParameterDirection.Input;
sessionProcedureID.DbType = System.Data.DbType.String;
sqlcmd.Parameters.Add(sessionProcedureID);
sqlcmd.ExecuteNonQuery();
sessionID = sessionProcedureID.Value.ToString();
if (sessionID.Length > 0) {
HttpCookie cookID = new HttpCookie("SessionSettings");
cookID["ID"] = sessionID;
}
if (StdVars.sqlConn1.State == ConnectionState.Open)
{
sqlcmd.Dispose();
StdVars.sqlConn1.Close();
StdVars.sqlConn1.Close();
}