asked on

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

lazyberezovsky

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

shpresa

ASKER

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

lazyberezovsky

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

ASKER CERTIFIED SOLUTION

shpresa

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial