C# issue within visual studio.

Question

1.00/5 (1 vote)

See more:

Im using visual studio 2019 and im trying to insert a record into the local database ive created within visual studio. during the process, im receiving this error

System.Data.SqlClient.SqlException: 'Conversion failed when converting the varchar value 'string' to data type int.'

its telling me this line of code is wrong cmd.ExecuteNonQuery();
but i dont understand what to change it too

this is my code

SQL

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace EmployeeDataBaseProject
{
    public partial class Form1 : Form
    {

        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Owner\Documents\Employee.mdf;Integrated Security=True;Connect Timeout=30");
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO Employee_Table VALUES('" + textBox1.Text + "',' " + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + comboBox1.Text + "')", con);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Inserted Data Successfully");
            con.Close();




        }
        

    }
}

What I have tried:

I dont understand how to fix this

Posted 29-Apr-20 0:00am

bensoccer94

Updated 29-Apr-20 1:10am

MadMyche

v2

Add a Solution

Comments

Sinisa Hajnal 29-Apr-20 6:03am

1) don't EVER concatenate user input (google SQL Injection)
2) check that all data contains correct TYPE of data (it would help if we had column names or useful control names txtEmploymentDate or txtSalary or something)

F-ES Sitecore 29-Apr-20 7:14am

You are putting your values in quotes which indicates they are text values, however one of the fields in your table is expecting an int. As others have said, switch to using parameterised queries, it makes the code easier to follow and more robust. For the field that needs an int you will need to convert the text value to an int vlue first using int.TryParse

Richard Deeming 29-Apr-20 10:46am

In addition to the SQL Injection[^] vulnerability in your code, you haven't specified the columns for the INSERT statement.

Always specify the names of the columns you're inserting into:

INSERT INTO Employee_Table (Column1, Column2, Column3, Column4, Column5) VALUES (@V1, @V2, @V3, @V4, @V5)

(You'll obviously need to use the correct column names for your table.)

2 solutions

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 · Answer 1 · 2020-04-29T00:05:00

Don't do 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. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

And never trust users to type numbers. Validate their input by converting integer value inputs to integers using TryParse, then pass the converted value via a parameterised query:

C#

int value;
if (!int.TryParse(MyTextbox.Text, out value))
   {
   ... report problem to user ...
   return;
   }

MadMyche · Answer 2 · 2020-04-29T01:11:00

First things first- your code is susceptible to SQL Injection.
NEVER EVER concatenate SQL commands and variables to form a query.

The recommended way to use variables and commands is to use the SqlParameter[^] class to add your variables to the Command object.

Applied to your code will give us something similar to this

C#

private void button2_Click(object sender, EventArgs e)
{
	string Tx1 = textbox1.Text;
	string Tx2 = textbox2.Text;
	string Tx3 = textbox3.Text;
	string CB1 = comboBox1.Text;
	
	con.Open();
	string qry = "INSERT INTO Employee_Table VALUES (@Tx1, @Tx2, @Tx3, @CB1)";
	SqlCommand cmd = new SqlCommand(qry, con);

	cmd.Parameters.AddWithValue("@Tx1", Tx1);
	cmd.Parameters.AddWithValue("@Tx2", Tx2);
	cmd.Parameters.AddWithValue("@Tx3", Tx3);
	cmd.Parameters.AddWithValue("@CB1", CB1);

	cmd.ExecuteNonQuery();

	MessageBox.Show("Inserted Data Successfully");

	con.Close();
}

So now that we got that taken care of, lets look at your error

System.Data.SqlClient.SqlException
'Conversion failed when converting the varchar value 'string' to data type int.'

This is pretty self explanatory- You are trying to place a string value into a database field that is defined to only accept integers.

Which one of these values is the problem really cannot be determined because we do not know what the table structure is, and there is nothing intuitive from the code you have provided.

What I would do is set a breakpoint on immediately after the 4 variables are populated at the opening of the script, and run this in debug mode. When you get to that point, check to see what the values that are assigned to your text- and check- boxes; and compare that to the data-types within the database.

According to the error- one of these is supposed to be an integer. Then all you need to do is change the variable declaration to reflect an Int instead of a String.

Reference:
SqlParameter Class (System.Data.SqlClient) | Microsoft Docs[^]

C# issue within visual studio.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0