ExecuteNonQuery not working in C#

Question

I am building a database using Visual Studio 2008 c# and when I'm a trying to insert a new record into my database it appears that ExecuteNonQuery has not initialized. I copy my code, hope anyone can help me in this because I am new.

 private void button1_Click(object sender, EventArgs e)
 {
     SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Usuario\Documents\Visual Studio 2010\Projects\Nova\Nova\Database1.mdf;Integrated Security=True;User Instance=True");
     SqlCommand cmd = new SqlCommand();
     cn.Open();
     cmd.CommandText = "insert into Database1.mdf(Codigo,Nombre,Cantidad,Tipo) values('"+comboBox1.Text+"','"+textBox3.Text+"','"+textBox1.Text+"','"+comboBox2.Text+"')";
     cmd.ExecuteNonQuery();
     cmd.Clone();
     cn.Close();
     MessageBox.Show("Acabas de agregar un producto");
 }

Your code is vulnerable to SQL injection. Also, you should use a `using` block with classes that implement the IDisposable interface, especially with the SqlConnection class in order to close the connection in case of an exception. — Brad M, Jun 03 '13 at 18:31
Also, Database1.mdf is the name of the file. Do you have any tables defined in your database? — Maciej, Jun 03 '13 at 18:32
And what 'cmd.Clone(); was supposed to do? typo? (cmd.Close?...). — Chris, Jun 03 '13 at 18:42

score 14 · Answer 1 · edited Jan 15 '14 at 23:21

14

You haven't set the connection to your command:

cmd.Connection = cn;

edited Jan 15 '14 at 23:21

Eran

387,369
54
702
768

answered Jun 03 '13 at 18:31

Fabian Bigler

10,403
6
47
70

This got me as well :( I wish it would throw an error – heymega Mar 07 '14 at 11:52

Steve · Answer 2 · 2013-06-03T21:29:47.193

10

You have numerous problems in your code:

First: The insert into statement requires a target datatable not the name of the MDF file
Second: Employ the using statement to close and dispose the connections
Third: Parametrized query to avoid parsing problems and sql injections

Fourth: You need to associate the connection to the command (Easily done at the SqlCommand constructor)

using(SqlConnection cn = new SqlConnection(.......))
using(SqlCommand cmd = new SqlCommand("insert into table_name(Codigo,Nombre,Cantidad,Tipo)" + 
                          "values (@cod, @nom,@can,@tipo)", con))
{
    cn.Open();
    cmd.Parameters.AddWithValue("@cod", comboBox1.Text);
    cmd.Parameters.AddWithValue("@nom", textBox3.Text);
    cmd.Parameters.AddWithValue("@can", textBox1.Text);
    cmd.Parameters.AddWithValue("@tipo", comboBox2.Text);
    cmd.ExecuteNonQuery();
    MessageBox.Show("Acabas de agregar un producto");
}

EDIT The information provided by the link added by @RemusRusanu below is very important. The use of AddWithValue, whilst handy, could hinder the performance of your query. The correct approach should be the usage of a proper defined SqlParameter with both explicit datatype and parameter size. As an example

SqlParameter p = new SqlParameter("@cod", SqlDbType.NVarChar, 255).Value = comboBox1.Text;
cmd.Parameters.Add(p);

But, of course, this requires that you check the exact datatype and size of your columns.

edited Jun 03 '13 at 21:29

answered Jun 03 '13 at 18:39

Steve

213,761
22
232
286

You have an extra semi-colon just before the first `{`. Also, even though you don't *need* curly braces after the first `using` statement, they would make it easier to see the scope. – egrunin Jun 03 '13 at 18:44
Yes, copy paste Always fails me – Steve Jun 03 '13 at 18:44
I really disagree on the curly brace though – Steve Jun 03 '13 at 18:46
Here you go again adding hyperlinks, better formatting and typing much faster. Touche! ;) – Evan L Jun 03 '13 at 18:48
2

+1 but FYI `AddWithValue` is an anti-pattern. See [How Data Access Code Affects Database Performance](http://msdn.microsoft.com/en-us/magazine/ee236412.aspx) – Remus Rusanu Jun 03 '13 at 20:43
I never use Parameters.AddWithValue, except when I don't know the datatype of my object (e.g. dynamic queries). – Fabian Bigler Jun 04 '13 at 18:20

Evan L · Answer 3 · 2013-06-03T18:54:23.790

You did not initialize your SqlCommand with a connection. Also, you should really enclose everything here with using. And consider using parametarized commands to avoid SQL Injection.

   private void button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Usuario\Documents\Visual Studio 2010\Projects\Nova\Nova\Database1.mdf;Integrated Security=True;User Instance=True"))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "insert into databaseTableName (Codigo,Nombre,Cantidad,Tipo) values (@Codigo, @Nombre, @Cantidad, @Tipo)";
                cmd.Parameters.AddWithValue("@Codigo", comboBox1.Text);
                cmd.Parameters.AddWithValue("@Nombre", textBox3.Text);
                cmd.Parameters.AddWithValue("@Cantidad", textBox1.Text);
                cmd.Parameters.AddWithValue("@Tipo", comboBox2.Text);
                cmd.Connection = cn; //this was where the error originated in the first place.
                cn.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Acabas de agregar un producto");
            }
        }
    }

Truth! Edited that out, was an oversight since I was trying to beat you to the answer ;) — Evan L, Jun 03 '13 at 18:54

ExecuteNonQuery not working in C#

3 Answers3

Linked