Data not saved correctely using asp.net/c#

Please help

I have the following tables
CollectionPoint, CollectionType and Survey table.
Collection Point Has about 4 fields
CollectionPointID
Date
CollectionID

and I have CollectionType Table that has:
CollectionID     CollectionType
      1              Beginning
        2               End
        3               Follow
which is being populated by a dropdownlist on the aspx page. This is conected with table CollectionPoint by collectionID.

Survey Table has about 70 culumns includeing SurveyID

I am attacing a txt document with some code I have to save collection-Point along with a survey

The problem I am having is:
I have each participant submit  a text bow with the date and a dropdownlist with collectionType(beg,end or follow) everytime they submit this little form, the collectiontypeID is to be created which works fine. Along with each collectionPoint I need a sruvey to go with. and I have in the code the survey Insertion. Sometimes each participant while entering the collectionPointData, jumps on the surveyData and What happens is that sometimes only the collectionPointGet inserted and the SurveyData gets lost. Sometimes it works just fine.

Please help how Can I avoid this problem. I doubt it hapens only on IE.
Not sure if it is a browser problem or somehting in my code.
protected void Save_CollectionPoint_SurveyInfo_Click()
    {
        if ((Session["sParticipantID"]) != null)
        {
            // create the check command
            SqlCommand cmdCollPoint2 = new SqlCommand("SELECT COUNT(*) FROM tbl_CollectionPoint  where ParticipantID = @ParticipantID and collectionID = @collectionID", myConnection);
            cmdCollPoint2.Parameters.Add(new SqlParameter("@ParticipantID", Session["sParticipantID"].ToString()));
            cmdCollPoint2.Parameters.Add(new SqlParameter("@collectionID", Session["sCollectionType"].ToString()));


            //checkpoint to get the visit ID
            SqlCommand cmdCollPoint = new SqlCommand("SELECT CollectionPointID FROM tbl_CollectionPoint where ParticipantID = @ParticipantID order by  CollectionPointID DEsc", myConnection);
            cmdCollPoint.Parameters.Add(new SqlParameter("@ParticipantID", Session["sParticipantID"].ToString()));


            //Create Insert Collection Point 
            SqlCommand cmd3 = new SqlCommand("sp_CollectionPointInsert", myConnection);
            cmd3.CommandType = CommandType.StoredProcedure;
             cmd3.Parameters.Add(new SqlParameter("@ParticipantID", Session["sParticipantID"].ToString()));
            //string strCollectionDate = lblCollectionDate.Text.Trim();
             string strCollectionDate = txtCollectionDate.Text.Trim();
             cmd3.Parameters.Add(new SqlParameter("@CollectionDate", strCollectionDate));
             cmd3.Parameters.Add(new SqlParameter("@CollectionID", Session["sCollectionType"].ToString()));

             myConnection.Open();
             if ((int)cmdCollPoint2.ExecuteScalar() == 1)
             {
                 lableC.Text = "FileNo already exist!";
                 lablePD.Text = "";
             }
             else
             {
                 try
                 {

                     cmd3.ExecuteNonQuery();

                     cmdCollPoint.CommandType = System.Data.CommandType.Text;
                     Session["sCollectionPointID"] = Convert.ToInt32(cmdCollPoint.ExecuteScalar());

                     RadTabStrip1.Tabs[3].ImageUrl = "../images/icons/checkMark0.gif";
                 }
                 catch (Exception exc)
                 {
                     lblMessageCOLL.Text = exc.Message;
                 }

                 finally
                 {
                     myConnection.Close();
                 }
             }
        }

           
   


        // This is all the Survey Insertion
        //-----------------------------------------------------------------------------------------------------------------

        if ((Session["sParticipantID"] != null) && (Session["sCollectionPointID"]) != null)
        {
            // Insert Participant info
            SqlCommand cmd = new SqlCommand("sp_FillSurvey", myConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            //Risk Factors
            cmd.Parameters.Add(new SqlParameter("@ParticipantID", Session["sParticipantID"].ToString()));
            cmd.Parameters.Add(new SqlParameter("@CollectionPointID", Session["sCollectionPointID"].ToString()));


            //string strddlLiveWith = CheckBoxList_GetValues(ddlLiveWith);
            if (!IsEmpty(chkCVD.SelectedValue))
                cmd.Parameters.Add(new SqlParameter("@CVD", chkCVD.SelectedValue));
            else
                cmd.Parameters.Add(new SqlParameter("@CVD", DBNull.Value));
      



            if (RiskFactorObesity.Checked)
                cmd.Parameters.Add(new SqlParameter("@RiskFactorObesity", "1"));
            else cmd.Parameters.Add(new SqlParameter("@RiskFactorObesity", "0"));
	                cmd.Parameters.Add(new SqlParameter("@CupsOfWater", chkCupsOfWater.SelectedValue));
            cmd.Parameters.Add(new SqlParameter("@Breakfast", chkBreakfast.SelectedValue));
            cmd.Parameters.Add(new SqlParameter("@DrinkAlchol", chkDrinkAlchol.SelectedValue));
            cmd.Parameters.Add(new SqlParameter("@DrinksPerweek", txtDrinksPerweek.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@FastfoodEating", chkFastfoodEating.SelectedValue));
            cmd.Parameters.Add(new SqlParameter("@HighSodiumEat", chkHighSodiumEat.SelectedValue));

							||
							||
							||
					                etc other fields'


 try
            {
                myConnection.Open();
                cmd.ExecuteNonQuery();
                

                FinalParticipantID.Text = Session["sParticipantID"].ToString();
                //CollPointID.Text = Session["sCollectionPointID"].ToString();
                CollectionIDFinal.Text = Session["sCollectionType"].ToString();
                RadTabStrip1.Tabs[4].ImageUrl = "../images/icons/checkMark0.gif";
                RadTabStrip1.Tabs[5].ImageUrl = "../images/icons/checkMark0.gif";
                //Response.Redirect("search.aspx");
            }
            catch (Exception exc)
            {
                lableP1.Text = exc.Message;
            }
            finally
            {
                myConnection.Close();

            }
           

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

Open in new window

shpresaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lazyberezovskyCommented:
Use transaction to be sure all commands were executed:

SqlTransaction transaction;
// Create cmdCollPoint2, cmdCollPoint, cmd3
try
{
  myConnection.Open();
  transaction = myConnection.BeginTransaction();
  try
  {
     // Execute commands
     transaction.Commit();
  }
  catch(Exception ex)
  {
      lableP1.Text = ex.Message;
      transaction.Rollback();
  }
}
catch (Exception ex)
{
   lableP1.Text = ex.Message;
}
finally
{
   myConnection.Close();
}

Open in new window

0
shpresaAuthor Commented:
Hi,
Where exactly should I plug this try and catch? I have already a try catch there in the end to make sure the commands goes right.

According to some people who have experienced this , cmd.ExecuteNonQuery(); doesn't get executed sometimes...

Thanks in advance
0
lazyberezovskyCommented:
You have to add inner try-catch block, that will begin after creating transaction (see my code above)
Also you can see:
http://www.knowdotnet.com/articles/transactions.html
http://www.aspnettutorials.com/tutorials/database/sql-transaction-csharp.aspx
0
shpresaAuthor Commented:
Thank you, I  found it out..one of the fields was saved an int on the databaase and they were entering nvarchars in the web..and was causing not saving data correctly...thanks..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

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.