C#: how can I delete from databound combobox (SQL, dataset)?

Question

0.00/5 (No votes)

See more:

What I'm trying to achieve:

To delete a row from SQL table1 where column1 = @myParameter.
Where @myParameter holds the value of my current comboBox1 selection.
My ComboBox populates fine, and is bound to a DataSet.
This is my code:

C#

using (SqlConnection sqlCon = new SqlConnection(conString))
using (SqlCommand sqlCmd = new SqlCommand(delString, SqlCon)) {

    sqlCon.Open();
    sqlCmd.Parameters.AddWithValue("@myParameter", SqlDbType.NVarChar).Value = 
    comboBox1.SelectedItem.ToString();
    sqlCmd.ExecuteNonQuery();

    
}

When I run the code, the database does not update.

A separate issue is my code to remove the selecteditem from the ComboBox itself.
I use:

C#

var i = comboBox1
comboBox1.DataSource = null;
comboBox1.Items.Remove(i);

What should I write in order to re-bind it to the DataSet?

This is how I populate my ComboBox:

DataSet

C#

DataSet MyDataSet() {

    DataSet ds = new DataSet();

    using (SqlConnection sqlCon = new SqlConnection(connString))
    using (SqlDataAdapter sqlDaAd = new SqlDataAdapter("myStoredProcedure", sqlCon)) {

        sqlDaAd.SelectCommand.CommandType = CommandType.StoredProcedure;
        sqlDaAd.SelectCommand.Parameters.Add("@myParameter", SqlDbType.NVarChar).Value = 
          lblLøyvehaver.Text.ToString();
        sqlDaAd.Fill(ds);
    }

    return ds;
}

Method

C#

DataSet ds = MyDataSet();
    comboBox1.DataSource = ds.Tables[0];
    comboBox1.DisplayMember = "column3";
    comboBox1.ValueMember = "column2";

Thanks alot for any advice you might be able to give me!

What I have tried:

Spent close to a full day trying different things.

Posted 18-May-21 0:53am

Flidrip

Updated 18-May-21 3:23am

v2

Add a Solution

Comments

CHill60 18-May-21 8:57am

Re your first problem "When I run the code, the database does not update." - what is in delString?

2 solutions

Solution 1

C

var i = comboBox1
comboBox1.DataSource = null;
comboBox1.Items.Remove(i);

What on earth did you expect this code to actually do?
Remove an item from itself? You do understand what a combo box list is, don't you?

You don't change the ComboBox: you change the DataTable it is bound to:

C#

private DataTable items = new DataTable();
private void FrmMain_Shown(object sender, EventArgs e)
    {
    items.Columns.Add("URL");
    items.Columns.Add("Name");
    items.Rows.Add("https://www.codeproject.com/", "CodeProject");
    items.Rows.Add("https://www.codeproject.com/Lounge.aspx", "The Lounge");
    items.Rows.Add("https://www.codeproject.com/script/Answers/List.aspx?tab=unanswered", "Unanswered Questions");
    BindingSource bs = new BindingSource();
    bs.DataSource = items;
    myComboBox.DataSource = items;
    myComboBox.DisplayMember = "Name";
    myComboBox.ValueMember = "URL";
    }
private void butAdd_Click(object sender, EventArgs e)
    {
    items.Rows.Add("A new URL", "A new name");
    }
private void butRemove_Click(object sender, EventArgs e)
    {
    items.Rows.RemoveAt(0);
    }

Posted 18-May-21 2:40am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Flidrip · Accepted Answer · 2021-05-18T03:23:00

Thank you both for your inputs. As for why I couldn't delete any rows, I figured out what the problem was.

My code that gave value to my parameter for the DELETE statement, returned:

"System.Data.DataRowView"

E.g. it returned the object, and not the value as string.

So I changed my code to this:

C#

DataRowView oDataRowView = comboBox1.SelectedItem as DataRowView;
string sValue = string.Empty;

if (oDataRowView != null) {

    sValue = oDataRowView.Row["column1"] as string;
    sqlCmd.Parameters.Add("@myParameter", SqlDbType.NVarChar).Value = sValue = oDataRowView.Row["column1"] as string;
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.ExecuteNonQuery();

I still have to tackle the issue OriginalGriff gave me an answer to, but at least this (the main issue) got resolved.

I have a sneaking suspicion that what I pasted above is not as efficient as it could be, although it works.
If anyone sees any bloats, feel free to tell me.