Extract value from stored procedure that takes a parameter

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 1018 Views - Last Post: 18 December 2014 - 07:42 AM Rate Topic: -----

#1 PeterCo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 17-December 14

Extract value from stored procedure that takes a parameter

Posted 17 December 2014 - 10:37 PM

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 an ID is generated and written to a database table. The created ID is then suppose to be selected and returned.
The INSERT does not seem to be working. I'm not getting the created ID value from my code, I am getting the value I passed to the procedure when I get to this
line of code "sessionID = sessionProcedureID.Value.ToString()";.
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();
          }



Is This A Good Question/Topic? 0
  • +

Replies To: Extract value from stored procedure that takes a parameter

#2 PeterCo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 17-December 14

Re: Extract value from stored procedure that takes a parameter

Posted 17 December 2014 - 11:38 PM

I changed

sqlcmd.ExecuteNonQuery();
sessionID = sessionProcedureID.Value.ToString();


to

sqlcmd.ExecuteScalar();
SqlDataReader reader = sqlcmd.ExecuteReader();

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



Was This Post Helpful? 0
  • +
  • -

#3 PeterCo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 17-December 14

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 03:08 AM

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

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


public static void SessionStoredProcedure()
    {
        string sessionID = "";
        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("MakeValue", StdVariables.sqlConn1))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

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

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

                    SqlDataReader reader = cmd.ExecuteReader();

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

Was This Post Helpful? 0
  • +
  • -

#4 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 03:15 AM

Hi

The ExecuteScalar method will return the first value from the first row and column, therefore there is no need to open a reader. You should just be able to do:

sessionID = (string)cmd.ExecuteScalar();




HTH
Was This Post Helpful? 0
  • +
  • -

#5 PeterCo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 17-December 14

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 03:21 AM

Hi

The issue is not the reading of the return value. The problem is the INSERT in the
stored procedure is not firing. Nothing is getting inserted into my database table.
Was This Post Helpful? 0
  • +
  • -

#6 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 03:31 AM

Hi

You are missing an END statement after your INSERT statement:

AS
BEGIN
SET NOCOUNT ON
DECLARE @CookieID varchar(50)
SET @CookieID = NEWID()
INSERT INTO dbo.Sessions (CookieID, TTL,LastVisit) VALUES (@CookieID, @timeLive,CURRENT_TIMESTAMP)
END 



Also, I am assuming the LastVisit column is defined as a timestamp. If it is then you will get an error stating that you cannot insert an explicit value into a timestamp column.


HTH
Was This Post Helpful? 0
  • +
  • -

#7 PeterCo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 17-December 14

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 03:54 AM

My apologies that END is in the stored procedure I just didn't copy it in.
The Lastvisit field is defined as datetime.
We use this procedure with other programs and it's only this C# code that is failing.
Was This Post Helpful? 0
  • +
  • -

#8 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 04:17 AM

Hi

Just to be clear, the problem is only with the stored procedure and not with the code?

I changed the LastVisit to a datetime and executed the stored procedure within Management Studio and it works fine.

I also copied your code into a new project and noticed some strange behaviour. I get a value for SessionID however the ExecuteScalar inserts one row and the ExecuteReader then inserts another row so for each run there are two rows inserted into the table.

My code is as follows:
            string sessionID;

            using (SqlConnection con = new SqlConnection("Server=DJ-ATIV;Database=Test;Trusted_Connection=True;"))
            {
                using (SqlCommand cmd = new SqlCommand("MakeValue",con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@timeLive ", SqlDbType.Int).Value = 45;
                    con.Open();
                    sessionID = (string)cmd.ExecuteScalar();

                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())

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



While you stated that the code isn't the problem, I do advise that you remove the reader code as it is not required.


HTH
Was This Post Helpful? 0
  • +
  • -

#9 PeterCo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 17-December 14

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 04:36 AM

Yes the stored procedure works hundred percent.
We use it with classic asp and it's fine.

I removed the reader from my code but still nothing in my database?
This is what I've got now. The sessionID gets the value from the procedure again
but nothing in database?

using (SqlConnection con = new SqlConnection(StdVariables.connStr))
{
using (SqlCommand cmd = new SqlCommand("CreateSessionID", StdVariables.sqlConn1))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@timeLive", SqlDbType.Int).Value = 15;
con.Open();
sessionID = (string)cmd.ExecuteScalar();
}
}
Was This Post Helpful? 0
  • +
  • -

#10 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 04:41 AM

What is StdVariables.sqlConn1? You assign this to the Command but elsewhere you are using con. I am assuming that these are both connection objects?

Can you try just using one connection object:
using (SqlCommand cmd = new SqlCommand("CreateSessionID", con))


Was This Post Helpful? 0
  • +
  • -

#11 PeterCo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 17-December 14

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 05:20 AM

Yes that is just a connection object.

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

is for

con.Open();

StdVariables is just a class for a lot of variables we use at the moment
it just has the connection string in it so nothing to conflict it.
Was This Post Helpful? 0
  • +
  • -

#12 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 05:26 AM

Hi

Did you try my previous suggestion? I understand that the StdVariables.connStr contains your connection string but in your previous post you also had:
using (SqlCommand cmd = new SqlCommand("CreateSessionID", StdVariables.sqlConn1))


which in my mind suggests that StdVariables.sqlConn1 is an SqlConnection object. If that is indeed the case then why do you declare your own SqlConnection object (con) but then assign sqlConn1 to the Command object?
Was This Post Helpful? 0
  • +
  • -

#13 PeterCo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 17-December 14

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 05:50 AM

I've hard coded the string and used only one connection still nothing??

using (SqlConnection con = new SqlConnection("Data Source=TestServer;Initial Catalog=tester;User ID=testID;Password=password"))
            {
                using (SqlCommand cmd = new SqlCommand("CreateSessionID", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@timeLive", SqlDbType.Int).Value = 15;
	                con.Open();
	                sessionID = (string)cmd.ExecuteScalar();
                }
            }

This post has been edited by JackOfAllTrades: 18 December 2014 - 05:54 AM
Reason for edit:: Added code tags

Was This Post Helpful? 0
  • +
  • -

#14 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6245
  • View blog
  • Posts: 24,013
  • Joined: 23-August 08

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 05:55 AM

When posting code, USE CODE TAGS!!!

:code:
Was This Post Helpful? 0
  • +
  • -

#15 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Extract value from stored procedure that takes a parameter

Posted 18 December 2014 - 05:59 AM

That's very odd.

I have copied your stored procedure and code and do not have any issues with inserting the data or retrieving the value.

My Sessions table is defined as:
Attached Image

The stored procedure (Called MakeValue as per your original) is as follows:
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)
END

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

and the code:
            string sessionID;

            using (SqlConnection con = new SqlConnection("Data Source=DJ-ATIV;Initial Catalog=Test;User ID=userId;Password=password"))
            {
                using (SqlCommand cmd = new SqlCommand("MakeValue", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@timeLive", SqlDbType.Int).Value = 15;
                    con.Open();
                    sessionID = (string)cmd.ExecuteScalar();
                }
            } 



So at this stage I am not sure what is going wrong.

This post has been edited by djjeavons: 18 December 2014 - 05:59 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2