Error converting data type varchar to numeric in c# program

Question

Please Help me out,This is my database table in sql-server

(empcode    varchar(50)
firstname   varchar(50)
lastname    varchar(50)
gender  varchar(50)
address varchar(50)
contactno   numeric(18, 0)
bloodgroup  varchar(50)
dateofbirth date
country varchar(50)
qid varchar(50)
passportno  varchar(50)
passportexpiredate  date
designation varchar(50)
doj date    
doexpid date
pf_acc_no   numeric(18, 0)  
agreementstartdate  date    
agreementenddate    date    
department  varchar(50) 
basic_sal   numeric(18, 0)  
remarks varchar(50) 
empimage    image)

and I'm Trying to insert my data through c# windows forms but i'm getting error as "Error converting data type varchar to numeric." Here below is my c# code

private void button2_Click(object sender, EventArgs e)
    {
        cn.Open();
        if (!empid())
        {
            DialogResult d = new DialogResult();
            d = MessageBox.Show("Do You Declare Yourself All The Information Of This Employee Is Correct To Save?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (d == DialogResult.Yes)
            {
                int i = 0;
                SqlCommand cmd = new SqlCommand("INSERT INTO Employee(
     empcode,firstname,lastname,gender,address,contactno,bloodgroup,
    dateofbirth, country,qid,passportno,passportexpiredate,designation,
    doj,doexpid,pf_acc_no, agreementstartdate,agreementenddate,
    department,basic_sal,remarks,empimage) 
    VALUES('" + txtempcode.Text + "','" + txtfrstname.Text + "', 
    '" + txtlstname.Text + "','" + combogender.Text + "','" +
    txtaddr.Text + "','" + txtcont.Text + "','" + txtblodgrp.Text + "', '" + 
    dob.Value.ToString("yyyy/MM/dd") + "' ,'" + txtcountry.Text + "','" + tqid.Text + "','" + 
    txtpassportno.Text + "', '" + passexpdate.Value.ToString("yyyy/MM/dd") + "' ,'" + 
    combodesig.Text + "', '" + doj.Value.ToString("yyyy/MM/dd") + "', '" + doexpqid.Value.ToString("yyyy/MM/dd") + "', '" + 
    txtqibacc.Text + "', '" + agreestartdate.Value.ToString("yyyy/MM/dd") + "','" + agreeenddate.Value.ToString("yyyy/MM/dd") + "' ,'" + combobranch.Text + "','" + txtnetsalary.Text + "','" + txtremark.Text + "',@empimage) ", cn);

                    MemoryStream stream = new MemoryStream();
                    pb1.Image.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
                    byte[] pic = stream.ToArray();
                    cmd.Parameters.AddWithValue("@empimage", pic);
                i = cmd.ExecuteNonQuery();

                if (i > 0)
                {
                    MessageBox.Show("Successfully Inserted Employee Record" + i);
                }
                cn.Close();
                showdata();
                clear();
            }
        }
    }

public bool empid()
    {
        using (SqlConnection con = new SqlConnection("Data Source=SAFIYA-PC;Initial Catalog=dbEmployee;User ID=sa;Password=sa$123"))
        {
            con.Open();
            string query = "select empcode from Employee where empcode = '" + txtempcode.Text + "'";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                empCode = dr["empcode"].ToString();
                if (empCode != "0")
                {
                    MessageBox.Show("Id already Existed Please use Another One!!!!!!");
                    return false;
                }
                con.Close();
            }
            return true;
        }                        
    }

Your code is vulnerable to SQL injection. Use named parameters instead of concatenating strings. Furthermore your code is not very readable. Please reformat it. — Dai
– Dai, Commented Jan 12, 2016 at 7:32
Also your DB types don't seem appropriate. Use nvarchar for "human-readable" strings instead of varchar; also note that text and image are both deprecated, use varbinary instead. — Dai
– Dai, Commented Jan 12, 2016 at 7:33

Community · Accepted Answer · 2017-05-23 12:15:23Z

2

Edit: the comment below is quite right, the immediate issue is that you have single quotes around the data you are trying to put in the contactno numeric(18, 0) field which is telling SQL to interpret that value as a varchar.

Change "','" + txtcont.Text + "','" to "'," + txtcont.Text + ",'" (i.e. remove the single quotes either side of the text box value) and do the same for any other numeric fields.

However, as is mentioned in other responses, this general approach of concatenating together a sql command string is not recommended from a security point of view. The preferred option is to use a parametrised query.

Using a parametrised query will also make it easier to deal with any nullable fields, as per this example.

(Edited to correct the answer in response to comment from Steffen)

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Jan 12, 2016 at 7:38

tomRedox

30.9k30 gold badges125 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Steffen Winkler Over a year ago

he is combining it to a string anyway so the C# type (in theory) doesn't matter. He would just need to not put those ' around values that aren't varchars on the database side.

tomRedox Over a year ago

@SteffenWinkler you're quite right, I've updated my answer accordingly

tomRedox Over a year ago

@IqbalKhan, that's great news! Feel free to accept this as the answer and upvote if it helped :)

Iqbal Khan Over a year ago

but it showing --->Incorrect syntax near ','. whenever i put null values in contactno,pf_acc_no and basic_sal

tomRedox Over a year ago

@IqbalKhan that's another issue. If the fields can be null you'd really be much better using a parametrised query. That will also be a lot safer as it guards against SQL injection attacks. Have a look at this stack overflow question for some good info on how to convert your query string: stackoverflow.com/questions/8032054/…

|

