Sql/C#: populating combobox with table1, based on which table2 row is selected

Question

0.00/5 (No votes)

See more:

Hello!

My question is this: How can I sort the ComboBox data (column from table1) based on which row (row in table2) I currently have selected.
I double click a row in a datagridview in the parent form. This then opens a child form specific to that row (just a way to edit the row info, basically). On this child form, I have a button to open a second child form, in which I have a ComboBox.

I am trying to fill this ComboBox with data from table1 in SQL, which works perfectly fine. BUT, I also want to sort the data based on which row I originally clicked in the parent forms datagridview. I have a label with the ID name of that row brought with me into the child-child form. So that's all fine.

But I still don't know how to sort the data in the ComboBox based on the ID of the child-child form I'm in.

Ok - I am confusing my self now.

I have a stored procedure which goes like this:

SELECT [ID] as ID
,[LøyvehaverID] as LøyvehaverID
,[KjøretøyID] as KjøretøyID
FROM [Løyve]

Can I add a WHERE command here to solve my issue?

Here is my code that I use to populate the ComboBox:

********************* My DataSet **********************

C#

DataSet GetLøyveDataFromDB() {

    try {

        DataSet ds = new DataSet();

        using (SqlConnection sqlCon = new SqlConnection(ConnectionString.connectionString))
        using (SqlDataAdapter sqlDaAd = new SqlDataAdapter("hentLøyver", sqlCon)) {

            sqlDaAd.SelectCommand.CommandType = CommandType.StoredProcedure;
            sqlDaAd.Fill(ds); 
        }

        return ds;

     } catch (Exception) {

                throw;
            }
        }

********************* On form load **********************

C#

    try {

        DataSet dsDataFromDB = GetLøyveDataFromDB();
        cbLøyve.DataSource = dsDataFromDB.Tables[0];
        cbLøyve.DisplayMember = "KjøretøyID";
        cbLøyve.ValueMember = "IDL";
        cbLøyve.SelectedItem = null;

    } catch (Exception ex) {

     MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

What I have tried:

I have tried many different variations with the stored procedure.

Posted 14-May-21 0:46am

Flidrip

Updated 14-May-21 3:52am

Add a Solution

Comments

CHill60 14-May-21 6:59am

You don't want a WHERE clause to sort anything, you want an ORDER BY clause in the SQL. If you are going to pass it in as a variable you may need to create some dynamic SQL.
Although reading your question again, regarding ID of the row, then you can pass that ID into the stored procedure as @Parameter and use WHERE ID = @Parameter on the SQL

Flidrip 14-May-21 9:07am

Thanks!

SQL in general is very new to me, I'm not sure how I would do that.
How can I pass in a column from another table as a parameter?

Am I way off here?

ALTER PROCEDURE [dbo].[myStoredProcedure]
AS
@table2.ColumnName // <-- is this where i declare the parameter, and is that
the way to reference it?
BEGIN
SELECT * FROM table1
WHERE ID = @table2.ColumnName

(because that doesn't seem to work...)

Appreciate you taking the time!

Richard MacCutchan 14-May-21 7:01am

What about ORDER BY?

1 solution

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)

CHill60 · Answer 1 · 2021-05-14T03:52:00

As per our conversation in the comments:
What you seem to be trying to do is FILTER the results based on ID rather than SORT (which would need an ORDER BY clause[^]).

To filter set of results returned in SQL you need to use a WHERE Clause[^]. To make that dynamic you will need to pass a parameter into your Stored Procedure, probably something like this..(NB untested)

SQL

ALTER PROCEDURE [dbo].[myStoredProcedure] @Foretaksnavn int 
AS
SELECT [ID] as ID
,[LøyvehaverID]
,[KjøretøyID]
FROM [Løyve]
WHERE [ID] = @parameter

There is a tutorial here at MSSqlTips.com[^] .
Couple of things to note here
- It is best to avoid "special" characters in table and column names e.g. can ø become o or oe instead
- You don't need the as LøyvehaverID because the column is already called LøyvehaverID. You only need the AS if you want to rename the column or if you have a calculation result that needs a name.
You then pass the parameter into the SP from C# using a Command Parameter e.g.

C#

sqlDaAd.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@Foretaksnavn", SqlDbType.Int).Value = id;

Where id is the id you captured from parent forms datagridview. There is more information on using Stored Procedures in .NET here - Working with SQL Server Stored Procedures and .NET[^]

When it comes to passing information between forms, I recommend the series of tips by OriginalGriff here on CodeProject. Start here - Transferring information between two forms, Part 1: Parent to Child[^]