Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi.
i have written a stored procedure in sqlserver:
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[Insert_Person_SP]
(
@CityId int ,
@Name nvarchar(100),
@Family nvarchar(100),
@Gender nchar(10),
@MilitaryStatus nvarchar(50)
)
as 

begin
insert into Person (CityId,Name,Family,Gender,MilitaryStatus)
values (@CityId,@Name,@Family,@Gender,@MilitaryStatus)

return @@Identity 
end


and also i have made a class in Asp.net for retrieving PersonId as below:
C#
public class globalvaribles
    {
        public static int PersonId
        {
            get
            {
                if (HttpContext.Current.Session["PersonId"] == null)
                    HttpContext.Current.Session["PersonId"] = -1;
                return int.Parse(HttpContext.Current.Session["PersonId"].ToString());
            }

            set
            {
                HttpContext.Current.Session["PersonId"] = value;
            }
        }

    }

and finally in my main code i intend to insert data into DB:
C#
protected void SubmitButton(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection();
            SqlTransaction trans = null;
            
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["PersonTest"].ConnectionString;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();

            trans = cmd.Connection.BeginTransaction();
            try
            {
                int pcode = 0;
                int.TryParse(DropCity.SelectedValue, out pcode);
                cmd.CommandText = "Insert_Person_SP";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = textbox1.Text;
                cmd.Parameters.Add("@Family", SqlDbType.NVarChar).Value = TextBox2.Text;
                cmd.Parameters.Add("@CityId", SqlDbType.Int).Value = DropCity.SelectedValue;
                cmd.Parameters.Add("@Gender", SqlDbType.NChar).Value = RadioGender.SelectedValue;
                cmd.Parameters.Add("@MilitaryStatus", SqlDbType.NVarChar).Value = DropDownMilitary.SelectedValue;
                trans.Commit();
                globalvaribles.PersonId = int.Parse(cmd.ExecuteScalar().ToString());
                
            }
            catch (SqlException se)
            {
                try
                {
                    trans.Rollback();
                }
                catch{ }
                Page.Title = "SqlException:" + se.Message;
                try
                {
                    trans.Rollback();
                }
                catch (Exception ex)
                {
                    Page.Title = "Exception:" + ex.Message;
                }   
            }
            finally
            {
                try
                {
                    conn.Close();
                }
                catch { }
            }
            Response.Redirect("~/JobInfo.aspx");
        }

in the first page and when i navigate to second page , i want to be able to retrieve PersonId , but thsi message appeares in this line:
(
C#
globalvaribles.PersonId = int.Parse(cmd.ExecuteScalar().ToString());
)

NullRefrence Exception Was Unhandeled by user code.
Object reference not set to an instance of an object

and i don't know whether this way of retreiving PersonId is correct or not?
Posted

Hi,

Exception occur because you have used below code to cast your Scalar value into ToString.

C#
globalvaribles.PersonId = int.Parse(cmd.ExecuteScalar().ToString());

instead use
C#
globalvaribles.PersonId = int.Parse(Convert.ToString(cmd.ExecuteScalar()));


This will not throw exception.

Thanks
-Amit Gajjar
 
Share this answer
 
Comments
mohammad ehsan 3-Sep-12 2:52am    
I had done what Amy said , and there was no problem except ; when i go to second page
a new row is inserted into DB , while i wanted to insert it inside the current row
AmitGajjar 3-Sep-12 3:06am    
ok great, try to figure out another issue yourself. if you not able to resolve then you can raise new QA.
You also need to SELECT it, not RETURN it.

ExecuteScalar will return the first column value from the first row of a result set.

Try this in your procedure:
SQL
SELECT SCOPE_IDENTITY();


Rest of the things are OK.


---Amit
 
Share this answer
 
v2
Comments
mohammad ehsan 3-Sep-12 2:18am    
it worked in the first page properly , but when i navigate to seconde page(JobInfo)
i need the PersonId , but again the same Exception appeares.
_Amy 3-Sep-12 2:23am    
Store it somewhere(i.e., in session). And get it from there in next page. :)
mohammad ehsan 3-Sep-12 2:40am    
I did what you said , and i stored it in a session , but when i go to second page
a new row is inserted into DB , while i wanted to insert it inside the current row.
nika2008 3-Sep-12 2:55am    
if your is in Integer try to parse it ??
_Amy 3-Sep-12 3:07am    
On click of submit button only you are redirecting to the other page. Before redirection put it in session. Its very very clear. It won't insert the new value.

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



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