Error converting data type nvarchar to numeric.

I am not sure why I am getting this error message.  Can someone point out why I keep getting this error message.  Here is my code

------------------------------------------------------------------
protected void PopulateDRRds()
        {

            SqlDataAdapter sqlDA = CreateDRRAdapter();


            HDSDataSet.Clear();

            sqlDA.FillSchema(HDSDataSet, SchemaType.Source);
            sqlDA.Fill(HDSDataSet, "DRRPHI");--------------------------------------------------------------------  ERROR OCCURS HERE

            this.UltraWebGrid1.DataSource = HDSDataSet.Tables["DRRPHI"];

           
            UltraWebGrid1.DisplayLayout.Bands.Clear();
            this.UltraWebGrid1.DataBind();

        }



        //------------   Adapter
        protected SqlDataAdapter CreateDRRAdapter()
        {

           
            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(SearchString, conn);

            if (tbName.Text != "")
            {
                tbName.Text = tbName.Text + "%";
            }

           

            cmd.Parameters.Add(new SqlParameter("@DRR1", tbName.Text));
            cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", tbDocumentId.Value));------THE ERROR ONLY OCCURS WHEN I LEAVE THIS FIELD BLANK
            cmd.Parameters.Add(new SqlParameter("@USERNAME", tbDocCreatedBy.Text));
            cmd.Parameters.Add(new SqlParameter("@FROMDATE", datechooseFrom.Text));
            cmd.Parameters.Add(new SqlParameter("@TODATE", datechooserTo.Text));
         
            SqlDataAdapter sqlDA = new SqlDataAdapter(cmd);
            return sqlDA;
        }

        //------------   Search Tab
        private String ConnString
        {
            get
            {
                //HDS ConnectionString comes from the Web.Config
                return ConfigurationManager.ConnectionStrings["123123ConnectionString"].ConnectionString;

            }
        }

        //------------   Search Tab
        private String SearchString
        {
            get
            {
                string srchCmd;
                srchCmd = "";

                if (datechooseFrom.Text != "Null")
                {

                    srchCmd = "SELECT DRRPHI.DOCUMENTID, DRRPHI.MODIFIEDDATE, DRRPHI.USERNAME, DRRPHI.DRR1 FROM DRRPHI WHERE DRRPHI.DOCUMENTID = @DOCUMENTID OR DRRPHI.MODIFIEDDATE >= @FROMDATE OR DRRPHI.MODIFIEDDATE <= @TODATE OR DRRPHI.DRR1 like @DRR1";
                   
                }


                if (datechooseFrom.Text == "Null")
                {

                    srchCmd = "SELECT DRRPHI.DOCUMENTID, DRRPHI.MODIFIEDDATE, DRRPHI.USERNAME, DRRPHI.DRR1 FROM DRRPHI WHERE DRRPHI.DOCUMENTID = @DOCUMENTID OR DRRPHI.DRR1 like @DRR1";
                   
                }
                return srchCmd;
               

            }
           
        }

    }
kwh3856Asked:
Who is Participating?
 
GreymanMSCConnect With a Mentor Commented:
cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", tbDocumentId.Value));------THE ERROR ONLY OCCURS WHEN I LEAVE THIS FIELD BLANK

---
Well then, don't leave the field blank.

Seriously, the problem appears to be that an empty string ("") cannot be converted to a numeric value.  So you should pretest and substitute DBNull.Value.
If (String.IsNullOrEmpty(tbDocumentId.Value) {
 cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", DBNull.Value));
}else{
 cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", tbDocumentId.Value));
}

Open in new window

0
 
philipjonathanCommented:
What's the error message? What type of object is tbDocumentId?
0
 
kwh3856Author Commented:
The error message is
Error converting data type nvarchar to numeric.
The data type in SQL is decimal(9,4)
 
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
kwh3856Author Commented:
I get an invalid expression term else.
Where did I mess up when I copied it in?
 

If (String.IsNullOrEmpty(tbDocumentId.Value));
{
cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", DBNull.Value));
}
else;
{
cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", tbDocumentId.Value));
}
0
 
kwh3856Author Commented:
Got it but new error message
Error 3 Argument '1': cannot convert from 'object' to 'string'  

if (String.IsNullOrEmpty(tbDocumentId.Value))
{
cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", DBNull.Value));
}
else

{
cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", tbDocumentId.Value));
}
 
0
 
philipjonathanCommented:
Well, what about this? The same idea as GreymanMSC, but substitute 0, instead of DBNull.Value. Btw, I hope you don't have any document with documentID = 0?
If (String.IsNullOrEmpty(tbDocumentId.Value) {
 cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", 0));
}else{
 cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", tbDocumentId.Value));
}

Open in new window

0
 
philipjonathanCommented:
>Error 3 Argument '1': cannot convert from 'object' to 'string'  

Sorry for overlapping comments.
Seems like the problem now is with String.IsNullOrEmpty, it expects a string as argument, which apparently tbDocumentId.Value is not.
That's why I asked you previously what is tbDocumentId.Value?
0
 
DarITUserCommented:
Please try the following

cmd.Parameters.Add(new SqlParameter("@DOCUMENTID",tbDocumentId.Value == null ? 0 : tbDocumentId.Value));

Open in new window

0
 
kwh3856Author Commented:
I would like to thank each of you for your help and education.  After multiple tries and combinations of above  I finally got it to work with GreymanMSC code.  I only had to change .value to .text.  Thank you very much to each of you.

Here was the final code that worked.

if (String.IsNullOrEmpty(tbDocumentId.Text))
            {
                cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", DBNull.Value));
            }
            else
            {
                cmd.Parameters.Add(new SqlParameter("@DOCUMENTID", tbDocumentId.Value));
            }
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.