Hello,

Firstly, i am not at my computer atm so i will not be able to show you my code (i will post it later).

I am creating a program for my dad which is sort of like a stock control program.

I have a form with two comboboxes and a listview. The first combobox binds to a SQL table called catagories which has two columns, CategoryID and CategoryDesc (Ex. 1,Tshirts). I can get these to populate correctly into the combobox, ValueMember = CategoryID & DisplayMamber = CategoryDesc. With this the selectedValue = CategoryID when the user selects it.

Now my issue is the second combobox. What i need to do is list in this combobx the products that match the categoryID (foreign key to another table called products). I have a WHERE query setup (basically, Find all products WHERE combobox1.selectedvalue = @categoryID.

Sounds simple enough and i thought it was, but for some strange reason i can get the displayMember to show the product description but the valuemember is null, therefore when selecting a product to populate the listview i get an SQL exception stating that @CategoryID = no data to read. This is due to the fact that the combobox2.selectedvalue = null.

I know this seems all over the place so i am willing to write a step by step process with the code (when i get home) if requested, as i am only after the help, if the questions no good the answer won't be any better ;)

Thanks in advance for the time spent on my query

Without seeing code its hard to say with any certainty. I had a similar issue in the past and if i remember correctly i fixed it by resetting the ValueMember before i accessed SelectedValue:

combobox1.ValueMember = "CategoryID";
string selectedID = combobox1.SelectedValue;

It was a while back and i cant remember the ins and outs, may be a different problem, but give it a try :)
Other than that, post your code and we'll see if theres anything wrong there.

Check that u r writing the code in combobox.selectedindexchanged.
If u r,it should work,unless u have not entered the table name correctly.
check for spaces at the beginning or end of the table name, for example, if your table name is table1, write it as "table1" and not as " table1 " when entering it for the value and/or display member.
Hope I helped.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using StockControl;
using allCentralClass;
using allSQLConnectionStrings;
using System.Data.SqlClient;

namespace StockControl
{
    public partial class Page2 : Form
    {
        public Page2()
        {
            InitializeComponent();

        }
        private void Page2_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'stockDataSet.tblCategory' table. You can move, or remove it, as needed.
            this.tblCategoryTableAdapter.Fill(this.stockDataSet.tblCategory);
            lstProducts.Visible = false;
            lstProducts.Items.Clear();
            lblProductTitle.Visible = false;
            ddlProduct.Visible = false;
        }
        private void btnBack_Click(object sender, EventArgs e)
        {
            this.Hide();
            initaliseForms objForm = new initaliseForms();
            objForm.p1.Show();
        }
        private void btnAddNewCategory_Click(object sender, EventArgs e)
        {
            this.Hide();
            initaliseForms objForm = new initaliseForms();
            objForm.p3.Show();

        }
        private void cboSelectStockCat_SelectedIndexChanged(object sender, EventArgs e)
        {
            sqlConnectionToCategory objSQLCon = new sqlConnectionToCategory();
            try
            {
                objSQLCon.mySqlConn.Open();
                objSQLCon.mySqlCmd = new SqlCommand("EXEC getAllProductsForCategory " + cboSelectStockCat.SelectedValue, objSQLCon.mySqlConn);
                objSQLCon.mySqlRdr = objSQLCon.mySqlCmd.ExecuteReader();
                while (objSQLCon.mySqlRdr.Read())
                {
                    ddlProduct.Items.Add(objSQLCon.mySqlRdr.GetValue(1).ToString());
                    //ADD VALUES INTO DDLPRODUCTS SO SELECTED VALUE = PRODUCTID FROM QUERY
                }
                objSQLCon.mySqlRdr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception: " + ex.ToString());
            }
            finally
            {
                objSQLCon.mySqlConn.Close();
                lstProducts.Visible = true;
                lblProductTitle.Visible = true;
                ddlProduct.Visible = true;
            }
        }
        private void ddlProduct_SelectedIndexChanged(object sender, EventArgs e)
        {
            sqlConnectionToCategory objSQLCon = new sqlConnectionToCategory();
            lstProducts.Items.Clear();
            try
            {
                objSQLCon.mySqlConn.Open();
                objSQLCon.mySqlCmd = new SqlCommand("EXEC GetProductsForCategory " + ddlProduct.SelectedValue ,objSQLCon.mySqlConn);
                objSQLCon.mySqlRdr = objSQLCon.mySqlCmd.ExecuteReader();
                lstProducts.Columns.Add("", 1);
                for (int i = 0; i< objSQLCon.mySqlRdr.FieldCount; i++)
                {
                    ColumnHeader ch = new ColumnHeader();
                    ch.Text = objSQLCon.mySqlRdr.GetName(i);
                    ch.Width = 150;
                        lstProducts.Columns.Add(ch);
                }
                ListViewItem lvItem;
                while (objSQLCon.mySqlRdr.Read()) 
                {
                    lvItem = new ListViewItem();
                    lvItem.SubItems.Add(objSQLCon.mySqlRdr.GetValue(0).ToString());
                    for (int i = 1; i<objSQLCon.mySqlRdr.FieldCount; i++) 
                    {
                        lvItem.SubItems.Add(objSQLCon.mySqlRdr.GetValue(i).ToString());
                    }
                    lstProducts.Items.Add(lvItem);
                }
                objSQLCon.mySqlRdr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception: " + ex.ToString());
            }
            finally
            {
                objSQLCon.mySqlConn.Close();
                lstProducts.Visible = true;
                lblProductTitle.Visible = true;
            }
        }
    }
}

