Value different to item in combobox

[lional]

Hi All
I am trying to add a combobox control whose value that gets written to the database is different from the text shown in the combobox at runtime.
Both of these fields are drawn from a database table.

I know how to add the database item to the value but been battling with having a different value and displayed text.

I am verrry new to C#

Just point me in the right direction, else I necer learn

Thanks a mil.

 

[jmcilhinney]

I don't really know what you're talking about. The thing to do is to provide the code you currently have and describe exactly what you expect to happen and exactly what does happen.

 

[lional]

Hi
Thank you for your response
My code snippet is as follows:

                if (cboProvince.SelectedIndex > -1)
                {
                    string ctprov = "select ProvinceCode, Province from Provinces";
                    cmd = new SqlCommand(ctprov);
                    cmd.Connection = conn;
                    cmd.Parameters.Add(new SqlParameter("@GrpProvinceCode", System.Data.SqlDbType.NChar, 50, "ProvinceCode"));
                    cmd.Parameters["@GrpProvinceCode"].Value = cboProvince.ValueMember.ToString();
                    cmd.Parameters.Add(new SqlParameter("@GrpProvince", System.Data.SqlDbType.NChar, 50, "Province"));
                    cmd.Parameters["@GrpProvince"].Value = cboProvince.DisplayMember.ToString();
                }
                else
                {
                    string defaultProvince = "";
                    cmd.Parameters.Add(new SqlParameter("@GrpProvince", System.Data.SqlDbType.NChar, 50, "Province"));
                    cmd.Parameters["@GrpProvince"].Value = defaultProvince;
                }

Basically I have a combobox that lists provinces.
In the database I have a province code and a province
1 Western Cape
2 Eastern cape
3 Northern Cape
....
When the user uses the combobox they must see Western Cape, Eastern Cape, Northern Cape but when the database is updated it must be updated with the province code: 1, 2, or 3.
I want to be able to link the province with the province code.

As I said I am very new to C#, having come from a web development background, I would prefer to be pointed in the right direction and not just given a solution so I can lear.

Thank you for any assistance

 

[jmcilhinney]

For the record, QUOTE tags a for posting quotes. Not surprisingly, CODE tags are for posting code. XCODE tags are generally better still, because they add syntax highlighting. Also, it just makes your code harder to read when you trim the leading whitespace from the first line and not the rest, making the indenting inconsistent. Leave all the whitespace or, better still, trim every line to a common starting point. That's easily done by holding Alt while you select an arbitrary block of text in VS.

As for the issue, you're not supposed to be using the DisplayMember and ValueMember there. They are the names of the columns or properties that you want the data to come from, not the values in those columns or properties.

You use the Text property to get the value from the column or property specified by the DisplayMember. Just note that that will always be a String representation of the data. If you want the original data and it was not a String, you'll have to get it yourself from the SelectedItem.

You similarly use the SelectedValue to get the value from the column or property specified by the ValueMember. That will be returned as an Object reference so you may need to cast in certain cases. In your case, it doesn't matter because the Value of a parameter is type Object anyway.

YOu can also make your code much less verbose. There's no point using a SqlParameter constructor there because the Add method has all equivalent overloads. Also, there's no point specifying the source column when your data is not coming from the column of a DataTable. You also don't need to qualify the SqlDbType enumeration multiple times because the namespace should be imported. Finally, the Add method returns the parameter so you can chain the Value property right onto it:

cmd.Parameters.Add("@GrpProvinceCode", SqlDbType.NChar, 50).Value = cboProvince.SelectedValue;
cmd.Parameters.Add("@GrpProvince", SqlDbType.NChar, 50).Value = cboProvince.Text;

