How to update SQL Database

Question

I am trying to update specific information on a SQL database. I have searched for helpful answers on the web but havent found anyhing I can use. The code works all the way up to the point when I hit the save button. Here is my code. Really thankful for all help!

  string NyString;
    string itemString;

    private void cmdHämtaLista_Click(object sender, EventArgs e)
    {
        ListBoxOne.Items.Clear();
        SqlConnection cm = new SqlConnection(@"Data Source = (localdb)\MSSQLLocalDB; 
            Initial Catalog = NORTHWND; Integrated Security = True; Connect Timeout = 30; 
            Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; 
            MultiSubnetFailover = False");
        cm.Open();
        SqlCommand cmd = cm.CreateCommand();
        cmd.CommandText = "SELECT TOP(1000)" +
        "[CategoryName]" +
        "FROM[NORTHWND].[dbo].[Categories]";
        //"order by[CategoryName]Desc";    tog bort sortering för kategorierna läggs in 
        //i listan efter bokstavsordning ist för id som de är sorterade på i sql

        SqlDataReader rd = cmd.ExecuteReader();

        while (rd.Read())
        {

            ListBoxOne.Items.Add(rd.GetString(0));

        }

        cm.Close();
        rd.Close();
    }

    private void ListBoxOne_SelectedIndexChanged(object sender, EventArgs e)
    {
        ListBoxTwo.Items.Clear();
        var sitem = (ListBoxOne.SelectedIndex) + 1;

        SqlConnection cm = new SqlConnection(@"Data Source = (localdb)\MSSQLLocalDB; 
            Initial Catalog = NORTHWND; Integrated Security = True; Connect Timeout = 30; 
            Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; 
            MultiSubnetFailover = False");
        cm.Open();
        SqlCommand cmd = cm.CreateCommand();
        cmd.CommandText = "SELECT TOP(1000)" +
        "[ProductName]" +
        "FROM[NORTHWND].[dbo].[Products]" +
        "WHERE [CategoryID]=" + sitem.ToString();
        //"ORDER BY[ProductName]Desc"; tog bort sortering för kategorierna läggs in i listan 
        //efter bokstavsordning ist för id som de är sorterade på i sql

        SqlDataReader rd = cmd.ExecuteReader();

        while (rd.Read())
        {

            ListBoxTwo.Items.Add(rd.GetString(0));

        }

        cm.Close();
        rd.Close();

        MessageBox.Show("Closed.");
    }

    private void ListBoxTwo_SelectedIndexChanged(object sender, EventArgs e)
    {

        itemString = ListBoxTwo.SelectedItem.ToString();
        SqlConnection cm = new SqlConnection(@"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = NORTHWND; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; MultiSubnetFailover = False");
        cm.Open();
        SqlCommand cmd = cm.CreateCommand();
        cmd.CommandText = "SELECT TOP(1000) [UnitPrice]" +
        "FROM[NORTHWND].[dbo].[Products]" +
        "WHERE [ProductName]=@ProductName";
        cmd.Parameters.AddWithValue("@ProductName", itemString.ToString());
        //"ORDER BY[ProductName]Desc";    tog bort sortering för kategorierna läggs in i listan efter bokstavsordning ist för id som de är sorterade på i sql

        SqlDataReader rd = cmd.ExecuteReader();


        while (rd.Read())
        {

            txtPris.Text = Math.Round(rd.GetDecimal(0), 2).ToString();

        }



        cm.Close();
        rd.Close();
        txtNamn.Text = ListBoxTwo.SelectedItem.ToString();

        txtNamn.Text = itemString;

    }

    private void cmdSave_Click(object sender, EventArgs e)
    {
        NyString = txtNamn.Text;
        itemString = ListBoxTwo.SelectedItem.ToString();

        SqlConnection cm = new SqlConnection(@"Data Source = (localdb)\MSSQLLocalDB; 
            Initial Catalog = NORTHWND; Integrated Security = True; Connect Timeout = 30; 
            Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; 
            MultiSubnetFailover = False");
        cm.Open();
        SqlCommand cmd = cm.CreateCommand();

        cmd.CommandText = "Update Products" +
        "Set [ProductName] =@NewName" +
         cmd.Parameters.AddWithValue("@NewName", NyString.ToString()) +
        //"FROM[NORTHWND].[dbo].[Products]" +
        "WHERE [ProductName]=@ProductName" +
        cmd.Parameters.AddWithValue("@ProductName", itemString.ToString());



        cmd.ExecuteNonQuery();

        cm.Close();



    }
}

Do you have to concatinate these `cmd.Parameters.AddWithValue("@NewName", NyString.ToString())` into your update string? Shouldn't you just pass them after you build your string? — Jens, Oct 19 '16 at 09:44
Post the exception please. And tell us which Database your are using Oracle, MySql etc. — Sebi, Oct 19 '16 at 09:44
Including the issues that have been addressed, I recommend you use `using` when opening connections to a database, shown here: http://stackoverflow.com/questions/40117636/what-is-best-approach-for-opening-closing-sqlconnection-in-c-sharp/40117694#40117694 — FakeCaleb, Oct 19 '16 at 09:48

score 2 · Accepted Answer · answered Oct 19 '16 at 09:44

What you did here : Are concatenating parameter to Command Text? Wrong way of doing this

cmd.CommandText = "Update Products" +
"Set [ProductName] =@NewName" +
cmd.Parameters.AddWithValue("@NewName", NyString.ToString()) +
//"FROM[NORTHWND].[dbo].[Products]" +
"WHERE [ProductName]=@ProductName" +
cmd.Parameters.AddWithValue("@ProductName", itemString.ToString());

Try to replace above code with below one

cmd.CommandText = "Update Products Set [ProductName] =@NewName WHERE [ProductName]=@ProductName";
cmd.Parameters.AddWithValue("@NewName", NyString.ToString())
cmd.Parameters.AddWithValue("@ProductName", itemString.ToString());

How to update SQL Database

1 Answers1