The issue i have is in the commented out part.

The SQL Queries are in a central class, if that is required i can attach it as well.

Edited 6 Years Ago by __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

Sorry i wrote it wrong.

objSQLCon.mySqlCmd = new SqlCommand("EXEC GetProductsForCategory " + ddlProduct.SelectedValue ,objSQLCon.mySqlConn);

when the program is running the above code ddlProduct.SelectedValue = null.

The reason why the first combobox works correctly is because i have binded the combobox to the category table, so the DisplayMember = CategoryName and the ValueMember = CategoryID.

The second combobox (ddlProduct) is not databinded because i do not use a single table, i use a table called Product_Details (which has CategoryID, ProductID, TypeID, ProductPrice, ProdctStock columns) to obtain the ProductID i use the Products table to obtain the Product_Description column (where the CategoryID = @CategoryID, use foreign key productID to match products table ProductID). so my query does work when i pass the parameter but when i input the values into the combobox, i get the product description to appear in the ddlProducts.

Sounds good so far, but the issue is that i need to use the selected value for ddlproducts to have a value as this is used for another query to use a listview of all the product type stocks and cost.

If i bind the combobox i can get the productDescription and ProductID to be in the DisplayMember and ValueMember respectively but this does not work as this then lists all products regardless of category selected. (certain categories should list certain products)

Hopefully this makes sense.

Thanks for the help, i have tried the requested but to no avail.

Check that u r writing the code in combobox.selectedindexchanged.
If u r,it should work,unless u have not entered the table name correctly.
check for spaces at the beginning or end of the table name, for example, if your table name is table1, write it as "table1" and not as " table1 " when entering it for the value and/or display member.
Hope I helped.

I don't understand what you mean, as i do not actually write the table data into code, i have been told that coding tables is bad for security reasons and i should keep to stored procedures to not leak information incase the program gets hacked the hacker would know my tables and change data to suit them. Not sure if this is true??

I don't understand what you mean, as i do not actually write the table data into code, i have been told that coding tables is bad for security reasons and i should keep to stored procedures to not leak information incase the program gets hacked the hacker would know my tables and change data to suit them. Not sure if this is true??

yes it is true,but that is applicable mostly for server based programs. I'm sure you are doing a windows based coding in which case the only way for the code to leak out is for you to give it off too someone.
any ways, I hope your query has been solved. :)
If it is solved, please mark the thread as solved, else your query will keep getting the unsolved focus.
And I also did not realize you were using stored procedures, it is a very fast way to deal with codes rather than use datasets and adapters.
My solution was not for stored procedures, sorry, could not be of help. :$

