Insert into C# with SQLCommand

Question

What's the best way to INSERT data into a database?

This is what I have but it's wrong..

cmd.CommandText = "INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)";

cmd.Parameters.Add(new SqlParameter("@param1", klantId));
cmd.Parameters.Add(new SqlParameter("@param2", klantNaam));
cmd.Parameters.Add(new SqlParameter("@param3", klantVoornaam));

The function add data into the listBox

http://www.pictourl.com/viewer/37e4edcf (link is dead)

but not into the database..

http://www.pictourl.com/viewer/4d5721fc (link is dead)

The full function:

private void Form1_Load(object sender, EventArgs e)
{            
    conn2 = new SqlConnection();
    conn2.ConnectionString = ConfigurationManager.ConnectionStrings["connSpionshopString"].ConnectionString;
}

private void button2_Click(object sender, EventArgs e)
{         
    string sqlCmd = "SELECT naam,voornaam,klant_id FROM klant;";
    SqlCommand cmd = new SqlCommand(sqlCmd, conn2);

    conn2.Open();

    using(SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            listBox2.Items.Add(reader.GetString(0) + " " + reader.GetString(1) + "  (" + reader.GetInt16(2) + ")");
        }  
    }
    conn2.Close();
}

private void button4_Click(object sender, EventArgs e)
{
    int klantId = Convert.ToInt32(textBox1.Text);
    string klantNaam = textBox2.Text;
    string klantVoornaam = textBox3.Text;

    conn2.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn2;
    cmd.CommandText = "INSERT INTO klant(klant_id, naam, voornaam)   VALUES(@param1,@param2,@param3)";

    cmd.Parameters.AddWithValue("@param1", klantId);
    cmd.Parameters.AddWithValue("@param2", klantNaam);
    cmd.Parameters.AddWithValue("@param3", klantVoornaam);

    cmd.ExecuteNonQuery(); 

    conn2.Close();
}

Devise

"It's wrong" is about as vague as you can be about what's happening. Please explain what you're observing. Note that you do need to actually execute the command... – Poyssick

Can you give more details? What do you mean it's wrong? – Decomposition

Are you opening and closing a connection and executing the query? – Stiver

Don't use direct INSERT/UPDATE/EDIT commands in code, it is not a good practice. Try stored procedures. – Hallucinosis

If that's your code and you're pressing button4 and then button2, the values are being inserted into your database. There's no other way it would get loaded into the listbox! I'll bet whatever you're using to look at the database is looking at a snapshot-read transaction and so it appears nothing changed. – Malti

@lc yes, its a duplicate version.. That's my code, and no the values are not being insterted into my databse :s – Devise

@Devise If you are so sure as to assert it is not being added into the database, then how are you populating the listbox? I see your code here hits the database for the listbox - if it comes out it must have been put in...?!? – Malti

This is duplicate of a question that has already been closed. And it still does not even have a try catch block that was the first comment in the closed question. – Wage

I have a try catch block and it doesn't solve the problem. – Devise

@lc, i know.. I don't get it.. That's why i came to StackOverflow.. Obviously mmm – Devise

Your question is essentially the same as before: stackoverflow.com/questions/12936345/listbox-and-database-issue. Same code, and same lack of precision as to what you are asking. I would advise maybe to think of it as if writing a bug report: what is happening with your code, what you would like to happen, and where/how you see the problem happening. – Ferrol

So this is not the actual code? I bet ExecuteNonQuery throw an exception or returns a 0. Your code does zero error checking. -1 – Wage

This is how they teach us at school ........ – Devise

Also, your question is clearly not "What's the best way to INSERT data into a database?" - it is "I wrote code which doesn't work, can you help me fix it". You need to help people help you, by focusing on the exact problem you are having. – Ferrol

ExecuteNonQuery returns one.. – Devise

First it was "What's the best way to INSERT data into a database?" But some people were asking if i have an instance, connection, .. So I gave the full code .. – Devise

Prove it. Post the code with try catch and the code you use to report the ExecuteNonQuery return count. I don't believe you any more than I don't believe they teach not to error check at school. No way that returns 1 and does not insert a row in the database. – Wage

Haha.. pictourl.com/viewer/d4e00017 It returns 1 and does not insert a row in the database : pictourl.com/viewer/42b020ef BAAAAAAAAAAAAAAM.. Enough prove? – Devise

try { conn2.Open(); int value = cmd.ExecuteNonQuery(); MessageBox.Show(value.ToString()); } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn2.Close(); } – Devise

HaHa two screen shots is not code – Wage

