Link to home
Start Free TrialLog in
Avatar of Arnold Layne
Arnold LayneFlag for United States of America

asked on

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"
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

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

Avatar of Arnold Layne

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.