How to read SQL database specific row text from another row value

Question

I am trying to query data of a specific column text from another column.

Basically, I have a Supplier Database that has a SupplierID and Country column.

I already have the SupplierID for that specific row for example, it is 14. I would like to get the text value of Country column based on the 14 value.

The Supplier ID I am getting by the following code (listbox):

<asp:ListBox ID="SupplierListBox" runat="server" 
            DataSourceID="SupplierCompanyDataSource" DataTextField="Company" 
            DataValueField="SupplierID" Width="315px" 
            Height="80px" 
            onselectedindexchanged="SupplierListBox_SelectedIndexChanged" 
            AutoPostBack="True"></asp:ListBox>

Code:

        string SupplierListvalue = SupplierListBox.SelectedItem.Value; //SupplierListvalue retrieves the SupplierID value

        SqlDataReader rdr = null;
        SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("select Country from SupplierDB", conn);
        cmd.Connection = conn;

        conn.Open();
        rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            TextBox1.Text = rdr["Country"].ToString();
            MessageBox.Show("Connection Successful");
            MessageBox.Show(rdr.ToString());
        }

        conn.Close();

Have you tried "SELECT country FROM myTable WHERE SupplierID=14"? — codingbiz
– codingbiz, Commented Jan 26, 2013 at 22:47
You're trying to query data? What rdbms are you using, how do you want to query it(ADO.NET,Entity-Framework,Linq-To-Sql,.....) and finally, what have you already tried? — Tim Schmelter
– Tim Schmelter, Commented Jan 26, 2013 at 22:48
@TimSchmelter Your method was correct. I had to use the following command: SelectCommand="SELECT [Company], [SupplierID] FROM [SupplierDB] WHERE ([SupplierID] >= @SupplierID)"> <SelectParameters><asp:Parameter DefaultValue="14" Name="SupplierID" Type="Int32" /> //defaultvalue="14" is the starting value in the database SelectCommand="SELECT [SupplierID], [Country] FROM [SupplierDB] WHERE ([SupplierID] = @SupplierID)"> <asp:ControlParameter ControlID="SupplierListBox" Name="SupplierID" PropertyName="SelectedValue" Type="Int32" /> — Soulblade
– Soulblade, Commented Jan 27, 2013 at 21:33

Tim Schmelter · Accepted Answer · 2013-01-26 23:03:35Z

2

Well, it's not clear what's the main problem, so i'll show you a working example which selects the Country column from database with ADO.NET, uses Parameters to avoid SQL-Injection and using-statement to ensure that all unmanaged resources as the connection get disposed(closed).

string sql = @"
            SELECT Country
            FROM dbo.Supplier
            WHERE SupplierID = @SupplierId";
using (var con = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True"))
{
    using (var cmd = new SqlCommand(sql, con))
    {
        con.Open();
        cmd.Parameters.Add("@SupplierId", SqlDbType.Int);
        cmd.Parameters["@SupplierId"].Value = int.Parse(SupplierListBox.SelectedItem.Value);
        using (var rdr = cmd.ExecuteReader())
        {
            if (rdr.Read())
            {
                TextBox1.Text = rdr.GetString(0);
            }
        }
    }
}

edited Jan 26, 2013 at 23:03

answered Jan 26, 2013 at 22:58

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sgeddes · Accepted Answer · 2013-01-26 22:59:47Z

0

You need to look into using parameterized queries. Try this out:

    string SupplierListvalue = SupplierListBox.SelectedItem.Value; //SupplierListvalue retrieves the SupplierID value
    ...
    SqlCommand cmd = new SqlCommand("select Country from SupplierDB WHERE SupplierID = @supplierId", conn);
    cmd.Parameters.AddWithValue("@supplierId", SupplierListvalue);
    ...

Good luck.

answered Jan 26, 2013 at 22:59

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

1 Comment

Soulblade Over a year ago

string SupplierListvalue = SupplierListBox.SelectedItem.Value;             MessageBox.Show(SupplierListvalue.ToString());                         SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True");             SqlCommand cmd = new SqlCommand("SELECT [Country] FROM [SupplierDB] WHERE [SupplierID] = @SupplierID", conn);             cmd.Parameters.AddWithValue("@SupplierID", SupplierListvalue.ToString());

Still not able to get the Country text based on Supplierlistvalue. Is there something I am missing?

Soulblade · Accepted Answer · 2013-01-27 21:41:24Z

I was approaching it in the wrong way. Basically the following was the correct code:

<asp:SqlDataSource ID="SupplierCompanyDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ROGConnectionString %>" SelectCommand="SELECT [Company], [SupplierID] FROM [SupplierDB] WHERE ([SupplierID] >= @SupplierID)"> <SelectParameters> <asp:Parameter DefaultValue="14" Name="SupplierID" Type="Int32" /></SelectParameters> </asp:SqlDataSource>

<asp:SqlDataSource ID="SupplierCountryDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ROGConnectionString %>" SelectCommand="SELECT [SupplierID], [Country] FROM [SupplierDB] WHERE ([SupplierID] = @SupplierID)"> <SelectParameters> <asp:ControlParameter ControlID="SupplierListBox" Name="SupplierID" PropertyName="SelectedValue" Type="Int32" /></SelectParameters></asp:SqlDataSource>

This does the job! So now when I click on the listbox1 the listbox2 also gets clicked similar to cascading.

Collectives™ on Stack Overflow

How to read SQL database specific row text from another row value

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related