haha try { conn2.Open() .... is not code .. right .. – Devise

Answer 1

Try confirm the data type (SqlDbType) for each parameter in the database and do it this way;

 using(SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connSpionshopString"].ConnectionString))
 {
            connection.Open();
            string sql =  "INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)";
            using(SqlCommand cmd = new SqlCommand(sql,connection)) 
            {
                  cmd.Parameters.Add("@param1", SqlDbType.Int).Value = klantId;  
                  cmd.Parameters.Add("@param2", SqlDbType.VarChar, 50).Value = klantNaam;
                  cmd.Parameters.Add("@param3", SqlDbType.VarChar, 50).Value = klantVoornaam;
                  cmd.CommandType = CommandType.Text;
                  cmd.ExecuteNonQuery(); 
            }
 }

Undersigned

If there was no conn2 then conn2.Open would fail. – Wage

In the Form_Load: conn2 = new SqlConnection(); conn2.ConnectionString = ConfigurationManager.ConnectionStrings["connSpionshopString"].ConnectionString; ? – Devise

You really do need to wrap that SqlCommand up in a using block just like the SqlConnection... otherwise you're looking at memory leaks up the patootie... :D – Bushwa

Answer 2

you can use implicit casting AddWithValue

cmd.Parameters.AddWithValue("@param1", klantId);
cmd.Parameters.AddWithValue("@param2", klantNaam);
cmd.Parameters.AddWithValue("@param3", klantVoornaam);

sample code,

using (SqlConnection conn = new SqlConnection("connectionString")) 
{
    using (SqlCommand cmd = new SqlCommand()) 
    { 
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = @"INSERT INTO klant(klant_id,naam,voornaam) 
                            VALUES(@param1,@param2,@param3)";  

        cmd.Parameters.AddWithValue("@param1", klantId);  
        cmd.Parameters.AddWithValue("@param2", klantNaam);  
        cmd.Parameters.AddWithValue("@param3", klantVoornaam);  

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery(); 
        }
        catch(SqlException e)
        {
            MessgeBox.Show(e.Message.ToString(), "Error Message");
        }

    } 
}

Halvah

Also , the connection is not assigned to the cmd. Add cmd.connection = conn before conn.open(); – Dictaphone

Answer 3

using (SqlConnection connection = new SqlConnection(connectionString)) 
{
    connection.Open(); 
    using (SqlCommand command = connection.CreateCommand()) 
    { 
        command.CommandText = "INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)";  

        command.Parameters.AddWithValue("@param1", klantId));  
        command.Parameters.AddWithValue("@param2", klantNaam));  
        command.Parameters.AddWithValue("@param3", klantVoornaam));  

        command.ExecuteNonQuery(); 
    } 
}

Topple

Answer 4

public class customer
{
    public void InsertCustomer(string name,int age,string address)
    {
        // create and open a connection object
        using(SqlConnection Con=DbConnection.GetDbConnection())
        {
            // 1. create a command object identifying the stored procedure
            SqlCommand cmd = new SqlCommand("spInsertCustomerData",Con);

            // 2. set the command object so it knows to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter paramName = new SqlParameter();
            paramName.ParameterName = "@nvcname";
            paramName.Value = name;
            cmd.Parameters.Add(paramName);

            SqlParameter paramAge = new SqlParameter();
            paramAge.ParameterName = "@inage";
            paramAge.Value = age;
            cmd.Parameters.Add(paramAge);

            SqlParameter paramAddress = new SqlParameter();
            paramAddress.ParameterName = "@nvcaddress";
            paramAddress.Value = address;
            cmd.Parameters.Add(paramAddress);

            cmd.ExecuteNonQuery();
        }
    }
}

Marseillaise

Answer 5

You should avoid hardcoding SQL statements in your application. If you don't use ADO nor EntityFramework, I would suggest you to ad a stored procedure to the database and call it from your c# application. A sample code can be found here: How to execute a stored procedure within C# program and here http://msdn.microsoft.com/en-us/library/ms171921%28v=vs.80%29.aspx.

Rojo

This does not answer the question, which is "How do I insert into SQL using C#'s sqlcommand property (family)?". This should be a comment, at best. – Selia

My answer does not explain how to fix the issue described. I was myself curious why I answered like this 12 years ago ;). After looking at the question edits history, it seems that when I answered, the question was a lot shorter and the only snippet there was the first one (setting command text and adding params), so I suppose it made more sense then. I haven't edited it later to reflect subsequent question edits. – Rojo

Answer 6

You can use dapper library:

conn2.Execute(@"INSERT INTO klant(klant_id,naam,voornaam) VALUES (@p1,@p2,@p3)", 
                new { p1 = klantId, p2 = klantNaam, p3 = klantVoornaam });

BTW Dapper is a Stack Overflow project :)

UPDATE: I believe you can't do it simpler without something like EF. Also try to use using statements when you are working with database connections. This will close connection automatically, even in case of exception. And connection will be returned to connections pool.

private readonly string _spionshopConnectionString;

private void Form1_Load(object sender, EventArgs e)
{            
    _spionshopConnectionString = ConfigurationManager
          .ConnectionStrings["connSpionshopString"].ConnectionString;
}

private void button4_Click(object sender, EventArgs e)
{
    using(var connection = new SqlConnection(_spionshopConnectionString))
    {
         connection.Execute(@"INSERT INTO klant(klant_id,naam,voornaam) 
                              VALUES (@klantId,@klantNaam,@klantVoornaam)",
                              new { 
                                      klantId = Convert.ToInt32(textBox1.Text), 
                                      klantNaam = textBox2.Text, 
                                      klantVoornaam = textBox3.Text 
                                  });
    }
}

Avent

Answer 7

Use AddWithValue(), but be aware of the possibility of the wrong implicit type conversion.

like this:

cmd.Parameters.AddWithValue("@param1", klantId);
cmd.Parameters.AddWithValue("@param2", klantNaam);
cmd.Parameters.AddWithValue("@param3", klantVoornaam);

Messier