Using combobox selection to search dataset

[Firblog]

Hi Folks, I'm working on a small application that interacts with a sql database. I've managed to pull a list of customers (Company names) from the Customer table and populate a combobox. Now I'm trying to populate some textboxs or labels with the company address from the same database based on the user selection in the combox box. I've been searching the net for the last week or so and haven't been able to get any of the code that I thought would do it to work.
So i need help....... which will be greatly appreciated.

Cheers

[Inanna]

Sorry, I don't want to give you broken code, and I don't have VB.NET Express on this machine yet, so the code is in C#. The easiest way is to play the binding game.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace InannaRocks {
    public class MyProgram {
        public static void Main()
        {
            Application.Run(new MyForm());
        }
    }

    class MyForm: Form {
        private FlowLayoutPanel _container = new FlowLayoutPanel();
        private TextBox _address = new TextBox();
        private ComboBox _picker = new ComboBox();

        public MyForm()
        {
            SuspendLayout();

            AutoSize = true;
            AutoSizeMode = AutoSizeMode.GrowAndShrink;
            _container.AutoSize = true;
            _picker.Parent = _container;
            _picker.SelectedIndexChanged += pickerOnSelect;
            _address.Parent = _container;
            _address.AutoSize = true;
            Controls.Add(_container);
            Load += mainFormLoader;

            ResumeLayout();
        }

        private void mainFormLoader(object sender, EventArgs e)
        {
            string connectionString = @"Server=.\SQLExpress;Database=Test;Trusted_Connection=Yes;";
            string queryString = "select name, address from MyTable";

            using (SqlConnection connection = new SqlConnection(connectionString)) {
                using (SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection)) {
                    DataTable table = new DataTable();

                    connection.Open();
                    adapter.Fill(table);
                    _picker.DataSource = table;
                    _picker.DisplayMember = "name";
                    _picker.ValueMember = "address";
                    pickerOnSelect(_picker, null);
                }
            }
        }

        private void pickerOnSelect(object sender, EventArgs e)
        {
            _address.Text = _picker.SelectedValue.ToString();
        }
    }
}

Or if you want to do it manually:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace InannaRocks {
    public class MyProgram {
        public static void Main()
        {
            Application.Run(new MyForm());
        }
    }

    class MyForm: Form {
        private readonly string connectionString =
            @"Server=.\SQLExpress;Database=Test;Trusted_Connection=Yes;";

        private FlowLayoutPanel _container = new FlowLayoutPanel();
        private TextBox _address = new TextBox();
        private ComboBox _picker = new ComboBox();

        public MyForm()
        {
            SuspendLayout();

            AutoSize = true;
            AutoSizeMode = AutoSizeMode.GrowAndShrink;
            _container.AutoSize = true;
            _picker.Parent = _container;
            _picker.SelectedIndexChanged += pickerOnSelect;
            _address.Parent = _container;
            _address.AutoSize = true;
            Controls.Add(_container);
            Load += mainFormLoader;

            ResumeLayout();
        }

        private void mainFormLoader(object sender, EventArgs e)
        {
            string queryString = "select name from MyTable";

            using (SqlConnection connection = new SqlConnection(connectionString)) {
                using (SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection)) {
                    DataTable table = new DataTable();

                    connection.Open();
                    adapter.Fill(table);

                    foreach (DataRow dbRow in table.Rows)
                        _picker.Items.Add(dbRow["name"].ToString());

                    _picker.SelectedIndex = 0;
                }
            }
        }

        private void pickerOnSelect(object sender, EventArgs e)
        {
            string queryString =
                "select address from MyTable where name = \'" +
                _picker.Text + "\'";

            using (SqlConnection connection = new SqlConnection(connectionString)) {
                using (SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection)) {
                    DataTable table = new DataTable();

                    connection.Open();
                    adapter.Fill(table);
                    _address.ResetText();

                    foreach (DataRow dbRow in table.Rows)
                        _address.AppendText(dbRow["address"].ToString() + "; ");
                }
            }
        }
    }
}

[Firblog]

Hi Inanna, thanks for your reply.
I can see what you are doing (though some of the syntax throws me a bit), perhaps it will help if i tell you exactly where I am at the minute. I've filled the combo box with the names of the companies, I have pulled the users selection to a string (cbContents). Now I wish to search the customer table and pull all info for this customer from this (essentially a single record from the table) and assign it's values to the text boxes (this is probably a very convoluted way of accomplishing this), my problem now is in a nutshell i don't know how to pass the contents of the variable to the query
"SELECT * FROM Customer WHERE Company_Name = &cbContents"
was my latest attempt that failed :-( could you give me the syntax I need to accomplish this?

[Inanna]

my problem now is in a nutshell i don't know how to pass the contents of the variable to the query

kk, in VB.NET it would look somethin' like this.

"select * from Customer where Company_Name = " & cbContents

You were close, but I'm pretty sure VB.NET doesn't support string interpolation. :)

[Firblog]

Hi Inanna, wasn't able to get your suggestion to work so have done a bit more searching about and came up with the following
http://searchvb.techtarget.com/tip/1,289483,sid8_gci528949,00.html?FromTaxonomy=%2Fpr%2F4f0 it seems that when passing values to sql they have to be encased in (') appostrophes..... this little bit of code reads in the value of my combobox an deals with any stray 's ie (Sam's Place) in the company name.
"SELECT * FROM Customer WHERE Company_Name = '" & Replace$(cb_CompanyN.Text, "'", "''") & "'" unfortunately there are so many 's and "s in there it's difficult to see how a value should be passed. On the bright side i've the form filling out now..........;)

Cheers and thanks for your help

[Inanna]

it seems that when passing values to sql they have to be encased in (') appostrophes

Ya, that was my fault. I should have asked you what the contents of cbContents was instead of assuming that it was an acceptable SQL string. :o

[Firblog]

No worries........... seem to have gotten there in the end

Cheers

[Firblog]

Sorry Inanna, just a little ps, how would you pass the contents of the varialbe if it was a string. (May come in handy some other time)

[Inanna]

how would you pass the contents of the varialbe if it was a string

The contents of what variable? If you mean program defined values like cbContents, my preference is to use parameters.

Dim commandString As String = "select * from Customer where Company_Name = @Company"

...

AddParameter(command, "@Company", SqlDbType.VarChar, 255, cbContents)

That way you don't have to worry about SQL injection or tricky formatting like the single quotes you were dealing with before. Here's the AddParameter function I used, since it's not a system function.

Private Function AddParameter( _
    ByVal command As SqlCommand, _
    ByVal paramID As String, _
    ByVal dataType As SqlDbType, _
    ByVal paramSize As Integer, _
    ByVal value As Object) _
As SqlParameter
    If paramID = String.Empty Then
        Throw New ArgumentOutOfRangeException("paramID")
    End If

    Dim newParam As SqlParameter = New SqlParameter()

    newParam.ParameterName = paramID
    newParam.SqlDbType = dataType

    If paramSize > 0 Then
        newParam.Size = paramSize
    End If

    newParam.Value = value
    command.Parameters.Add(newParam)

    Return newParam
End Function