Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi.
i have written a stored procedure in sqlserver:
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:
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:
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:
(
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 2-Sep-12 19:54pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
SELECT SCOPE_IDENTITY();
 
Rest of the things are OK.
 

---Amit
  Permalink  
v2
Comments
mohammad ehsan at 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 at 3-Sep-12 2:23am
   
Store it somewhere(i.e., in session). And get it from there in next page. :)
mohammad ehsan at 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 at 3-Sep-12 2:55am
   
if your is in Integer try to parse it ??
_Amy at 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.
mohammad ehsan at 3-Sep-12 3:18am
   
i am sorry for asking this question (because i am a beginner). i didn't get ; how can i put it in session? thanks of your attention
_Amy at 3-Sep-12 3:36am
   
Try this: Session["PersionID"] = int.Parse(cmd.ExecuteScalar().ToString());
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
Exception occur because you have used below code to cast your Scalar value into ToString.
 
globalvaribles.PersonId = int.Parse(cmd.ExecuteScalar().ToString());
instead use
globalvaribles.PersonId = int.Parse(Convert.ToString(cmd.ExecuteScalar()));
 
This will not throw exception.
 
Thanks
-Amit Gajjar
  Permalink  
Comments
mohammad ehsan at 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 at 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.

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 437
1 sanket saxena 355
2 thatraja 240
3 Abhinav S 216
4 CPallini 215
0 Sergey Alexandrovich Kryukov 8,784
1 OriginalGriff 5,025
2 Peter Leow 3,839
3 Maciej Los 3,535
4 Er. Puneet Goel 3,107


Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 3 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid