Insert not working in Stored Procedure

[Ice]

Hi

I have a stored procedure that inserts and returns a value.
I've been able to execute the procedure and I get the return value but the
INSERT is not working. I'm getting no errors. Why is the INSERT not firing in my stored Procedure?
I've checked the Permissions on the tables and the procedures as well and it has rights for INSERTING

/***************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

public static void SessionStoredProcedure()
    {
        if (sessionID.Length < 1)
        {
            int sessionTimeOut = 15;

            if (StdVariables.sqlConn1.State == ConnectionState.Closed)
            {
                StdVariables.sqlConn1.Open();
            }

            using (SqlConnection con = new SqlConnection(StdVariables.connStr))
            {
                using (SqlCommand cmd = new SqlCommand("CreateSessionID", StdVariables.sqlConn1))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@TTL", SqlDbType.Int).Value = sessionTimeOut;

                    con.Open();
                    cmd.ExecuteScalar();

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            sessionID = reader["@@SessionID"].ToString();
                        }
                    }
                }
            }
        }
    }

 

[wim sturkenboom]

You either posted the wrong stored procedure or you're executing the wrong stored procedure from your code behind. Which one

Further, why are you using the below

using (SqlConnection con = new SqlConnection(StdVariables.connStr))

but use a different connection in

using (SqlCommand cmd = new SqlCommand("CreateSessionID", StdVariables.sqlConn1))

The below code works with the stored procedure that you showed; only problem is that it inserts records twice

string strconnection = "Data Source=WIM_LT-PC\\SQL2012;Initial Catalog=testje;User ID=northwind;Password=northwind";
using (SqlConnection con = new SqlConnection(strconnection))
{
    using (SqlCommand cmd = new SqlCommand("usp_MakeValue", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@timeLive", SqlDbType.Int).Value = 15;

        con.Open();
        cmd.ExecuteScalar();

        SqlDataReader reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            if (reader.HasRows)
            {
                string sessionID = reader["@@CookieID"].ToString();
            }
        }
    }
}