Adding values to asp.net dropdown in cs file

I'm having problems adding the right values to an asp.net dropdown in the cs file. The dropdown is also an FK, linking to the PK of another table. The values will be populated by getting PK values (int) from the PK column, and the value of the Company column (varchar50) as well. The PK val will be used for the actual value of the selected item. The Company value will be used to display the Company name associated with the PK val. I've tried this:

<div style="padding:20px">
        First Name<br />
        <asp:TextBox ID="FirstName" runat="server"></asp:TextBox><br />
        Last Name<br />
        <asp:TextBox ID="LastName" runat="server"></asp:TextBox><br />
        PhoneNumber<br />
        <asp:TextBox ID="PhoneNumber" runat="server"></asp:TextBox><br />
        Username<br />
        <asp:TextBox ID="UserName" runat="server"></asp:TextBox><br />
        Password<br />
        <asp:TextBox ID="Password" runat="server"></asp:TextBox><br />
        User Type<br />
        <asp:DropDownList ID="UserType" runat="server"></asp:DropDownList><br />
        Company<br />
        <asp:DropDownList ID="Dispatcher" runat="server"></asp:DropDownList>
    </div>
    <div style="margin-left:20px">
        <asp:Button ID="Add" runat="server" Text="Add" 
            style="width:50px; margin-right:5px" OnClick="Add_Click"  />
    </div>

Open in new window


try
        {
            myConnection.Open();

            SqlDataReader myReader = null;
            SqlCommand myCommand = new SqlCommand("Select ID, DispatcherName FROM Dispatchers", myConnection);
            myReader = myCommand.ExecuteReader();
            Dispatcher.Items.Clear();
            while (myReader.Read())
            {
                string DispatcherName = myReader["DispatcherName"].ToString();
                string ID = myReader["ID"].ToString();
                Dispatcher.Items.Add(new ListItem("\"" + DispatcherName + "\", \"" + ID + "\""));
            }
            myConnection.Close();
        }
        catch (Exception error)
        {
            Response.Write("<p>" + error + "</p>");
            myConnection.Close();
        }

    }
    protected void Add_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection myConnection = new SqlConnection("server=localhost;" +
                                           "Trusted_Connection=yes;" +
                                           "database=myData;" +
                                           "connection timeout=30");
            myConnection.Open();
            try
            {
                SqlCommand myInsert = new SqlCommand("INSERT INTO Users (FirstName, LastName, PhoneNumber, Email, Password, UserType, Dispatcher) Values ('" + FirstName.Text + "', '" + LastName.Text + "', '" + PhoneNumber.Text + "', '" + UserName.Text + "', '" + Password.Text + "', '" + UserType.SelectedItem.Value + "', '" + Dispatcher.SelectedItem.Value + "')", myConnection);
                myInsert.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                Response.Write("<p>" + err + "</p>");
                myConnection.Close();
            }
        }
        catch (Exception err)
        {
            Response.Write("<p>" + err + "</p>");
        }
    }

Open in new window



And it gives me an error, because it seems to be concatenating both the SelectedItem.Value and the SelectedItem.Text, when it's supposed to be writing just the value part (int) to the record. I thought SelectedItem.Value would only return the value of the choice, but not the text or display value.

"System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the varchar value '"Company1", "1"' to data type int. at System.Data.SqlClient.SqlConnection"
LVL 9
BobHavertyComhAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
Bob,
the method that I showed you is the recommended way of doing that.
If you want to proceed on your own way and does not take advantage of the framework, then the only line to be changed is this:
Dispatcher.Items.Add(new ListItem(DispatcherName,ID));

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
It has passed a good time since I last have coded in .NET, but I believe this is what you are looking for:
try
        {
            myConnection.Open();

            SqlDataReader myReader = null;
            SqlCommand myCommand = new SqlCommand("Select ID, DispatcherName FROM Dispatchers", myConnection);
            myReader = myCommand.ExecuteReader();
            Dispatcher.Items.Clear();
            Dispatcher.DataSource = MyDataSet;
            Dispatcher.DataTextField = "DispatcherName";
            Dispatcher.DataValueField = "ID";
            Dispatcher.DataBind();
            myReader.Close();
            myConnection.Close();
        }
        catch (Exception error)
        {
            Response.Write("<p>" + error + "</p>");
            myConnection.Close();
        }

    }

Open in new window

0
 
BobHavertyComhAuthor Commented:
Hi Walter, thanks for your response. If possible, I would like to leave the code as is as much as possible, and fix the error in the existing method I am using. There seems to only be one small misunderstanding that I have in my code, because somehow I am not adding items to the dropdownlist control the way I think that I am.

The dropdown items should have a display value of Company-X, but the actual value should be an ID number that comes from the query. Instead, what I am getting in the Dropdown is (for example) Company1, 1, When all I want to appear in the dropdown is Company1, but I want to use the actual value of the selection, which would be 1, to add to the actual record.

I'm not sure I need to bother to databind
0
 
BobHavertyComhAuthor Commented:
Hi Walter, I wasn't being critical of your idea and it often is the best way to do it, I just knew that I didn't need to bother to change everything over, and that there was a simple mistake with what I was doing. And you picked it out PERFECTLY!! Just a misunderstanding on my part about the quotes when adding new list items using variables that are strings. Thanks a bunch!!! Works perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.