Save listbox to SQL database, "Procedure or function Music_Add has too many arguments specified-CodePudding

Here is the stored procedure on my DB. I want to save data of list to my SQL database, i can save one data, but when i try to add more it caused error like this "Procedure or function Music_Add has too many arguments specified". Can someone help me how to fix that error... Thanks... Here is the code to save the list to database:

private void btnsave_Click(object sender, EventArgs e)
    {
        string username = dtuser;
        conn.Open();
        SqlCommand cmd = new SqlCommand("Music_Add", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (var item in fav_list.Items)
        {
            cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@music", item.ToString());
            cmd.ExecuteNonQuery();
        }
        cmd.Dispose();
        conn.Close();
    }

CodePudding user response：

Consider something more like this:

private void btnsave_Click(object sender, EventArgs e)
{
    string username = dtuser;
    using var cmd = new SqlCommand("Music_Add", "conn str here");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@username", SqlDbType.VarChar, COLUMN_SIZE_HERE);
    cmd.Parameters.Add("@music", SqlDbType.VarChar, COLUMN_SIZE_HERE);
    cmd.Connection.Open();
    foreach (var item in fav_list.Items)
    {
        cmd.Parameters["@username"].Value = username;
        cmd.Parameters["@music"].Value = item.ToString());
        cmd.ExecuteNonQuery();
    }
}

Use the string/string version of SqlCommand; it's one less thing to dispose and also leads you not into the temptation of keeping ahold of SqlConnection variables; easy to forget to close it (or suffer an exception and not reach the closing code), and then you leak conenctions. You only really need to carry SqlConnections around if you're enrolling multiple commands in a transaction
Use using - it doesn't forget to dispose, even if an error occurs
Create your parameters once, and use the same size in the param as the column is wide. See Dan Guzman's blog about using AddWithValue - I'm overjoyed to read a question that actually parameterizes instead of risking SQL Injection, but be aware that there are potential performance penalties to using AddWithValue in some circumstances. It's unlikely to affect you in this case, but generally on SQLServer it's only one rule of thumb to remember, to get into the habit of avoiding AWV rather than try and work out when it's safe and when it's a problem
Repeatedly set your values in the loop and execute the query. You could even consider batching with a table valued parameter set, for better performance