• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

Session asp.net and C#

Hi experts,

I have this simple form that is driving me crazy. Basically i have two tables in the database, one that stores OrgID , a username and password where loginID is primary key and another one that stores data entry per each organization where dataEntryID is primary key.
I have a simple form in asp.net and C# that will insert information to this tables, retrieve and update all in the same page.

My insert function works well, retrive function works well but when i try to update that's where it is not saving.
I create a session["dataEntryID"] after i save a data entry and I tested that, works fine everytime i click save, the information goes in and lblmessage.text displays me the session number.  

When i do page load, that number dissapers.
The error i get when i click the  update is "Error converting data type nvarchar to int. "    this is so general, i don't think i have a conversion problem.

When i place this on page load, check if sessions are working on page load
  if (Session["sOrgID"] != null && Session["DataEntryID"] != null)
        {

            Bind_Data();
            Bind_DataID();
            IDja.Text = Session["DataEntryID"].ToString();
        }

the data bind no longer are displaed, meaning that my DataEntryID session is not working . Also my IDja.text is not showing.  
I think i need the dataEntryID

Any suggestion.

thanks in advance.
0
shpresa
Asked:
shpresa
2 Solutions
 
Lokesh B RDeveloperCommented:
Hi,

The error is not in this piece of code.

The Error is in UPDATE method.
can you post that code.
0
 
