SQL search to Label

Question

1.00/5 (1 vote)

See more:

Who will help me continue with the code at ">>>>????<<<<<" below...

In this project, I'm looking at the table "Klanten"

At "KlantenBOXfilteren()" I search my record in a listbox. After selecting the client (visualized by name "naam") I get back an ID.

At "KlantenContactBOX()" I use the ID field ( one more to manny relationship ) to search for all contacts from the customer

Now I would like to make a similar solution in "KlantenInfo()" I want to load the fields from the " customer table " in multiple labels.

Example fields in

Table filename Labelname
Naam = LB_KlantenNaam
Adres = LB_KlantenAdres
Telefoon = LB_KlantenTelefoon

What code do I need to write at ">>>>????<<<<<"

private void KlantenBOXfilteren()
        {
            string queryZoekOrganisatie = "Select * From Klanten WHERE Organisatie like @ZoekOrganisatie";

            using (connection = new SqlConnection(connectionstring))
            using (SqlCommand command = new SqlCommand(queryZoekOrganisatie, connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))


            {
                command.Parameters.AddWithValue("@ZoekOrganisatie", "%" + TX_KlantenZoeken.Text + "%");

                DataTable Klantenlijst = new DataTable();
                adapter.Fill(Klantenlijst);

                ListBoxKlanten.DisplayMember = "Organisatie";
                ListBoxKlanten.ValueMember = "ID Klanten";
                ListBoxKlanten.DataSource = Klantenlijst;
            }
        }

        //Klanten Contact personen
        private void KlantenContactBOX()
        {
            string queryKLantID = "select * From Klantencontact WHERE FK_ID_Klant = @SELKlantID";

            using (connection = new SqlConnection(connectionstring))
            using (SqlCommand command = new SqlCommand(queryKLantID, connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))

            {
                command.Parameters.AddWithValue("@SELKlantID", ListBoxKlanten.SelectedValue);

                DataTable KlantenContactlijst = new DataTable();
                adapter.Fill(KlantenContactlijst);

                listBoxKlantenContact.DisplayMember = "Naam";
                listBoxKlantenContact.ValueMember = "FK_ID_klant";
                listBoxKlantenContact.DataSource = KlantenContactlijst;
            }
        }

        //klant informatie inzien
        private void KlantenInfo()
        {
            string queryKLantinfoID = "select * From Klanten WHERE ID Klanten = @SELKlantinfoID";

            using (connection = new SqlConnection(connectionstring))
            using (SqlCommand command = new SqlCommand(queryKLantinfoID, connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                
            {
                command.Parameters.AddWithValue("@SELKlantinfoID", ListBoxKlanten.SelectedValue);

                >>>>????<<<<<
            }
        }

Posted 19-Nov-15 3:35am

MaikelO1

Add a Solution

Comments

John C Rayan 19-Nov-15 11:03am

Can you explain the following

Table filename Labelname
Naam = LB_KlantenNaam
Adres = LB_KlantenAdres
Telefoon = LB_KlantenTelefoon

The indentation is poor. Are they table strucuture? what you mean by multiple labels?

MaikelO1 19-Nov-15 11:15am

Hello John,
Thanks for your comment..

I have several fields in my database in the record that i look-up on the basis of the @SELKlantinfoID.

the field names in the database are "naam", "Adres" and "Telefoon"

I would like to load this information into three different labels called "LB_KlantenNaam", "LB_KlantenAdres" and "LB_KlantenTelefoon".

The idea is that when a customer name (klantnaam) is selected in the "listboxklanten" that the labels fill with te customer information of the selected record.

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)

Richard Deeming · Answer 1 · 2015-11-19T05:50:00

Try something like this:

C#

private void KlantenInfo()
{
    const string queryKLantinfoID = "SELECT TOP 1 naam, Adres, Telefoon FROM Klanten WHERE [ID Klanten] = @SELKlantinfoID";

    using (SqlConnection connection = new SqlConnection(connectionstring))
    using (SqlCommand command = new SqlCommand(queryKLantinfoID, connection))
    {
        command.Parameters.AddWithValue("@SELKlantinfoID", ListBoxKlanten.SelectedValue);
        
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
        {
            if (reader.Read())
            {
                LB_KlantenNaam.Text = Convert.ToString(reader["naam"]);
                LB_KlantenAdres.Text = Convert.ToString(reader["Adres"]);
                LB_KlantenTelefoon.Text = Convert.ToString(reader["Telefoon"]);
            }
            else
            {
                LB_KlantenNaam.Text = string.Empty;
                LB_KlantenAdres.Text = string.Empty;
                LB_KlantenTelefoon.Text = string.Empty;
            }
        }
    }
}

NB: You appear to be storing the SqlConnection in a field. That's a bad idea; instead, you should use a local variable in each method, as you have for the SqlCommand objects.

You should avoid using SELECT * FROM ...; only select the columns you're going to use.

I've added a TOP 1 clause to your query, in case the Klanten column isn't the primary key. If it is the primary key, you can omit this.

The SqlDataReader class has a GetString method which looks like it would work. Unfortunately, if the data returned from the database is NULL, this method throws a SqlNullValueException. You could use the IsDBNull method to test for this, but it's generally easier to use Convert.ToString on the raw value instead.