Conversion failed when converting the varchar value to int

Question

0.00/5 (No votes)

See more:

Hi There is a conversion error "Conversion failed when converting the varchar value to int"

Note: Age(txtAge) column is integer.

What I have tried:

C#

<pre>public void Bind_ddlState()
        {


            SqlCommand cmd = new SqlCommand("SELECT [StateID] ,[StateName]   FROM [StateDetails] where [StateID]='" + DDlCountry.SelectedValue + "'", cn);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            Ddlstate.DataSource = dt;
            Ddlstate.DataTextField = "StateName";
            Ddlstate.DataValueField = "StateID";
            Ddlstate.DataBind();

            cn.Close();
        }



        protected void Button1_Click1(object sender, EventArgs e)
        {
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO [RegisDetails] (Name, Department,Salry,DOJ,DOB, Age,Country,Stat,Phone,Email,Pincode)  VALUES ('" + txtname.Text + "','" + txtDept.Text + "','" + txtSalary.Text + "','" + txtDoj.Text + "','" + txtDob.Text + "','" + txtAge.Text + "','" + DDlCountry.Text + "','" + Ddlstate.Text + "','" + txtPhone.Text + "','" + TxtEmail.Text + "','" + txtPin.Text + "' )", cn);

                cmd.ExecuteNonQuery();
                cn.Close();
            }

        }


        protected void Button2_Click1(object sender, EventArgs e)
        {
            this.ClearCachedClientID();


        }

        protected void DDlCountry_SelectedIndexChanged(object sender, EventArgs e)
        {

            Bind_ddlState();

        }

      

        protected void txtDate1_TextChanged(object sender, EventArgs e)
        {


        }

        protected void txtDate2_TextChanged(object sender, EventArgs e)
        {

        }

        protected void txtAge_TextChanged(object sender, EventArgs e)
        {
            DateTime dateOfBirth = new DateTime();
            int currentYear, currentMonth, birthMonth, birthYear, years, months;
            dateOfBirth = Convert.ToDateTime(txtAge.Text);
            currentYear = Convert.ToInt32(DateTime.Now.Year);
            currentMonth = Convert.ToInt32(DateTime.Now.Month);
            birthYear = Convert.ToInt32(dateOfBirth.Year);
            birthMonth = Convert.ToInt32(dateOfBirth.Month);
            years = currentYear - birthYear;
            if ((currentMonth - birthMonth > 0))
            {
                months = Convert.ToInt32(currentMonth - birthMonth);
            }
            else
            {
                years = years - 1;
                months = Convert.ToInt32((12 - birthMonth) + currentMonth);
            }
            txtAge.Text = years.ToString() + "/" + months.ToString();


        }

Posted 11-Jan-17 21:56pm

Member 12605293

Updated 12-Jan-17 16:58pm

v3

Add a Solution

Comments

F-ES Sitecore 12-Jan-17 4:02am

What is "year"? "months"? The error is telling you you are trying to convert a varchar (string in a database) to an int, so if that field contains "hello" then a conversion to int will fail. I see no SQL in your question either, I'm not sure you're posting the appropriate bit of code.

Jon McKee 12-Jan-17 4:13am

My thoughts exactly. Seems like age might be stored as a "MM/DD/YYYY" string.

Member 12605293 12-Jan-17 4:19am

Hi Jon ,Have a look at my code. Is there a need for int.Parse/int.TryParse??

F-ES Sitecore 12-Jan-17 4:26am

What line do you get the error on? I can see it possibly happening when you execute the INSERT. We don't know what your field types are though, what is int, what is varchar or anything else. As I've said already you are trying to convert a string to an int somewhere, we can't access your database or your data so you just need to track down where this conversion is happening.

Member 12605293 12-Jan-17 4:28am

Name varchar(50) Checked
Department varchar(50) Checked
Salry int Checked
DOJ datetime Checked
DOB datetime Checked
Age int Checked
Country varchar(30) Checked
Stat varchar(30) Checked
Phone int Checked
Email varchar(50) Checked
Pincode int Checked
Unchecked

