Insert into C# with SQLCommand

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

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

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

    } 
}

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

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