CODEWITHSUNDEEP
c#sqlsql-server
Lazu Razvan
Lazu Razvan

Reputation: 69

Simple, ordinary SQL Server insert method

This had to be a simple, ordinary SQL insert method but when I run it and I click "button1" I get the error

An unhandled exception of type 'system.data.sqlclient.sqlexception' occurred in system.data.dll

Does anyone know what the problem is?

namespace InsertDeleteUpdate_Login
{
    public partial class Form1 : Form
    {
        SqlConnection cn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=E:\C #\InsertDeleteUpdate-Login\InsertDeleteUpdate-Login\Database1.mdf;Integrated Security=True");

        SqlCommand cmd = new SqlCommand();
        SqlDataReader dr;

        public Form1()
        {
            InitializeComponent();
            cmd.Connection = cn;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text != "" && textBox2.Text != "")
            {
                cn.Open();
                cmd.CommandText = "INSERT INTO info (ID,Name,Password)" + " VALUES ('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "')'";

                cmd.ExecuteNonQuery();

                cmd.Clone();

                MessageBox.Show("Inserare reusita");

                cn.Close();
            }
        }
    }
}

Upvotes: 0

Views: 164

Answers (3)

Ed B
Ed B

Reputation: 796

As mentioned by several people above, you should ALWAYS parameterise your queries, and you also have a trailing single quote, which is most likely what SQL Server is choking on.

Try something like this:

cmd.CommandText = "INSERT INTO info (ID, Name, Password) VALUES (@ID, @Name, @Password)";
cmd.Parameters.AddWithValue("@ID", textBox1.Text);
cmd.Parameters.AddWithValue("@Name", textBox2.Text);
cmd.Parameters.AddWithValue("@Password", textBox3.Text);
cmd.ExecuteNonQuery();

Upvotes: 1

Philip Stuyck
Philip Stuyck

Reputation: 7467

The root cause of your problem is that you are not using parameterized queries and are trying to create an sql string on the fly. As a result you make an error in the assembling code of that string. But if you use a parameterized query the chance of running into an issue like that is a lot lower because you don't have to mess about with quotes and the like. On top of this, you cannot have a sql injection attack if you use parameters and it makes the code more readable too.

Read http://www.dotnetperls.com/sqlparameter on how to use a parameterized query the way it should be done and don't just fix the textual error in the querystring. It is not the way it is supposed to be done.

This is a good explanation too : http://www.dreamincode.net/forums/topic/268104-parameterizing-your-sql-queries-the-right-way-to-query-a-database/

Upvotes: 3

MarioAna
MarioAna

Reputation: 865

I can't add comments yet, but it looks like you might have an extra single quote after the last close bracket that shouldn't be there.

Upvotes: 1

Related Questions