F-ES Sitecore 12-Jan-17 5:29am

Your "Age" field is an int yet you are inserting the value of txtAge in quotes. First off the quotes mean the value is a string, secondly the value of txtAge is something like "20\5". How do you convert that to an int?

You just have to ensure the values going into the fields are correct, using parameterised queries will help you with this, but you also need to decide exactly what is going to be stored in Age and how to make sure the value gets there.

Member 12605293 12-Jan-17 5:13am

Yes exactly the same line "I can see it possibly happening when you execute the INSERT"

Jon McKee 12-Jan-17 4:27am

If you mean the Convert.ToInt32 methods, then no. DateTime stores the invidivual year/month/day as an int.

Side Note: There is a slight difference between Convert.ToInt32 and Int32.Parse. Convert returns 0 instead of throwing an exception on an invalid argument.

Afzaal Ahmad Zeeshan 12-Jan-17 4:46am

Is that varchar valid number? Make sure there are no extra symbols in there, such as special characters.

Member 12605293 12-Jan-17 4:54am

Hi Afzaal
Which varchar???

Afzaal Ahmad Zeeshan 12-Jan-17 5:08am

The column type.

Member 12605293 12-Jan-17 5:12am

Yes it is valid

Richard Deeming 12-Jan-17 15:51pm

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

3 solutions

Solution 3

First off, appending the values from your input to your SQL statement is a big NO NO, as it can lead you to SQL injection attack. Read: Protect Your Data: Prevent SQL Injection[^][^]

Second, make it a habit to put objects that eat resources such as SqlConnection within a using statement to ensure that objects will be properly disposed and closed after they are used.

Third, don't write your code for calculating the age at TextChanged event because that event will always be triggered when you type/change something from the TextBox. As an alternative, you could instead write the code at Button's Click event.

Now down your issue...

You said, Age column is of type integer. Now looking at your code at TextChanged event, you are using this:

C#

txtAge.Text = years.ToString() + "/" + months.ToString();

Now your txtAge.Text value contains the character "/" which triggers the error you have. An integer type can only accept numeric values so you need to ensure that the value of your TxtAge should only contain numeric values. To prevent that, you could:

1. implement client-side validation to allow only numeric values for your txtAge using JavaScript/jQuery
2. as suggested, use Int32.TryParse Method (System)[^] to re-validate the value at the server. For example:

C#

int age;
if(Int32.TryParse(txtAge.Text.Trim(), out age))
{
    //do stuff here
    //use the value of age variable in your query
}
else
{
    Response.Write("Not valid");
}

Posted 12-Jan-17 16:58pm

Vincent Maverick Durano

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2017-01-11T22:33:00

Start by not doing it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Use the various TryParse methods to convert your user inputs to appropriate datatypes - numbers into int or double variables, dates into DateTime and so forth - and then pass the converted values to SQL as parameters, remembering to report any problems to the user instead of just blindly assuming his input is correct.

That way, not only is your database safer from deliberate or accidental damage, but the problem you are noticing will disappear at the same time.

Ramesh Kumar Barik · Accepted Answer · 2017-01-12T04:54:00

Solution 2

"Conversion failed when converting the varchar value to int"

This issue occurred because of inserting a string value into an integer field in the table.

In you database table there are 4 columns (Salary, Age, Phone, Pincode) which are of datatype int.

So Please check whether all values which your passing all are integer or not for these columns (Salary, Age, Phone, Pincode).

Note: Any integer value quoted with single quote can insert to an integer field with out giving any error. Ex: '0' or '123'

Posted 12-Jan-17 4:54am

Ramesh Kumar Barik

Comments

Er.Muneer Khan 12-Jan-17 11:32am

Convert.ToInt64(txtAge.Text);

Vincent Maverick Durano 12-Jan-17 22:35pm

This isn't safe. What if txtAge.Text contains non numeric values?

Er.Muneer Khan 17-Jan-17 6:30am

chech validation of age text box simple