Insert not working in Stored Procedure

Ice

Member
Joined
Jan 25, 2013
Messages
14
Programming Experience
Beginner
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

C#:
/***************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

C#:
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();
                        }
                    }
                }
            }
        }
    }
 
Last edited:

wim sturkenboom

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
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 :D
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();
            }
        }
    }
}
 
Top Bottom