locked
Accept textbox value even if it's null RRS feed

  • Question

  • User14881260 posted

    I have a javascript to validate and avoid null values for my form but there are some textboxes that I need to be 'optional'. I tried erasing the validation for province address and province contact in javascript to make it optional, but the problem is whenever I add the values in the database, an error message appears "Input string was not in a correct format". It's in C# This is my present code wherein the provincial address and provincial contact must only be optional:


    protected void submitButton_Click(object sender, EventArgs e)
        {
            string command = ("insert into EnrollDB values (@FamilyName, @FirstName, @MiddleName, @Gender, @CivilStatus, @Citizenship, @BirthDate, @BirthPlace, @CityAddress, @CityContact, @ProvinceAddress, @ProvinceContact, @Attainment, @SchoolGraduated, @SchoolAddress, @PrcLicense, @License, @LicenseNumber, @LicenseIssued)");
            SqlCommand cmd = new SqlCommand(command, con);
            cmd.Parameters.Add("@FamilyName", SqlDbType.NVarChar).Value = familyname.Text;
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = firstname.Text;
            cmd.Parameters.Add("@MiddleName", SqlDbType.NVarChar).Value = middlename.Text;
            cmd.Parameters.Add("@Gender", SqlDbType.NVarChar).Value = gender.SelectedValue;
            cmd.Parameters.Add("@CivilStatus", SqlDbType.NVarChar).Value = civilstatus.SelectedValue;
            cmd.Parameters.Add("@Citizenship", SqlDbType.NVarChar).Value = citizenship.Text;
            cmd.Parameters.Add("@BirthDate", SqlDbType.NVarChar).Value = birthdate.Text;
            cmd.Parameters.Add("@BirthPlace", SqlDbType.NVarChar).Value = birthplace.Text;
            cmd.Parameters.Add("@CityAddress", SqlDbType.NVarChar).Value = cityaddress.Text;
            cmd.Parameters.Add("@CityContact", SqlDbType.BigInt).Value = citycontact.Text;
            cmd.Parameters.Add("@ProvinceAddress", SqlDbType.NVarChar).Value = provinceaddress.Text;
            cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value = provincecontact.Text;
            string getValue = "";
            for (int i = 0; i < attainment.Items.Count; i++)
            {
                if (attainment.Items[i].Selected)
                    getValue += attainment.Items[i].Value + ", ";
            }
            getValue = getValue.TrimEnd(',', ' ');
            cmd.Parameters.Add("@Attainment", SqlDbType.NVarChar).Value = getValue;
            cmd.Parameters.Add("@SchoolGraduated", SqlDbType.NVarChar).Value = schoolgraduated.Text;
            cmd.Parameters.Add("@SchoolAddress", SqlDbType.NVarChar).Value = schoolgraduated.Text;
            cmd.Parameters.Add("@PrcLicense", SqlDbType.NVarChar).Value = prclicenserbl.SelectedValue;
            cmd.Parameters.Add("@License", SqlDbType.NVarChar).Value = license.Text;
            cmd.Parameters.Add("@LicenseNumber", SqlDbType.NVarChar).Value = licensenumber.Text;
            cmd.Parameters.Add("@LicenseIssued", SqlDbType.NVarChar).Value = licenseissued.Text;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

    protected void submitButton_Click(object sender, EventArgs e)
        {
            string command = ("insert into EnrollDB values (@FamilyName, @FirstName, @MiddleName, @Gender, @CivilStatus, @Citizenship, @BirthDate, @BirthPlace, @CityAddress, @CityContact, @ProvinceAddress, @ProvinceContact, @Attainment, @SchoolGraduated, @SchoolAddress, @PrcLicense, @License, @LicenseNumber, @LicenseIssued)");
            SqlCommand cmd = new SqlCommand(command, con);
            cmd.Parameters.Add("@FamilyName", SqlDbType.NVarChar).Value = familyname.Text;
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = firstname.Text;
            cmd.Parameters.Add("@MiddleName", SqlDbType.NVarChar).Value = middlename.Text;
            cmd.Parameters.Add("@Gender", SqlDbType.NVarChar).Value = gender.SelectedValue;
            cmd.Parameters.Add("@CivilStatus", SqlDbType.NVarChar).Value = civilstatus.SelectedValue;
            cmd.Parameters.Add("@Citizenship", SqlDbType.NVarChar).Value = citizenship.Text;
            cmd.Parameters.Add("@BirthDate", SqlDbType.NVarChar).Value = birthdate.Text;
            cmd.Parameters.Add("@BirthPlace", SqlDbType.NVarChar).Value = birthplace.Text;
            cmd.Parameters.Add("@CityAddress", SqlDbType.NVarChar).Value = cityaddress.Text;
            cmd.Parameters.Add("@CityContact", SqlDbType.BigInt).Value = citycontact.Text;
            cmd.Parameters.Add("@ProvinceAddress", SqlDbType.NVarChar).Value = provinceaddress.Text;
            cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value = provincecontact.Text;
            string getValue = "";
            for (int i = 0; i < attainment.Items.Count; i++)
            {
                if (attainment.Items[i].Selected)
                    getValue += attainment.Items[i].Value + ", ";
            }
            getValue = getValue.TrimEnd(',', ' ');
            cmd.Parameters.Add("@Attainment", SqlDbType.NVarChar).Value = getValue;
            cmd.Parameters.Add("@SchoolGraduated", SqlDbType.NVarChar).Value = schoolgraduated.Text;
            cmd.Parameters.Add("@SchoolAddress", SqlDbType.NVarChar).Value = schoolgraduated.Text;
            cmd.Parameters.Add("@PrcLicense", SqlDbType.NVarChar).Value = prclicenserbl.SelectedValue;
            cmd.Parameters.Add("@License", SqlDbType.NVarChar).Value = license.Text;
            cmd.Parameters.Add("@LicenseNumber", SqlDbType.NVarChar).Value = licensenumber.Text;
            cmd.Parameters.Add("@LicenseIssued", SqlDbType.NVarChar).Value = licenseissued.Text;
    
    
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }



    Monday, June 14, 2010 3:03 AM

Answers

  • User197322208 posted

     cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value = provincecontact.Text;  

    int ProvinceContact;

    if(int.TryParse( provincecontact.Text, out ProvinceContact))

    {


    cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value =ProvinceContact;

    }

    else

    {


    cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value =null;

    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 14, 2010 3:54 AM
  • User197322208 posted

    I'm planning to just = 0 instead of =DBNull.Value

    Good!

    The use of null versus 0 ( or empty for varchar) is debated. Do whatever you please.

    For me, null is " it does not meant to be here"

    0 will be "user entered data"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 15, 2010 12:06 AM
  • User-446933086 posted

    When you setting value from C# code to Sqlparameter then always set

    DBNULL.

    Whether it is int or String.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 15, 2010 1:17 AM

All replies

  • User197322208 posted

     cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value = provincecontact.Text;  

    int ProvinceContact;

    if(int.TryParse( provincecontact.Text, out ProvinceContact))

    {


    cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value =ProvinceContact;

    }

    else

    {


    cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value =null;

    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 14, 2010 3:54 AM
  • User14881260 posted

    I didn't notice that the textboxes with null values are being accepted by the databases. The error 'input string is not in the correct format' is the contact number being null because of the fact that it is a BigInt.


    How to fix this to accept even if it's null.?

    Monday, June 14, 2010 3:56 AM
  • User-131059102 posted

    Just a guess but does the DB column that is throwing the error allow Nulls? If not set it to allow null values...  

    Monday, June 14, 2010 3:59 AM
  • User14881260 posted

    It allows null values because the other textboxes that is set to nvarchar, even it's empty, is being accepted. Unlike the contact number which is set to bigint, aren't accept if it's empty(input string is not in the correct format). I believe that empty value is not being detected as '0' for bigint.

    Monday, June 14, 2010 4:32 AM
  • User197322208 posted

    I believe that empty value is not being detected as '0' for bigint.

    Please see my code...


    Monday, June 14, 2010 4:35 AM
  • User14881260 posted

    Oh sorry. I forgot to report the result.


    The parameterized query '(@FamilyName nvarchar(6),@FirstName nvarchar(4),@MiddleName nvar' expects the parameter '@ProvinceContact', which was not supplied.

    Monday, June 14, 2010 4:57 AM
  • User14881260 posted

    I believe your code and this code is the same as it gives similar error .


            if (provincecontact.Text != "")
            {
                cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value = provincecontact.Text;
            }
            else
            {
                cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value = null;
            }



    Monday, June 14, 2010 5:26 AM
  • User197322208 posted

    If are you sure that you put the "else" part,  please replace

      cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value = null

    with

      cmd.Parameters.Add("@ProvinceContact", SqlDbType.BigInt).Value = DBNull.Value


    Monday, June 14, 2010 5:29 AM
  • User14881260 posted

    I'm not infront of the computer right now that i'm working with but could you explain me the error?

    Monday, June 14, 2010 7:05 AM
  • User14881260 posted

    Is null value and 0 the same? It's just that, I read some articles that as much as possible I shouldn't use NULL. What i'm trying to say is an integer cannot be empty, and I believe that making 0 show that's empty.


    I'm planning to just = 0 instead of =DBNull.Value

    Monday, June 14, 2010 9:55 PM
  • User197322208 posted

    I'm planning to just = 0 instead of =DBNull.Value

    Good!

    The use of null versus 0 ( or empty for varchar) is debated. Do whatever you please.

    For me, null is " it does not meant to be here"

    0 will be "user entered data"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 15, 2010 12:06 AM
  • User-446933086 posted

    HI

    There are two thing regarding db field if you enter 0 in the field than this mean that this is value which was provided by user.

    And null mean that there is no change in DB field. If you want to maintain when enter no data than it should be null not 0. Because you cannot figure out which 0 is entered by user and which 0 is replacement of null.

    I will suggest you to use DBNull.

    Tuesday, June 15, 2010 12:39 AM
  • User14881260 posted

    What if it is a string? Do i need to set it up NULL?

    Tuesday, June 15, 2010 1:04 AM
  • User-446933086 posted

    When you setting value from C# code to Sqlparameter then always set

    DBNULL.

    Whether it is int or String.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 15, 2010 1:17 AM
  • User-744022866 posted

    As ignatandrei suggested, first try to convert the input to an int, if failed insert null value...


    Tuesday, June 15, 2010 1:18 AM