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

Inserting records into MSSQL Error message problem

I am entering records into an MSSQL Database with an ASP.NET form, Everything is going fine, the records are being entered fine, but I have this little nagging problem. I have a Label that is supposed to say when the records have been inserted correctly "RECORDS INSERTED" but it keeps showing the error catch "Failed Insert Records.  There was a database error" I'm not sure why it is doing this. The code is below. Where the Messages are is at the bottom.
public partial class _Default : System.Web.UI.Page
{
 
    protected void InsertButton_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=CNXSQLPRD;Initial Catalog=Prop_Change;Integrated Security=SSPI");
        SqlCommand com = new SqlCommand("Insert into PropChanges_DISPMain(Company,Sub_Date,State,County,AddCountyYN,MapIMGDIR,MapIMG_DESC,AcqDispYN,AddDocumentsYN,ApprYN,ClosingSettYN,DiscStmntYN,DocToBeFwdYN,OrigDocYN,SpSheetDIR,Prep_By,AddInformation) values(@Company,@Sub_Date,@State,@County,@AddCountyYN,@MapIMGDIR,@MapIMG_DESC,@AcqDispYN,@AddDocumentsYN,@ApprYN,@ClosingSettYN,@DiscStmntYN,@DocToBeFwdYN,@OrigDocYN,@SpSheetDIR,@Prep_By,@AddInformation)", con);
        string strPath = string.Empty;
        int intResult = 0;
        con.Open();
 
        TextBox CompanyTB = (TextBox)FormView1.FindControl("Co_NameTextBox");
        if (CompanyTB != null)
        {
            string txtBox = CompanyTB.Text.Trim();
            com.Parameters.Add("@Company", SqlDbType.NVarChar, 50, "Company").Value = Server.HtmlEncode(txtBox);
        }
 
        TextBox SubDateTB = (TextBox)FormView1.FindControl("Sub_DateTextBox");
        if (SubDateTB != null)
        {
            string txtBox3 = SubDateTB.Text.Trim();
            com.Parameters.Add("@Sub_Date", SqlDbType.DateTime, 50, "Sub_Date").Value = Server.HtmlEncode(txtBox3);
        }
 
        DropDownList ddlS = (DropDownList)FormView1.FindControl("ddlState");
        string ddlStateValue = ddlS.SelectedValue;
        com.Parameters.Add("@State", SqlDbType.NVarChar, 50, "State").Value = ddlStateValue;
 
        DropDownList ddlC = (DropDownList)FormView1.FindControl("ddlCounty");
        string ddlCountyValue = ddlC.SelectedValue;
        com.Parameters.Add("@County", SqlDbType.NVarChar, 50, "County").Value = ddlCountyValue;
 
        TextBox AdInformationTB = (TextBox)FormView1.FindControl("AdInformationTextBox");
        if (AdInformationTB != null)
        {
            string txtBoxAddInf = AdInformationTB.Text.Trim();
            com.Parameters.Add("@AddInformation", SqlDbType.NVarChar, 4000, "AddInformation").Value = Server.HtmlEncode(txtBoxAddInf);
        }
 
        TextBox SpreadDIRTB = (TextBox)FormView1.FindControl("SPFilePath");
        if (SpreadDIRTB != null)
        {
            string SpDirTB = SpreadDIRTB.Text.Trim();
            com.Parameters.Add("@SpSheetDIR", SqlDbType.NVarChar, 1000, "SpSheetDIR").Value = Server.HtmlEncode(SpDirTB);
        }
 
        TextBox MapPath = (TextBox)FormView1.FindControl("MapLocation");
        if (MapPath != null)
        {
            string MapPathtxt = MapPath.Text.Trim();
            com.Parameters.Add("@MapIMGDIR", SqlDbType.NVarChar, 1000, "MapIMGDIR").Value = Server.HtmlEncode(MapPathtxt);
        }
 
        CheckBox AddCounties1 = (CheckBox)FormView1.FindControl("AddCounties");
        int flagAdCo;
        if (AddCounties1.Checked)
        {
            flagAdCo = 1;
        }
        else
        {
            flagAdCo = 0;
        }
        com.Parameters.Add("@AddCountyYN", SqlDbType.Bit, 1, "AddCountyYN").Value = flagAdCo;
 
 
        CheckBox OrigDocumentCB1 = (CheckBox)FormView1.FindControl("OrigDocumentCB");
        int flag12;
        if (OrigDocumentCB1.Checked)
        {
            flag12 = 1;
        }
        else
        {
            flag12 = 0;
        }
        com.Parameters.Add("@OrigDocYN", SqlDbType.Bit, 1, "OrigDocYN").Value = flag12;
 
        CheckBox AcqDisCB1 = (CheckBox)FormView1.FindControl("AcqDisCB");
        int flag13;
        if (AcqDisCB1.Checked)
        {
            flag13 = 1;
        }
        else
        {
            flag13 = 0;
        }
        com.Parameters.Add("@AcqDispYN", SqlDbType.Bit, 1, "AcqDispYN").Value = flag13;
 
