LINQ to SQL Failing to Insert to the Database

Question

I am currently learning LINQ and now I am trying to insert using Linq to sql but am having challenges when inserting as It is showing me incorrect string format when I debug but it is no pointing on specific line. Can you please help me where I made mistake.

Save button

List<LEGAL_MEMBER> _LegalMemberList = _dc.LEGAL_MEMBERs.Where(a => a.IDNumber == txtIDNumber.Text.ToString()).ToList();

if (rbtnAreYouEmployed.Items[0].Selected == true)
{
    ViewState["AreyouEmployed"] = true;
}
else
{
    ViewState["AreyouEmployed"] = false;
}

if (rbtnIsSACitizen.Items[0].Selected == true)
{
    ViewState["IsSACitizen"] = true;
}
else
{
    ViewState["IsSACitizen"] = false;
}
if (_LegalMemberList != null)
{
    if (_LegalMemberList.Count() == 0)
    {
        LEGAL_MEMBER _legalMember = new LEGAL_MEMBER
        {
            IDNumber = txtIDNumber.Text,
            InceptionDate = Convert.ToDateTime(txtInceptionDate.Text),
            LegalPreferedName = txtPreferedName.Text,
            Initials = txtInitials.Text,
            TitleID = int.Parse(cboTitle.SelectedValue),
            FullNames = txtFullNames.Text,
            Surname = txtSurname.Text,
            Age = int.Parse(txtAge.Text),
            DateOfBirth = Convert.ToDateTime(txtDateOfBirth.Text),
            PassportNumber = txtPassport.Text,
            AreyouEmployed = bool.Parse(ViewState["AreyouEmployed"].ToString()),
            Employer = txtEmployer.Text,
            ContactNumber = txtContactNumber.Text,
            OtherContanctNumber = txtOtherContanctNumber.Text,
            EmailAddress = txtEmailAddress.Text,
            IsSACitizen = bool.Parse(ViewState["IsSACitizen"].ToString()),
            TelephoneWork = txtTelephoneWork.Text,
            TelephoneHome = txtTelephoneHome.Text,
        };

        _dc.LEGAL_MEMBERs.InsertOnSubmit(_legalMember);
        _dc.SubmitChanges();

SQL Table

CREATE TABLE [dbo].[LEGAL_MEMBER](
[LegalMembershipID] [int] IDENTITY(1,1) NOT NULL,
[InceptionDate] [datetime] NULL,
[LegalPreferedName] [nvarchar](50) NULL,
[Initials] [nvarchar](50) NULL,
[TitleID] [int] NULL,
[FullNames] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL,
[Age] [int] NULL,
[DateOfBirth] [datetime] NULL,
[IDNumber] [nvarchar](50) NULL,
[PassportNumber] [nvarchar](50) NULL,
[AreyouEmployed] [bit] NULL,
[Employer] [nvarchar](50) NULL,
[ContactNumber] [nvarchar](50) NULL,
[OtherContanctNumber] [nvarchar](50) NULL,
[EmailAddress] [nvarchar](50) NULL,
[IsSACitizen] [bit] NULL,
[TelephoneWork] [nvarchar](50) NULL,
[TelephoneHome] [nvarchar](50) NULL)

what is the value of `txtDateOfBirth.Tex`? `DateOfBirth = int.Parse(txtDateOfBirth.Text)`? check the inputs to make sure they make sense. — user1666620, Sep 21 '15 at 09:08
You may have issues with parsing textboxes values but it will fail in the end when specifying the value for your LegalMembershipID Column which is IDENTITY (it should be a R/O column) — xum59, Sep 21 '15 at 09:19
From @hevans900: You could begin by writing some debug code inside your catch block, have it write the details of your error to the console. — Panagiotis Kanavos, Sep 21 '15 at 09:26
I think this question is too broad and should be closed until the OP refines it. — huysentruitw, Sep 21 '15 at 09:29

score 0 · Answer 1 · answered Sep 21 '15 at 09:09

It will be one of the Parse methods that fail. You have some options to debug this:

Put a breakpoint on the line LEGAL_MEMBER _legalMember = new LEGAL_MEMBER and then try all individual Parse calls in the Immediate Window in VS.

Pass property values one-by-one and see where it fails.

LEGAL_MEMBER _legalMember = new LEGAL_MEMBER();
_legalMember.LegalMembershipID = int.Parse(txtMemberShipNumber.Text);
_legalMember.InceptionDate = DateTime.Parse(txtInceptionDate.Text);
_legalMember.LegalPreferedName = txtPreferedName.Text;
_legalMember.Initials = txtInitials.Text;
_legalMember.TitleID = int.Parse(cboTitle.SelectedValue);
_legalMember.FullNames = txtFullNames.Text;
_legalMember.Surname = txtSurname.Text;
_legalMember.Age = int.Parse(txtAge.Text);
_legalMember.DateOfBirth = int.Parse(txtDateOfBirth.Text);
_legalMember.IDNumber = txtIDNumber.Text;
_legalMember.PassportNumber = txtPassport.Text;
_legalMember.AreyouEmployed = bool.Parse(ViewState["AreyouEmployed"].ToString());
_legalMember.Employer = txtEmployer.Text;
_legalMember.ContactNumber = txtContactNumber.Text;
_legalMember.OtherContanctNumber = txtOtherContanctNumber.Text;
_legalMember.EmailAddress = txtEmailAddress.Text;
_legalMember.IsSACitizen = bool.Parse(ViewState["IsSACitizen"].ToString());
_legalMember.TelephoneWork = txtTelephoneWork.Text;
_legalMember.TelephoneHome = txtTelephoneHome.Text;

I have edited the code with a hope that i will find out were it is showing an error. I have debug and still not pointing where the error is up until i got this Value was either too large or too small for an Int32. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. " Exception Details: System.OverflowException: Value was either too large or too small for an Int32. — JuniorLinq, Sep 21 '15 at 09:59

score 0 · Answer 2 · edited May 23 '17 at 11:58

Its hard to be sure from this, but the most obvious issue in the code you've given is this line:

DateOfBirth = int.Parse(txtDateOfBirth.Text)

I assume that this is going to attempt to convert a string similar to "09/21/2015" to something like a Julian or OADate date?

If so, you need to convert the string to a datetime, and then onwards to the integer.

To get the datetime, try

var dob = DateTime.Parse(txtDateOfBirth.Text);

To then get an OADate use

DateOfBirth = dob.ToOADate().

If you need a Julian date use a simple helper function like this:

public static double ToJulianDate(this DateTime date)
{
    return date.ToOADate() + 2415018.5;
}

(taken from this answer)

The same would be true of InceptionDate = DateTime.Parse(txtInceptionDate.Text);

If you have the choice though, a better option would be to store these dates as DateTime columns in the database though.

score 0 · Answer 3 · answered Sep 21 '15 at 09:24

0

I don't think you need this line: Try to insert after removing this line:

       LegalMembershipID = int.Parse(txtMemberShipNumber.Text)

as your primary key field is auto increment. Please remove this and then try to insert.

answered Sep 21 '15 at 09:24

Shilpa Soni

2,034
4
27
38

I have removed the fist line and still not working. this is the error now ? "Value was either too large or too small for an Int32. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.OverflowException: Value was either too large or too small for an Int32. " – JuniorLinq Sep 21 '15 at 10:00
In your table script I noticed [DateOfBirth] is of int type, how can DOB is of int type? – Shilpa Soni Sep 21 '15 at 10:04
Please update the datatype in your database as well as in your LINQ table designer and update this line accordingly: _legalMember.DateOfBirth = int.Parse(txtDateOfBirth.Text); – Shilpa Soni Sep 21 '15 at 10:08
I have updated it and still getting the same error. Please help me – JuniorLinq Sep 21 '15 at 10:16
here is my Team Viewer details if you can assist : ID :771 744 106 Pass :2088 – JuniorLinq Sep 21 '15 at 10:22
Make sure datatype in sql table and LINQ table designer are same – Shilpa Soni Sep 21 '15 at 10:34
Yes they are the same. I have changed it as you and updated from the data access layer – JuniorLinq Sep 21 '15 at 10:46
I'm not getting you problem and why you've updated your question. It seems now (by your updated code in question) that you're updating NOT inserting. – Shilpa Soni Sep 21 '15 at 11:26
I have updated the codes as i was trying to catch up the error but still not sure were the error is occurring. I have change the datatype from the table and from the code as well. Can i give you my Team Viewer details so that you can help ID :771 744 106 Pass :2088 – JuniorLinq Sep 21 '15 at 11:39

score 0 · Answer 4 · answered Sep 29 '15 at 10:00

I have realized that my check boxes where the one's causing the problem. After a deep dive i managed to fix it. below is my solution and thanks to everyone who helped me.

            List<LEGAL_MEMBER> _LegalMemberList = _dc.LEGAL_MEMBERs.Where(a => a.IDNumber == txtIDNumber.Text.ToString()).ToList();

            if (rbtnAreYouEmployed.Items[0].Selected == true)
            {
                ViewState["AreyouEmployed"] = true;
            }
            else
            {
                ViewState["AreyouEmployed"] = false;
            }

            if (rbtnIsSACitizen.Items[0].Selected == true)
            {
                ViewState["IsSACitizen"] = true;
            }
            else
            {
                ViewState["IsSACitizen"] = false;
            }
            if (_LegalMemberList != null)
            {
                if (_LegalMemberList.Count() == 0)
                {
                    LEGAL_MEMBER _legalMember = new LEGAL_MEMBER
                    {
                        IDNumber = txtIDNumber.Text,
                        InceptionDate = Convert.ToDateTime(txtInceptionDate.Text),
                        LegalPreferedName = txtPreferedName.Text,
                        Initials = txtInitials.Text,
                        TitleID = int.Parse(cboTitle.SelectedValue),
                        FullNames = txtFullNames.Text,
                        Surname = txtSurname.Text,
                        Age = int.Parse(txtAge.Text),
                        DateOfBirth = Convert.ToDateTime(txtDateOfBirth.Text),
                        PassportNumber = txtPassport.Text,
                        AreyouEmployed = bool.Parse(ViewState["AreyouEmployed"].ToString()),
                        Employer = txtEmployer.Text,
                        ContactNumber = txtContactNumber.Text,
                        OtherContanctNumber = txtOtherContanctNumber.Text,
                        EmailAddress = txtEmailAddress.Text,
                        IsSACitizen = bool.Parse(ViewState["IsSACitizen"].ToString()),
                        TelephoneWork = txtTelephoneWork.Text,
                        TelephoneHome = txtTelephoneHome.Text,
                    };

                    _dc.LEGAL_MEMBERs.InsertOnSubmit(_legalMember);
                    _dc.SubmitChanges();

LINQ to SQL Failing to Insert to the Database

4 Answers4