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();
}

"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... — Jon Skeet
– Jon Skeet, Commented Oct 17, 2012 at 17:02
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. — paparazzo
– paparazzo, Commented Oct 17, 2012 at 17:20
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. — Mathias
– Mathias, Commented Oct 17, 2012 at 17:33
So this is not the actual code? I bet ExecuteNonQuery throw an exception or returns a 0. Your code does zero error checking. -1 — paparazzo
– paparazzo, Commented Oct 17, 2012 at 17:33

TotPeRo · Accepted Answer · 2021-03-05 07:57:02Z

58

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(); 
            }
 }

edited Mar 5, 2021 at 7:57

TotPeRo

6,8314 gold badges52 silver badges66 bronze badges

answered Oct 17, 2012 at 17:27

Oluwafemi

15k11 gold badges47 silver badges59 bronze badges

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

3 Comments

paparazzo Over a year ago

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

KevinDW Over a year ago

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

jrypkahauer Over a year ago

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

James · Accepted Answer · 2014-07-09 15:35:25Z

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");
        }

    } 
}

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

Gene S · Accepted Answer · 2012-10-17 17:05:17Z

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(); 
    } 
}

Robert · Accepted Answer · 2016-01-13 09:12:02Z

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();
        }
    }
}

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

1

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.

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Oct 17, 2012 at 17:03

Lukasz M

5,7532 gold badges24 silver badges30 bronze badges

2 Comments

TylerH Over a year ago

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.

Lukasz M Over a year ago

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.

Sergey Berezovskiy · Accepted Answer · 2012-10-17 17:26:05Z

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 
                                  });
    }
}

TylerH · Accepted Answer · 2024-01-29 22:50:13Z

0

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);

edited Jan 29, 2024 at 22:50

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Oct 17, 2012 at 17:04

Ravindra Bagale

17.7k9 gold badges46 silver badges70 bronze badges

Collectives™ on Stack Overflow

Insert into C# with SQLCommand

7 Answers 7

3 Comments

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Linked

Related