Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
2.67/5 (3 votes)
See more:
i want to add session values in the stored procedure my code as:
C#
int userId = 0;
           {
               using (SqlCommand cmd = new SqlCommand("rsa_products_sp_ValidateUser"))
               {
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.AddWithValue("@email", txtUserName.Text);
                   cmd.Parameters.AddWithValue("@Password", txtPwd.Text);
                   cmd.Parameters.AddWithValue("@UserId ", Session["UserId"].ToString());
                   cmd.Connection = con;
                   con.Open();
                   Session["User"] = txtUserName.Text;
                   userId = Convert.ToInt32(cmd.ExecuteScalar());
                   con.Close();
               }

i'm getting expection as
System.NullReferenceException: Object reference not set to an instance of an object.


My stored Procedure as follows
CREATE PROCEDURE [dbo].[rsa_products_sp_ValidateUser]
      @email NVARCHAR(50),
      @Password NVARCHAR(20)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @UserId INT, @LastLoginDate DATETIME
     
      SELECT @UserId = UserId, @LastLoginDate = LastLoginDate
      FROM rsa_Users WHERE email = @email AND [Password] = @Password
     
      IF @UserId IS NOT NULL
      BEGIN
            IF NOT EXISTS(SELECT UserId FROM rsa_UserActivation WHERE UserId = @UserId)
            BEGIN
                  UPDATE rsa_Users
                  SET LastLoginDate = GETDATE()
                  WHERE UserId = @UserId
                  SELECT @UserId [UserId] -- User Valid
            END
            ELSE
            BEGIN
                  SELECT -2 -- User not activated.
            END
      END
      ELSE
      BEGIN
            SELECT -1 -- User invalid.
      END

i Have declared UserId in sp in this line
SQL
SET NOCOUNT ON;
      DECLARE @UserId INT, @LastLoginDate DATETIME

i have kept the break point and checked and exception throws in this line
cmd.Parameters.AddWithValue("@UserId ", Session["UserId"].ToString());

please Help me to solve this
Posted
Comments
Code For You 1-Apr-15 1:15am    
comment this line and run it again. It should work.

It is obvious that Session["UserId"] is null!
Go and check your code to see why!
 
Share this answer
 
Comments
kwelpooh 30-Mar-15 3:15am    
checked in immediate window and it shows null please let me know if this wrong way to add session like this
Kornfeld Eliyahu Peter 30-Mar-15 3:23am    
Of course it is wrong! You try to access member method on a non-existing object - it is always wrong...
To set such a session variable you should assign to it a value somewhere in your code (probably after successful login), like Session["UserId"] = [userid];
Sergey Alexandrovich Kryukov 1-Apr-15 12:27pm    
5ed.
—SA
Kornfeld Eliyahu Peter 1-Apr-15 12:57pm    
Thank you...
Herman<T>.Instance 1-Apr-15 13:50pm    
uhmmmm...did you check that properly?
In the Stored Procedure there is no declaration for @UserID:
CREATE PROCEDURE [dbo].[rsa_products_sp_ValidateUser]
@email NVARCHAR(50),
@Password NVARCHAR(20)

and
Session["UserId"] = txtEmail.Text.

Looks like an April's fool question
First problem is you need to check if Session exists or not and then read the value.

Second thing is you don't have a UserId parameter declared for the Stored Procedure. I can see that there are parameters declared for email and password, whereas there is not parameters for UserId. Procedure should take UserId as a input parameter.
 
Share this answer
 
Comments
kwelpooh 30-Mar-15 3:22am    
i want to add something like this
Session.Add("UserId", ds.Tables["rsa_Users"].Rows[0][0].ToString()); since not using inline query i dont know how to add session when using stored procedure. please guide me.
First see where exactly you are adding the session. You have written one line...

Session["User"] = txtUserName.Text;

after the query... I think it should come before all codes, so that session will contain the value. Then only you can use that values and send it as parameter to the stored procedure.
kwelpooh 30-Mar-15 6:01am    
UserId is identity column in table how to assign session for that
I am not sure of your project flow. I don't even know when you are getting the UserId in your application. I can just suggest you that, when you are retrieving the User details, there only try to set the UserId Session.
Code For You 31-Mar-15 7:46am    
Agree with Tadit Dash. Need to declare @UserId either in .Net code or remove the @UserId parameter declaration line.
You did show where the exception with the message "Object reference not set to an instance of an object" is thrown, but you did not provide all the detail. Fortunately, the authors of other answers were able to point it out. But you cannot ask this question every time it happens, need, to learn how to sort it out by yourself.

Not to worry. This is one of the very easiest cases to detect and fix. It simply means that some member/variable of some reference type is dereferenced by using and of its instance (non-static) members, which requires this member/variable to be non-null, but in fact it appears to be null. Simply execute it under debugger, it will stop the execution where the exception is thrown. Put a break point on that line, restart the application and come to this point again. Evaluate all references involved in next line and see which one is null while it needs to be not null. After you figure this out, fix the code: either make sure the member/variable is properly initialized to a non-null reference, or check it for null and, in case of null, do something else.

Please see also: want to display next record on button click. but got an error in if condition of next record function "object reference not set to an instance of an object"[^].

Sometimes, you cannot do it under debugger, by one or another reason. One really nasty case is when the problem is only manifested if software is built when debug information is not available. In this case, you have to use the harder way. First, you need to make sure that you never block propagation of exceptions by handling them silently (this is a crime of developers against themselves, yet very usual). The you need to catch absolutely all exceptions on the very top stack frame of each thread. You can do it if you handle the exceptions of the type System.Exception. In the handler, you need to log all the exception information, especially the System.Exception.StackTrace:
http://msdn.microsoft.com/en-us/library/system.exception.aspx[^],
http://msdn.microsoft.com/en-us/library/system.exception.stacktrace.aspx[^].

The stack trace is just a string showing the full path of exception propagation from the throw statement to the handler. By reading it, you can always find ends. For logging, it's the best (in most cases) to use the class System.Diagnostics.EventLog:
http://msdn.microsoft.com/en-us/library/system.diagnostics.eventlog.aspx[^].



Congratulations with 1st of April! :-)

I would like to use the occasion to invite everyone to see my new 1 of April publication and have some fun:

Some Programming Approaches to "Neuro-Linguistic Programming".
Participation in this game in Comments and Discussions is especially encouraged.

Thank you.
—SA
 
Share this answer
 
v3
Comments
As usual, the best. My 5. :)
Sergey Alexandrovich Kryukov 2-Apr-15 10:03am    
Thank you, Tadit.
—SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900