I Need to insert Exception details into sql server database

Question

My issue is that i have an aspx page which is having try and catch bocks in its code file which will handle exception using exception object in catch,now when the program execution reaches this catch block it calls the public method GetExceptionDetails which will return a long string text containing the values of all the properties of exception but not property's name in it.Now when i insert the properties values into table object's field of database everything is right upto the point when the code reaches at db.submitchanges(),in which an exception statement pops out which reads sqldatetimeoverflow and under sqltypesexception.Please help me figure out the issue in this,Below is the whole code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication5
{
    public partial class EnterMarks : System.Web.UI.Page
    {
        public float average,total;
       public string grade,chk,exmessage;

        DataClasses1DataContext db = new DataClasses1DataContext();
        protected void Page_Load(object sender, EventArgs e)
        {   

            if (Request.QueryString["StudentID"] != null)
            {
                Label1.Text = Request.QueryString["StudentID"];

            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            List<int> stdID = new List<int>();
            tblGrade tb = new tblGrade();
            total = (float)(Convert.ToDouble(TextBox1.Text) + Convert.ToDouble(TextBox2.Text) + Convert.ToDouble(TextBox3.Text));
            average = total / 3;
            if (average > 85 && average < 90)
            {
                grade = "AA";
            }
            else if(average>80 && average<85)
            {
                grade = "A";
            }
            else if (average > 75 && average < 80)
            {
                grade = "BB";
            }
            else if (average > 70 && average < 75)
            {
                grade = "B";
            }
            else if (average > 65 && average < 70)
            {
                grade = "C";
            }
            else if (average > 60 && average < 65)
            {
                grade = "CC";
            }
            else if (average > 55 && average < 60)
            {
                grade = "D";
            }
            else
            {
                grade = "DD";
            }
            //var query = from m in db.tblGrades
            //            where m.StudentID == Convert.ToInt32(Request.QueryString["StudentID"])
            //            select m;
            // foreach (var q in query)
            //{

                tb.StudentID = Convert.ToInt32(Request.QueryString["StudentID"]);
                tb.Grade = grade;
                db.tblGrades.InsertOnSubmit(tb);
                db.SubmitChanges();
            Response.Redirect("WebForm1.aspx");

        }

        protected void Button2_Click(object sender, EventArgs e)
         {
            //var query1 = from n in db.tblContacts where n.StudentID == int.Parse(TextBox4.Text) select n;
             tblExcDet te = new tblExcDet();
            var query1 = from n in db.tblContacts select n.StudentID;
            //try
            //{


                foreach (var q in query1)
                {
                    if (q.Equals((int.Parse(TextBox4.Text))))
                    {
                        Label2.Text = "ID Found";
                    }
                }
                try
                {


                    int? i = null;
                    tblContact tc = new tblContact();
                    tc.StudentID = (int)i ;
                    //db.tblContacts.InsertOnSubmit(tc);
                    db.SubmitChanges();
                }
                catch (Exception ex)
                {

                    exmessage = GetExceptionDetails(ex);
                    te.ExMessage = exmessage.Split('*')[0];
                    te.ExData = exmessage.Split('*')[1];
                    te.ExInner = exmessage.Split('*')[2];
                    te.ExTargetSite = exmessage.Split('*')[3];
                    te.ExStackTrace = exmessage.Split('*')[4];
                    te.ExHelplink = exmessage.Split('*')[5];
                    te.ExSource = exmessage.Split('*')[6];
                    te.ExHresult = exmessage.Split('*')[7];
                    db.tblExcDets.InsertOnSubmit(te);
                    db.SubmitChanges();
                    Label2.Text = "Can't assign null value into a table id";
                }






        }
        //public static string GetExceptionDetails(Exception ex) 
        //{
        //    var properties = ex.GetType().GetProperties();
        //    var fields  = properties.Select(property=>new{
        //        name = property.Name,value = property.GetValue(ex,null)
        //    }).Select(x => String.Format(
        //                 "{0} = {1}",
        //                 x.name,
        //                 x.value != null ? x.value.ToString() : String.Empty
        //             ));
        //    return String.Join("*", fields);
        //}
        public static string GetExceptionDetails(Exception ex)
        {
            var properties = ex.GetType().GetProperties();
            var fields = properties.Select(property => new
            {
                name = property.Name,
                value = property.GetValue(ex, null)
            }).Select(x => String.Format(
                         "{0}",
                         x.value != null ? x.value.ToString() : String.Empty
                     ));
            return String.Join("*", fields);
        }

    }
}

Also here i'm using LINQ structure to insert data into sql server database.

You don't generally use reflection to get details from an except. Just store the name message, and stacktrace, and do the same for any inner exceptions. Perhaps you should look for a logging framework that handles these details for you so you're not reinventing the wheel. — mason, Oct 20 '16 at 12:21
okay. check for the property of datetime type for tblExcDet class. It must be assigning wrong value. — Vicky_Burnwal, Oct 20 '16 at 12:39
[THis is the solution to my problem in which it explains not to use nvarchar(50) which assumes only one character length that's why the exceptions is thrown that says string or binary data truncated in sqlexception](http://stackoverflow.com/questions/15019397/string-or-binary-data-would-be-truncated-the-statement-has-been-terminated) — Kamal Singh, Oct 21 '16 at 05:56

Liquid Core · Accepted Answer · 2016-10-21T09:32:00.647

0

Do you have the field set as autogenerated in the designer? If that's not the problem, I'd suggest setting up logging of the data context actions to the console and checking the actual SQL generated to make sure that it's inserting that column, then trace backward to find the problem.

 context.Log = Console.Out;

FWIW, I often set my "CreatedTime" and "LastUpdatedTime" columns up as autogenerated (and readonly) in the designer and give them a suitable default or use a DB trigger to set the value on insert or update. When you set it up as autogenerated, it won't include it in the insert/update even if modified. If the column doesn't allow nulls, then you need to supply an alternate means of setting the value, thus the default constraint and/or trigger.

You might also want to try an explicit cast to

SqlDbType.DateTime

before doing updatechanges

edited Oct 21 '16 at 09:32

answered Oct 20 '16 at 12:21

Liquid Core

1
5
20
43

Sir the field to store the date time's is an autogenerated field which will be getting data by default method or action in sql database table by getdate() – Kamal Singh Oct 20 '16 at 12:27
1

Basically, the issue is that the Sql DATETIME datatype starts at 1/1/1753, wheras DateTime.MinValue is 1/1/0000 (or something like that). So, if you don't initialize the date properties, you get Sql overflows. Options to fix: a) Initialize the DateTime somewheres. b) Switch sql to use the DATETIME2 datatype which does cover the whole range of .NET DateTime values. [SQL 2008 only] – Liquid Core Oct 20 '16 at 12:28
sir when i changed the type to datetime2 then it doesn't throw the same exception error but it shows that string or binary data would be truncated and it's under sqlexception – Kamal Singh Oct 20 '16 at 12:37
Check what is the lenght of your database field and increase it. Or on the datetime field in c# use .ToShortDateString() method. The error is telling you that you are trying to put something that has more charachter than the maximum limit that the receiving database field can contains – Liquid Core Oct 21 '16 at 09:31
sir the problem is solved now the error was that for some columns in sql server i was using nvarchar(50),which sql database was assuming as only 1 character wide(i don't know why) type due to which the exception was generated! – Kamal Singh Oct 21 '16 at 10:15

I Need to insert Exception details into sql server database

1 Answers1