c# populating multiple combo box for update and delete question

Question

this combo box gets the job id from the database and assigns it to the jobidcombobox.

    private void filljobid()
    {
        SqlConnection con = new SqlConnection(strConn);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT job_id FROM job";
        DataSet ds = new DataSet();
        SqlDataAdapter dAdapter = new SqlDataAdapter();
        dAdapter.SelectCommand = cmd;
        con.Open();
        dAdapter.Fill(ds);
        con.Close();
        jobidcombobox.DisplayMember = "job_id";
        jobidcombobox.ValueMember = "job_id";
        jobidcombobox.DataSource = ds.Tables[0];
    }

And then this indexchange code takes the jobidcombobox value and uses it it to query to get the rest of the columns that relate to it.

    private void jobidcombobox_SelectedIndexChanged(object sender, EventArgs e)
    {
        string JobID = jobidcombobox.Text;


        SqlConnection con = new SqlConnection(strConn);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT * from job where job_id = '" + JobID + "' ";
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        da.Fill(ds);

        if (ds.Tables[0].Rows.Count > 0)
        {
            customeridcombobox.Text = ds.Tables[0].Rows[0]["customer_id"].ToString();
            depotidcombobox.Text = ds.Tables[0].Rows[0]["depot_id"].ToString();
            startlocationtextbox.Text = ds.Tables[0].Rows[0]["start_location"].ToString();
            endlocationtextbox.Text = ds.Tables[0].Rows[0]["end_location"].ToString();
            jobtypecombobox.Text = ds.Tables[0].Rows[0]["job_type"].ToString();
        }

        else
        {
            MessageBox.Show("Invalid job number");
        }
    }

As seen above the customerid is filled but only with a single value which relates to the jobid. I would like to add other customer id values in here from the database. I have tried to same function as jobid to get the customer id but i cant make it relate to the job id.

Is there any way to do this?

Answer 1

Try this...

Fill the job id and customer id boxes.

private void FillJobIdAndCustomerId()
{
    SqlConnection con = new SqlConnection(strConn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT job_id, customer_id FROM job";
    DataSet ds = new DataSet();
    SqlDataAdapter dAdapter = new SqlDataAdapter();
    dAdapter.SelectCommand = cmd;
    con.Open();
    dAdapter.Fill(ds);
    con.Close();
    var dataRows = ds.Tables[0].AsEnumerable();
    jobidcombobox.DisplayMember = "job_id";
    jobidcombobox.ValueMember = "job_id";
    jobidcombobox.DataSource = dataRows.Select(x=>x.job_id);
    customeridcombobox.DisplayMember = "customer_id";
    customeridcombobox.ValueMember = "customer_id";
    customeridcombobox.DataSource = dataRows.Select(x=>x.customer_id);
}

And then when the a job id is selected...

private void jobidcombobox_SelectedIndexChanged(object sender, EventArgs e)
{
    string JobID = jobidcombobox.Text;


    SqlConnection con = new SqlConnection(strConn);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT * from job where job_id = @jobId";
    commandObject.Parameters.AddWithValue("@jobId", JobID);
    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    da.Fill(ds);

    if (ds.Tables[0].Rows.Count > 0)
    {
        customeridcombobox.SelectedIndex = customeridcombobox.FindString(ds.Tables[0].Rows[0]["customer_id"].ToString());
        depotidcombobox.Text = ds.Tables[0].Rows[0]["depot_id"].ToString();
        startlocationtextbox.Text = ds.Tables[0].Rows[0]["start_location"].ToString();
        endlocationtextbox.Text = ds.Tables[0].Rows[0]["end_location"].ToString();
        jobtypecombobox.Text = ds.Tables[0].Rows[0]["job_type"].ToString();
    }

    else
    {
        MessageBox.Show("Invalid job number");
    }
}

Can follow similar pattern for other combo boxes you have (dept, jobtype from your example) if you would like to.

Note: You may have observed that I changed the query building slightly, using SqlParameters. The way it was written in your sample code is a classic case of SQL Injection.