        CheckBox AddDocCB1 = (CheckBox)FormView1.FindControl("AddDocCB");
        int flag15;
        if (AddDocCB1.Checked)
        {
            flag15 = 1;
        }
        else
        {
            flag15 = 0;
        }
        com.Parameters.Add("@AddDocumentsYN", SqlDbType.Bit, 1, "AddDocumentsYN").Value = flag15;
 
        CheckBox AppCB1 = (CheckBox)FormView1.FindControl("AppCB");
        int flag17;
        if (AppCB1.Checked)
        {
            flag17 = 1;
        }
        else
        {
            flag17 = 0;
        }
        com.Parameters.Add("@ApprYN", SqlDbType.Bit, 1, "ApprYN").Value = flag17;
 
        CheckBox ClosingCB1 = (CheckBox)FormView1.FindControl("ClosingCB");
        int flag19;
        if (ClosingCB1.Checked)
        {
            flag19 = 1;
        }
        else
        {
            flag19 = 0;
        }
        com.Parameters.Add("@ClosingSettYN", SqlDbType.Bit, 1, "ClosingSettYN").Value = flag19;
 
        CheckBox DisclosureCB1 = (CheckBox)FormView1.FindControl("DisclosureCB");
        int flag21;
        if (DisclosureCB1.Checked)
        {
            flag21 = 1;
        }
        else
        {
            flag21 = 0;
        }
        com.Parameters.Add("@DiscStmntYN", SqlDbType.Bit, 1, "DiscStmntYN").Value = flag21;
 
 
        CheckBox DocFowLaterCB1 = (CheckBox)FormView1.FindControl("DocFowLaterCB");
        int flag23;
        if (DocFowLaterCB1.Checked)
        {
            flag23 = 1;
        }
        else
        {
            flag23 = 0;
        }
        com.Parameters.Add("@DocToBeFwdYN", SqlDbType.Bit, 1, "DocToBeFwdYN").Value = flag23;
 
        TextBox ENMapIMGDESCTextBox = (TextBox)FormView1.FindControl("MapIMG_DESCTextBox");
        if (ENMapIMGDESCTextBox != null)
        {
            string txtBoxMap = ENMapIMGDESCTextBox.Text.Trim();
            com.Parameters.Add("@MapIMG_DESC", SqlDbType.NVarChar, 50, "MapIMG_DESC").Value = Server.HtmlEncode(txtBoxMap);
        }
 
        TextBox PrepByTextBox = (TextBox)FormView1.FindControl("Prep_ByTextBox");
        if (PrepByTextBox != null)
        {
            string txtBoxPB = PrepByTextBox.Text.Trim();
            com.Parameters.Add("@Prep_By", SqlDbType.NVarChar, 50, "Prep_By").Value = Server.HtmlEncode(txtBoxPB);
        }
        try
        {
            intResult = Convert.ToInt32(com.ExecuteNonQuery());
               if (intResult != 0)
               {
                Label MsgLabel = (Label)form1.FindControl("MessageLabel1");
                MsgLabel.Text = "Records Entered.";
               }
                   else
               {
                   Label MsgLabel = (Label)form1.FindControl("MessageLabel");
                   MsgLabel.Text = "No Recoreds Entered";
               }
            }
         catch
        {
            Label MsgLabel = (Label)form1.FindControl("MessageLabel");
            MsgLabel.Text = "Failed to Insert Records.  There was a database error.";
        }
    }

Open in new window

0
GravitaZ24
Asked:
GravitaZ24
  • 4
3 Solutions
 
abelCommented:
Change your try/catch to catch only specific errors. Like SqlException. That way, it will only trigger when a "real" exception is thrown.
0
 
abelCommented:
For instance, suppose the following returns null:

Label MsgLabel = (Label)form1.FindControl("MessageLabel");

Then MsgLabel.Text would raise a NullPointerException. You want that to be caught by either a global exc. handler, or you want to find it out during development. Suppose there's an error somewhere, you will always see that error condition, without knowing why.
0
 
DotNetThinkerCommented:
I'm guessing this...

else
               {
                   Label MsgLabel = (Label)form1.FindControl("MessageLabel"); <------
                   MsgLabel.Text = "No Recoreds Entered";
               }

should be this....

else
               {
                   Label MsgLabel = (Label)form1.FindControl("MessageLabel1"); <----
                   MsgLabel.Text = "No Recoreds Entered";
               }

MessageLabel1 instead of MessageLabel
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
abelCommented:
Good eyes, DotNetThinker, I copied the line, but I didn't see that mistake ;)
0
 
GravitaZ24Author Commented:
Oh, man, thanks guys. That is the second time this week that same type of thing has happened to me. I think I need to start getting some more sleep! Actually abel your tip kind of helped too! Thanks you!
0
 
abelCommented:
> I think I need to start getting some more sleep!

that's often a good thing :)
However, more important in your situation (actually, for anybody) is to refrain as much as possible from catch-all error catchers, unless in the catcher you log all the trace or exception information. Now you know why that is ;)

Glad I could've been of some help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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