Klaudiusz bryjamus · Accepted Answer · 2016-01-12 08:40:54Z

Why don't you use parameters? It prevents your code from SQL Injection and you don't have to convert values and code is much more readable. Look at sample (it is for select statement but the same works for insert/update/delete statement):

var connectionString = "some connection string";
using (var connection = new SqlConnection(connectionString)) {
    connection.Open();
    using (var command = new SqlCommand("SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection)) {
        command.Parameters.Add(new SqlParameter("Name", dogName));
        var reader = command.ExecuteReader();
        while (reader.Read()) {
            int weight = reader.GetInt32(0);
            string name = reader.GetString(1);
            string breed = reader.GetString(2);
            Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}",
            weight,
            name,
            breed);
        }
    }
}

Dieter Meemken · Accepted Answer · 2016-01-12 08:08:33Z

0

You are directly storing the text value of your controls in your numeric fields (contactno, pf_acc_no and basic_sal). Are the values really sql-string representations of numeric values?

I would try to convert them to numeric value

double dValue = 0.0;
double.TryParse(textBox1.Text, out dValue);

and then, in the sql string convert to their sql-string representations with dValue.ToString() or a special dedicated method.

answered Jan 12, 2016 at 8:08

Dieter Meemken

1,9672 gold badges19 silver badges22 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:44:56Z

0

As per your table structure, there are 3 fields that are of numeric datatype. These are - contactno, pf_acc_no and basic_sal

And the values for these fileds are txtcont.Text, txtqibacc.Text and txtnetsalary.Text respectively, And your are also putting ' around them, that is why they are being treated as varchar filed.

While constructing the sql statement, you should pass the values for these fields as numeric, which is to say without ' marks. So just remove ' before and after these fields and your query should work fine.

SqlCommand cmd = new SqlCommand("INSERT INTO Employee(
     empcode,firstname,lastname,gender,address,contactno,bloodgroup,
    dateofbirth, country,qid,passportno,passportexpiredate,designation,
    doj,doexpid,pf_acc_no, agreementstartdate,agreementenddate,
    department,basic_sal,remarks,empimage) 
    VALUES('" + txtempcode.Text + "','" + txtfrstname.Text + "', 
    '" + txtlstname.Text + "','" + combogender.Text + "','" +
    txtaddr.Text + "'," + txtcont.Text + ",'" + txtblodgrp.Text + "', '" + 
    dob.Value.ToString("yyyy/MM/dd") + "' ,'" + txtcountry.Text + "','" + tqid.Text + "','" + 
    txtpassportno.Text + "', '" + passexpdate.Value.ToString("yyyy/MM/dd") + "' ,'" + 
    combodesig.Text + "', '" + doj.Value.ToString("yyyy/MM/dd") + "', '" + doexpqid.Value.ToString("yyyy/MM/dd") + "', " + 
    txtqibacc.Text + ", '" + agreestartdate.Value.ToString("yyyy/MM/dd") + "','" + agreeenddate.Value.ToString("yyyy/MM/dd") + "' ,'" + combobranch.Text + "'," + txtnetsalary.Text + ",'" + txtremark.Text + "',@empimage) ", cn);

In case you need to pass null to these numeric fields, use DBNull.Value as shown here - Assign Null value to the Integer Column in the DataTable

Constructing queries like these are prone to SqlInjection, suggest to use parameterized queries instead.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Jan 12, 2016 at 8:21

Yogi

9,8693 gold badges49 silver badges64 bronze badges

2 Comments

Iqbal Khan Over a year ago

but it showing --->Incorrect syntax near ','. whenever i put some null values in contactno,pf_acc_no and basic_sal

Yogi Over a year ago

Use DBNull.Value instead where you want to pass null for these values.

Khazratbek · Accepted Answer · 2016-01-12 07:50:17Z

Actually, your code is very vulnerable to SQL-injection attacks. But I don't changing your programming style; I'm changing just your insertion query:

SqlCommand cmd = new SqlCommand(@"
    INSERT INTO Employee
        (empcode, firstname, lastname, gender, address, contactno,
         bloodgroup, dateofbirth, country, qid, passportno, passportexpiredate, 
         designation, doj, doexpid, pf_acc_no, agreementstartdate, agreementenddate, 
         department, basic_sal, remarks, empimage) 
    VALUES
        ('" + txtempcode.Text + "',
         '" + txtfrstname.Text + "', 
         '" + txtlstname.Text + "',
         '" + combogender.Text + "',
         '" + txtaddr.Text + "',
         "  + txtcont.Text + ",
         '" + txtblodgrp.Text + "', 
         '" + dob.Value.ToString("yyyy/MM/dd") + "' ,
         '" + txtcountry.Text + "',
         '" + tqid.Text + "',
         "  + txtpassportno.Text + ", 
         '" + passexpdate.Value.ToString("yyyy/MM/dd") + "' ,
         '" + combodesig.Text + "', 
         '" + doj.Value.ToString("yyyy/MM/dd") + "', 
         '" + doexpqid.Value.ToString("yyyy/MM/dd") + "', 
         '" + txtqibacc.Text + "', 
         '" + agreestartdate.Value.ToString("yyyy/MM/dd") + "',
         '" + agreeenddate.Value.ToString("yyyy/MM/dd") + "' ,
         '" + combobranch.Text + "',
         "  + txtnetsalary.Text + ",
         '" + txtremark.Text + "',
         @empimage) ", cn);

Collectives™ on Stack Overflow

Error converting data type varchar to numeric in c# program

5 Answers 5

17 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

17 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related