yes it is true,but that is applicable mostly for server based programs. I'm sure you are doing a windows based coding in which case the only way for the code to leak out is for you to give it off too someone.
any ways, I hope your query has been solved. :)
If it is solved, please mark the thread as solved, else your query will keep getting the unsolved focus.
And I also did not realize you were using stored procedures, it is a very fast way to deal with codes rather than use datasets and adapters.
My solution was not for stored procedures, sorry, could not be of help. :$

Hello, my issue is not solved, all i have done is try to explain my issue. At least i know that i can use table coding instead of trying to hide it all (as it is a windows based application). Looking at my code, when i add the information to the combobox i do a command

ddlproducts.items.add(objSQL.mysqlrdr.getvalue(1).tostring());

this has two values, value(0) is the productID and value (1) is the productDescription, so how do i add value(0) to be the valuemember. I have tried the obvious method such as;

ddlproducts.valueMember = (objSQL.mysqlrdr.getvalue(0).tostring());

which does not work.

Any help will be great

Edited 6 Years Ago by Ap0ca1ypse: n/a

Have a look at this sample that uses a custom datasource,

MyListItem.cs

public class MyListItem
{
    string _text;
    string _value;

    public MyListItem() { _text = string.Empty; _value = string.Empty; }
    public MyListItem(string text, string value)
    {
        _text = text;
        _value = value;
    }
    public string Text
    {
        get
        {
            return _text;
        }
        set
        {
            _text = value;
        }
    }
    public string Value
    {
        get { return _value; }
        set { _value = value; }
    }
    public override string ToString()
    {
        return _value;
    }
}

Form - Test1

...
 public partial class Test1 : Form
    {
        public Test1()
        {
            InitializeComponent();
        }

        private void Test1_Load(object sender, EventArgs e)
        {
            List<MyListItem> item = new List<MyListItem>();
            item.Add(new MyListItem("A", "1"));
            item.Add(new MyListItem("B", "2"));
            item.Add(new MyListItem("C", "3"));

            comboBox1.DataSource = item;
            comboBox1.DisplayMember = "Text";
            comboBox1.ValueMember = "Value";
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
          label1.Text = comboBox1.SelectedValue.ToString();
        }
    }
 ...

Have a look at this sample that uses a custom datasource,

MyListItem.cs

public class MyListItem
{
    string _text;
    string _value;

    public MyListItem() { _text = string.Empty; _value = string.Empty; }
    public MyListItem(string text, string value)
    {
        _text = text;
        _value = value;
    }
    public string Text
    {
        get
        {
            return _text;
        }
        set
        {
            _text = value;
        }
    }
    public string Value
    {
        get { return _value; }
        set { _value = value; }
    }
    public override string ToString()
    {
        return _value;
    }
}

Form - Test1

...
 public partial class Test1 : Form
    {
        public Test1()
        {
            InitializeComponent();
        }

        private void Test1_Load(object sender, EventArgs e)
        {
            List<MyListItem> item = new List<MyListItem>();
            item.Add(new MyListItem("A", "1"));
            item.Add(new MyListItem("B", "2"));
            item.Add(new MyListItem("C", "3"));

            comboBox1.DataSource = item;
            comboBox1.DisplayMember = "Text";
            comboBox1.ValueMember = "Value";
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
          label1.Text = comboBox1.SelectedValue.ToString();
        }
    }
 ...

Hello,

this looks very useful.

i am guessing that what i will need to do is change

item.Add(new MyListItem("A", "1"));            
item.Add(new MyListItem("B", "2"));            
item.Add(new MyListItem("C", "3"));

to my loop method, something like this

item.add(new MyListItem(objSQLcon.mysqlrdr.getvalue(1).tostring()),objSQLcon.mysqlrdr.getvalue(0).tostring()));
//getvalue(0) = ProductID and getvalue(1) = productDescription

i will give this a try and get back to you.

Thanks for your help

Hello,

it is working perfectly, man you certainly know your stuff. all i changed from your code was the static lists as seen in the above post.

Thanks so much, i have been messing with this little issue for the last week now, its such a relief to finally crack on with the project.

This question has already been answered. Start a new discussion instead.