Also, why are you using SqlDbType.NChar? Are your columns realy type 'nchar(50)' in the database? If so, change them. 'char' and 'nchar' are fixed-width while 'varchar' and 'nvarchar' are variable width. You should always use variable-width unless you have a specific reason to use fixed-width. What's the point of having a column that stores 50 double-width characters in every row when you are just storing 1, 2, 3, etc? 'char' and 'varchar' are single-byte characters while 'nchar' and 'nvarchar' are multi-byte characters. I can see that you MIGHT need Unicode for the province names although maybe not even for that, but you definitely don't need it for the codes. That first column should be 'varchar' if you're storing text and definitely not 50 characters wide. The second column should be 'varchar' or maybe 'nvarchar' and only as wide as you need it to be for the data because province names are not going to change any time soon.

 

[lional]

Thank you for your assistance
I have noted the advice you gave and will change in due course.
I only saw afterwards that I only entered the part to update the database, not retrieve the provinces from the table:
I got the retrieval to work by using the following code:

// ####  List Provinces  ###

            // Create a List to store our KeyValuePairs
            string QueryProvince = @"select ProvinceCode, Province from Provinces order by Province";
            SqlCommand cmdProvince = new SqlCommand(QueryProvince, conn);
            List<KeyValuePair<string, string>> provinceData = new List<KeyValuePair<string, string>>();
            SqlDataReader rdrProvince = cmdProvince.ExecuteReader();

            //// Loop through the result set
                while (rdrProvince.Read())
                {
                    provinceData.Add(new KeyValuePair<string, string>(rdrProvince["ProvinceCode"].ToString(), rdrProvince["Province"].ToString()));
                }
            // Clear the comboBox

            cboProvince.DataSource = null;
            cboProvince.Items.Clear();

            // Bind the combobox
            cboProvince.DataSource = new BindingSource(provinceData, null);
            cboProvince.DisplayMember = "value";
            cboProvince.ValueMember = "Key";

            // Close denomination data reader
            rdrProvince.Close();

I am using the following line of code to update the database however it inputs the word "key" instead of the province code:

cmd.Parameters.AddWithValue("@GrpProvince", cboProvince.SelectedValue);

Not sure what I am missing

many thanks

 

[lional]

Thank you for your assistance
I have noted the advice you gave and will change in due course.
I only saw afterwards that I only entered the part to update the database, not retrieve the provinces from the table:
I got the retrieval to work by using the following code:

// ####  List Provinces  ###

            // Create a List to store our KeyValuePairs
            string QueryProvince = @"select ProvinceCode, Province from Provinces order by Province";
            SqlCommand cmdProvince = new SqlCommand(QueryProvince, conn);
            List<KeyValuePair<string, string>> provinceData = new List<KeyValuePair<string, string>>();
            SqlDataReader rdrProvince = cmdProvince.ExecuteReader();

            //// Loop through the result set
                while (rdrProvince.Read())
                {
                    provinceData.Add(new KeyValuePair<string, string>(rdrProvince["ProvinceCode"].ToString(), rdrProvince["Province"].ToString()));
                }
            // Clear the comboBox

            cboProvince.DataSource = null;
            cboProvince.Items.Clear();

            // Bind the combobox
            cboProvince.DataSource = new BindingSource(provinceData, null);
            cboProvince.DisplayMember = "value";
            cboProvince.ValueMember = "Key";

            // Close denomination data reader
            rdrProvince.Close();

I am using the following line of code to update the database however it inputs the word "key" instead of the province code:

cmd.Parameters.AddWithValue("@GrpProvince", cboProvince.SelectedValue);

Not sure what I am missing

many thanks

 

[lional]

Hi
Thank you for the assistance, I sorted my issue out

Have a great day everyone!

 

[kritikas]

You may try this code:

using (SqlConnection connection =new SqlConnection(strCon)) // strCon is the string containing connection string
{
    SqlCommand command =new SqlCommand("select * from tbllogin", connection);

    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    if(reader.HasRows)
    {
        while(reader.Read())
        {
            comboBox1.Items.Add(reader.GetString(intindex)); // index of column you want, because this method takes only int
        }
    }

    reader.Close();
}