Click here to Skip to main content
12,635,398 members (24,862 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET SQL-Server
Hi,
In my SQL Server database table I have a column of type float and it allow null values.
But when from my web form user left that column empty then get error message:
System.FormatException was unhandled by user code
Message="Input string was not in a correct format."
 

What to do handle this?
Thanks.

Here is the code
This is code from .cs file code

protected void btnAdd_Click(object sender, EventArgs e)
    {

        if (objLotCreation.Insert(ddlDepot.SelectedValue, txtlotcode.Text.Trim(), txtDepo_stackno.Text.Trim(), txtSPECIES_Ename.Text.Trim(),
                                  txtSPECIES_Uname.Text.Trim(), txtSPECIES_Kname.Text.Trim(), txtGrade.Text.Trim(), Convert.ToInt32(txtNo_of_pieces.Text),
                                  txtLength_class.Text.Trim(), txtlblGirth_class.Text.Trim(), Convert.ToDouble(txtCubic_meter.Text), Convert.ToDouble(txtUpset_price.Text),
                                  Convert.ToDouble(txtSale_Price.Text), Convert.ToInt32(Session["OfficeID"].ToString())))
        {
            lblStatus.Text = Resources.Language.Common_Record_Add;
        }
        BindGrid(Session["Language"].ToString());
        clearfields();
    }


This is business logic code
 public bool Insert(string Depot_Code, string Lot_Code, string Depo_stackno, string SPECIES_Ename, string SPECIES_Uname, string SPECIES_Kname, string Grade,
            int No_of_pieces, string Length_class, string Girth_class, double Cubic_meter,double Upset_price,double Sale_Price,int DIV_ID)
        {
            bool res = false;
            System.Collections.ArrayList sel = new System.Collections.ArrayList();

            sel.Add("SP_m_LotCreation_Insert");
            ArrayList lstParam = new System.Collections.ArrayList();

            SqlParameter param;

            param = new SqlParameter();
            param.ParameterName = "@Depot_Code";
            param.SqlDbType = SqlDbType.VarChar;
            param.Value = Depot_Code;
            lstParam.Add(param);


            param = new SqlParameter();
            param.ParameterName = "@Lot_Code";
            param.SqlDbType = SqlDbType.NVarChar;
            param.Value = Lot_Code;
            lstParam.Add(param);


            param = new SqlParameter();
            param.ParameterName = "@Depo_stackno";
            param.SqlDbType = SqlDbType.VarChar;
            param.Value = Depo_stackno;
            lstParam.Add(param);


            param = new SqlParameter();
            param.ParameterName = "@SPECIES_Ename";
            param.SqlDbType = SqlDbType.VarChar;
            param.Value = SPECIES_Ename;
            lstParam.Add(param);

            param = new SqlParameter();
            param.ParameterName = "@SPECIES_Uname";
            param.SqlDbType = SqlDbType.VarChar;
            param.Value = SPECIES_Uname;
            lstParam.Add(param);


            param = new SqlParameter();
            param.ParameterName = "@SPECIES_Kname";
            param.SqlDbType = SqlDbType.VarChar;
            param.Value = SPECIES_Kname;
            lstParam.Add(param);


            param = new SqlParameter();
            param.ParameterName = "@Grade";
            param.SqlDbType = SqlDbType.VarChar;
            param.Value = Grade;
            lstParam.Add(param);

            param = new SqlParameter();
            param.ParameterName = "@No_of_pieces";
            param.SqlDbType = SqlDbType.Int;
            param.Value = No_of_pieces;
            lstParam.Add(param);
                               

            param = new SqlParameter();
            param.ParameterName = "@Length_class";
            param.SqlDbType = SqlDbType.VarChar;
            param.Value = Depo_stackno;
            lstParam.Add(param);

            
            param = new SqlParameter();
            param.ParameterName = "@Girth_class";
            param.SqlDbType = SqlDbType.VarChar;
            param.Value = Girth_class;
            lstParam.Add(param);

            param = new SqlParameter();
            param.ParameterName = "@Cubic_meter";
            param.SqlDbType = SqlDbType.Float;
            param.Value = Cubic_meter;
            lstParam.Add(param);

            param = new SqlParameter();
            param.ParameterName = "@Upset_price";
            param.SqlDbType = SqlDbType.Float;
            param.Value = Upset_price;
            lstParam.Add(param);

            param = new SqlParameter();
            param.ParameterName = "@Sale_Price";
            param.SqlDbType = SqlDbType.Float;
            param.Value = Sale_Price;
            lstParam.Add(param);


            param = new SqlParameter();
            param.ParameterName = "@DIV_ID";
            param.SqlDbType = SqlDbType.Int;
            param.Value = DIV_ID;
            lstParam.Add(param);

            res = new eAuctionDAL.SQLDAL().UpdateTransData(sel, lstParam, false);
            return res;

        }


[Edit]Code block added[/Edit]
Posted 26-Dec-12 3:20am
Updated 26-Dec-12 3:24am
ProgramFOX189.4K
v2
Comments
Zafar Sultan 26-Dec-12 9:01am
   
Which of the columns allow null? You said you have one column as float and nullable but your code suggests more than one float. Can you point out the column that is giving you the error?
ruby kaur 27-Dec-12 5:28am
   
Cubic_meter and Upset_price are the column in database which allow Null values .
OriginalGriff 26-Dec-12 9:31am
   
Which line is it complaining on?
ruby kaur 27-Dec-12 5:32am
   
if (objLotCreation.Insert(ddlDepot.SelectedValue, txtlotcode.Text.Trim(), txtDepo_stackno.Text.Trim(), txtSPECIES_Ename.Text.Trim(),
txtSPECIES_Uname.Text.Trim(), txtSPECIES_Kname.Text.Trim(), txtGrade.Text.Trim(), Convert.ToInt32(txtNo_of_pieces.Text),
txtLength_class.Text.Trim(), txtlblGirth_class.Text.Trim(), Convert.ToDouble(txtCubic_meter.Text), Convert.ToDouble(txtUpset_price.Text),
Convert.ToDouble(txtSale_Price.Text), Convert.ToInt32(Session["OfficeID"].ToString())))
OriginalGriff 27-Dec-12 6:03am
   
So you need to look at the contents of your text fields:
Convert.ToInt32(txtNo_of_pieces.Text)
Convert.ToDouble(txtCubic_meter.Text)
Convert.ToDouble(txtUpset_price.Text)
Convert.ToDouble(txtSale_Price.Text)
Convert.ToInt32(Session["OfficeID"].ToString())
Either one of your textboxes does not contain a valid double/int as a sting, inwhich case the Convert.To... operation will fail and throw the error, or the Session content is not what you expect (again, not a number expressed as a double value).
Try putting some logging code in before the line so you can find out exactly what is in the textboxes and session before you execute it - but if your textboxes are user entries, then you really should check them first, by using TryParse and report a problem if the user mistyped.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

OK. In your Insert function pass everything as "string". Then check before sending these values to the stored procedure. For example if No_of_pieces is integer and it is also nullable in the database so in your insert function you should pass this value to the stored procedure as

param = new SqlParameter();
param.ParameterName = "@No_of_pieces";
param.SqlDbType = SqlDbType.Int;
param.Value = No_of_pieces == "" ? DBNull.Value : Convert.ToInt32(No_of_pieces);
lstParam.Add(param);


The fourth line in the above code is checking if the value you are passing is blank? If it is blank it will be replaced with NULL otherwise it will be converted to integer.

Another alternative is to pass everything as varchar to your stored procedure and check the values there as

declare @numberofpieces int
if @No_of_pieces = ''
set @No_of_pieces = NULL;
else
set @numberofpieces =  Convert(int,@No_of_pieces)

Hope it helps.
  Permalink  
Comments
ruby kaur 28-Dec-12 8:29am
   
Thanks for the solution.
I make changes in the procedure and it is working successfully. I tried the first solution as i wanted to learn it. It is giving me a message "operator == cannot be applied to operands of type 'int' and 'string'". Can you please guide me about that.
Zafar Sultan 28-Dec-12 9:03am
   
At which line precisely?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I think you need to be sanity check before insert, check if the value is Null or Empty if that value is empty set null value of that columns.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161208.2 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100