shpresaAuthor Commented:
Here is the code
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if ((Session["OrgID"] != null))
        {
            // create the check command to see if the record exits already on table DataEntry
            //-----------------------------------------------------------------------------------------------------------------
            SqlCommand cmd1 = new SqlCommand("SELECT COUNT(*) FROM tbl_DataEntry where (OrgID = @OrgID)", myConnection);
            cmd1.Parameters.Add(new SqlParameter("@OrgID", lblOrgID.Text.Trim()));

            // Insert Participant info
            SqlCommand cmd = new SqlCommand("sp_DataEntry_insert", myConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            //Data Entry 1
            //cmd.Parameters.Add(new SqlParameter("@DataEntryID", Session["DataEntryID"].ToString()));
            cmd.Parameters.Add(new SqlParameter("@OrgID", Session["OrgID"].ToString()));

            cmd.Parameters.Add(new SqlParameter("@TaskAss1", TaskAss1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@PosFeed1", PosFeed1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@BriefHuddle1", BriefHuddle1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Step1", Step1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Handoff1", Handoff1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Challenges1", Challenges1.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@TaskAss2", TaskAss2.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@PosFeed2", PosFeed2.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@BriefHuddle2", BriefHuddle2.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Step2", Step2.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Handoff2", Handoff2.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Challenges2", Challenges2.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@TaskAss3", TaskAss3.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@PosFeed3", PosFeed3.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@BriefHuddle3", BriefHuddle3.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Step3", Step3.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Handoff3", Handoff3.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Challenges3", Challenges3.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@TaskAss4", TaskAss4.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@PosFeed4", PosFeed4.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@BriefHuddle4", BriefHuddle4.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Step4", Step4.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Handoff4", Handoff4.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Challenges4", Challenges4.Text.Trim()));

            cmd.Parameters.Add(new SqlParameter("@TaskAss5", TaskAss5.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@PosFeed5", PosFeed5.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@BriefHuddle5", BriefHuddle5.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Step5", Step5.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Handoff5", Handoff5.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Challenges5", Challenges5.Text.Trim()));

            //command to read dataentryID and create session after record has been inserted on dataentry
            SqlCommand cmdDE = new SqlCommand("SELECT DataEntryID FROM tbl_DataEntry where (OrgID = @OrgID)", myConnection);
            cmdDE.Parameters.Add(new SqlParameter("@DataEntryID", IDja.Text.Trim()));
            cmdDE.Parameters.Add(new SqlParameter("@OrgID", lblOrgID.Text.Trim()));

            //update command
            SqlCommand cmd0 = new SqlCommand("sp_DataEntry_Update", myConnection);
            cmd0.CommandType = CommandType.StoredProcedure;
            //Data Entry 1
            cmd0.Parameters.Add(new SqlParameter("@DataEntryID", Session["DataEntryID"].ToString()));
            cmd0.Parameters.Add(new SqlParameter("@OrgID", Session["OrgID"].ToString()));

            cmd0.Parameters.Add(new SqlParameter("@TaskAss1", TaskAss1.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@PosFeed1", PosFeed1.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@BriefHuddle1", BriefHuddle1.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Step1", Step1.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Handoff1", Handoff1.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Challenges1", Challenges1.Text.Trim()));

            cmd0.Parameters.Add(new SqlParameter("@TaskAss2", TaskAss2.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@PosFeed2", PosFeed2.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@BriefHuddle2", BriefHuddle2.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Step2", Step2.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Handoff2", Handoff2.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Challenges2", Challenges2.Text.Trim()));

            cmd0.Parameters.Add(new SqlParameter("@TaskAss3", TaskAss3.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@PosFeed3", PosFeed3.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@BriefHuddle3", BriefHuddle3.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Step3", Step3.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Handoff3", Handoff3.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Challenges3", Challenges3.Text.Trim()));

            cmd0.Parameters.Add(new SqlParameter("@TaskAss4", TaskAss4.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@PosFeed4", PosFeed4.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@BriefHuddle4", BriefHuddle4.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Step4", Step4.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Handoff4", Handoff4.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Challenges4", Challenges4.Text.Trim()));

            cmd0.Parameters.Add(new SqlParameter("@TaskAss5", TaskAss5.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@PosFeed5", PosFeed5.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@BriefHuddle5", BriefHuddle5.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Step5", Step5.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Handoff5", Handoff5.Text.Trim()));
            cmd0.Parameters.Add(new SqlParameter("@Challenges5", Challenges5.Text.Trim()));


            try
            {
                myConnection.Open();
                if ((int)cmd1.ExecuteScalar() == 1) // if there is a record already
                {
                    //update function
                    cmd0.ExecuteScalar();
                    txtTexbox1.Text = "Record has been updated";
           

                }
                else
                {
                    cmd.ExecuteScalar();//insert function if record does not exist
                    txtTexbox1.Text = "Data has been saved";
                    SqlDataReader drDED = null; // read data entryID
                    drDED = cmdDE.ExecuteReader();

                    if (drDED.Read())
                    {
                        IDja.Text = drDED["DataEntryID"].ToString();
                        lblOrgID.Text = drDED["OrgID"].ToString();
                        lblMessage.Text = "session will read";
                    }
                    else
                    {
                        drDED.Close();
                    }
                 

                }
                Session["DataEntryID"] = IDja.Text; // here creates a session
                IDja.Text = Session["DataEntryID"].ToString(); // read the session into a labl



            }


            catch (Exception exc)
            {
                txtTexbox1.Text = exc.Message;
            }
            finally
            {
                myConnection.Close();

            }



        }
        else
        {
            Session.Abandon();
            Response.Redirect("../default.aspx", true);
        }

    }
0
 
shpresaAuthor Commented:
both save and update are in the same button. Basically if the record already exit, update it, otherwise save it. Insertion works fine. Update function, is not display the message "record has been update" but no record is updated on the table.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Daniel Van Der WerkenIndependent ConsultantCommented:
First off, I'd be very careful with this statement:

if ((int)cmd1.ExecuteScalar() == 1) // if there is a record already

Open in new window


Your select is a COUNT(*). What if that returns more than one row counted? Could be, maybe. Right?

Try this:
if ((int)cmd1.ExecuteScalar() >= 1) // if there is a record already

Open in new window


This logic appears oddly circular:
Session["DataEntryID"] = IDja.Text; // here creates a session
 IDja.Text = Session["DataEntryID"].ToString(); // read the session into a labl

Open in new window


You're setting the session to a text value which may in fact be string.Empty, and then setting the same exact text value to the session. ???

Make sure you have your DataEntryID, however you get it -- either from the database or the session. Not clear that's going to happen all the time here.
0
 
shpresaAuthor Commented:
Hi,

Your select is a COUNT(*). What if that returns more than one row counted? Could be, maybe. Right?
- it always return 1 or 0 . It will never be 2. Each orgID will have 1 dataentryID.

This logic appears oddly circular:
- I was trying to create that session after i do an insertion.

I create the session "dataentryID" right after the record is saved and it goes well till there. Once i do page load, the session dissapers. The same when i retrieve the data. If I go back to display that data entry and update, it will go right after the execution and display the message "data is updated" but it is not really updated in the table. On page load, it goes back to original data.
0
 
Daniel Van Der WerkenIndependent ConsultantCommented:
Sessions don't just generally disappear, in my experience.

One, you're over-writing it. Check all the places you assign to this Session value and make sure you're not overwriting it incorrectly. You may want to set break points on all these places and see what's happening.

Two, are you paging out or just reloading the same page? It won't make much difference, but you'll tie up fewer server resources that way.

In my opinion, this line of code is definitely a problem:
Session["DataEntryID"] = IDja.Text; // here creates a session

Open in new window


Make sure you absolutely have the ID you're looking for from the databse and then assign it to the session variable. I think you're overwriting it here with an empty string and that's why you're losing it.
0
 
shpresaAuthor Commented:
Hi Dan7el

so i update the code again,
 try
            {
                myConnection.Open();
                if ((int)cmd1.ExecuteScalar() == 1) // if there is a record already
                {
               
                    Session["DataEntryID"] = Convert.ToInt32(cmd0.ExecuteScalar());
                    txtTexbox1.Text = "Record has been updated";
           

                }
                else
                {
                    Session["DataEntryID"] = Convert.ToInt32(cmd.ExecuteScalar());
                    txtTexbox1.Text = "Data has been saved";

                    }

            }
               

and the only other place i have them are on page load/postback.
        if (!Page.IsPostBack)
        {
            if (Session["OrgID"] != null)
            {
                Bind_Data();
     
                IDja.Text = Session["DataEntryID"].ToString();
               lblOrgID.Text = Session["OrgID"].ToString();
             
            }                  

Still on the same status, it insert fine, and it does not update. Eventhough i see the message that the record is updated. So the check command to check if there is already a record, works fine.
0
 
shpresaAuthor Commented:
and now, after clearing out all sessions, I am back to the same original error

Error converting data type nvarchar to int.
0
 
David Johnson, CD, MVPOwnerCommented:
Generic routine adjust as required.. BTW you shouldn't store numbers as strings [nvchar]
    select 
          case 
              when isnumeric(submitter) = 1 then 
                      cast(submitter AS int)
              else
                      NULL
         end
    AS 'Submitter'
    from YourTable

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now