drop down list value not text

Question

4.00/5 (1 vote)

See more:

Hi everyone,
I have dropdownlist that read the user data and select the correct Category based on the ID(value). and it's work fine, but when i am trying to change this category and update it into the database, it gives me error because it's trying to put the category text not the value(ID)

The error Msg (Conversion failed when converting the nvarchar value 'EXE Technologies' to data type int.)

I want to to put the id not the name.

to open the data . and it shows the Category name and has the value(number)

C#

ddlCategoryID.SelectedItem.Text = drP["Description"].ToString();
              ddlCategoryID.SelectedItem.Value = drP["CategoryID"].ToString();

where when i am trying to update. it gives me error.

C#

            cmd.Parameters.AddWithValue("@CategoryID", ddlCategoryID.SelectedValue);
//I tried this too cmd..AddWithValue("@CategoryID", ddlCategoryID.SelectedItem.Value)

here is the full code.

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Data.Sql;

public partial class ProductEdit : System.Web.UI.Page
{
    public int Product_ID()
    {
        int ProductID = 0;
        ProductID = int.Parse(Request.QueryString["id"]);

        return ProductID;

    }


    protected void Page_Load(object sender, EventArgs e)
    {
        this.Master.HighlightMenu = "Products";

        if (!IsPostBack)
        {
            //Vendor ID
            //Declare the connection object
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

            //Make the connection 
            Conn.Open();

            //Define you query
            string sql = "SELECT *  FROM Vendor";

            //Declare the Command
            SqlCommand cmd = new SqlCommand(sql, Conn);

            //Declare the DataReader
            SqlDataReader dr = null;

            //Fill the DataReader
            dr = cmd.ExecuteReader();

            //Loop through the DataReader
            ddlVendorID.Items.Clear();
            while (dr.Read())
            {
                ListItem li = new ListItem();
                li.Text = dr["VendorName"].ToString();
                li.Value = dr["VendorID"].ToString();
                

            //   ddlVendorID.DataTextField = li.Text.ToString();
          //      ddlVendorID.DataValueField = li.Value.ToString();

                ddlVendorID.Items.Add(li.Text);
             //   ddlVendorID.Items(li.Value);
                ddlVendorID.DataValueField = li.Value;
              
            }
            dr.Close();
            Conn.Close();


            //Declare the connection object
            SqlConnection ConnC = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

            //Make the connection 
            ConnC.Open();

            //Define you query
            string sqlC = "SELECT *  FROM lu_Category";

            //Declare the Command
            SqlCommand cmdC = new SqlCommand(sqlC, ConnC);

            //Declare the DataReader
            SqlDataReader drC = null;

            //Fill the DataReader
            drC = cmdC.ExecuteReader();

            //Loop through the DataReader
            ddlCategoryID.Items.Clear();
            while (drC.Read())
            {
                ListItem liC = new ListItem();
                liC.Text = drC["Description"].ToString();
                liC.Value = drC["CategoryID"].ToString();

              
        //      ddlCategoryID.DataTextField =liC.Text ;
              //ddlCategoryID.DataValueField = liC.Value;
                ddlCategoryID.Items.Add(liC.Text);
              //  ddlCategoryID.DataValueField = liC.Value;
                
                Label1.Text=ddlCategoryID.SelectedItem.Text;
              
               
            }
            drC.Close();
            ConnC.Close();


            if (Request.QueryString["MODE"] != null)
            {
             //   txtVendorID.Text = "";
             //   txtCategoryID.Text = "";
                txtCost.Text = "";
                txtProductDescription.Text = "";
                txtMarkup.Text = "";
                txtProductNumber.Text = "";
                txtProductName.Text = "";
                txtQtyOnHand.Text = "";
                txtShippingWeight.Text = "";
                txtUnit.Text = "";



               


                

                //Declare the connection object


                SqlConnection ConnP = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);


                //Make the connection 
                ConnP.Open();
                
                //Define you query
                string sqlP = "SELECT * FROM Product, Vendor, lu_Category Where ProductID=@ProductID AND Vendor.VendorID = Product.VendorID AND lu_Category.CategoryID = Product.CategoryID";

                //Declare the Command
                SqlCommand cmdP = new SqlCommand(sqlP, ConnP);

                //Add the parameters needed for the SQL query
                cmdP.Parameters.AddWithValue("@ProductID", Product_ID());

                //Declare the DataReader
                SqlDataReader drP = null;

                //Fill the DataReader
                drP = cmdP.ExecuteReader();


                    //Get the data
                           if (drP.Read() == false)
                              {
                                  //No Records
                                  drP.Close();
                                  ConnP.Close();
                                  return; 
                              }  
                txtProductName.Text = drP["ProductName"].ToString();
                txtProductNumber.Text = drP["ProductNumber"].ToString();
                txtProductDescription.Text = drP["ProductDescription"].ToString();
                txtCost.Text = drP["Cost"].ToString();
                txtMarkup.Text = drP["Markup"].ToString();
                txtUnit.Text = drP["Unit"].ToString();
                txtQtyOnHand.Text = drP["QtyOnHand"].ToString();
                txtShippingWeight.Text = drP["ShippingWeight"].ToString();
                ddlCategoryID.SelectedItem.Value = drP["CategoryID"].ToString();
                ddlCategoryID.SelectedItem.Text = drP["Description"].ToString();
               ddlVendorID.SelectedValue = drP["VendorID"].ToString();
               ddlVendorID.SelectedItem.Text = drP["VendorName"].ToString();
                //ddlCategoryID.SelectedItem.Value = drP["CategoryID"].ToString();
              //  ddlVendorID.SelectedItem.Text = drP["VendorName"].ToString();
                
              //  txtCategoryID.Text = dr["CategoryID"].ToString();
               
             //   ddlCategoryID.DataTextField = dpP["Description"].ToString;
                // ddlCategoryID.SelectedItem.Value = drP["CategoryID"].ToString(); 
                drP.Close();
                ConnP.Close();
              

            }
                
        } 
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
         if (Request.QueryString["MODE"] != null)
        {
            //Declare the connection object
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

            //Make the connection 
            Conn.Open();
            
            //Define you query
            string sql = "UPDATE Product SET ProductName=@ProductName, ProductNumber=@ProductNumber, ProductDescription=@ProductDescription, Cost=@Cost, Markup=@Markup, Unit=@Unit, QtyOnHand=@QtyOnHand, ShippingWeight=@ShippingWeight, VendorID=@VendorID, CategoryID=@CategoryID  Where ProductID=@ProductID";
            //string sql = "INSERT INTO Customer(FirstName, LastName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax) VALUES(@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax) Where CustomerID=@CustomerID";
           // int intvenID = Int32.Parse(ddlVendorID.SelectedItem.Value);
//int intCatID = Int32.Parse(ddlCategoryID.SelectedItem.Value);
            //Declare the Command
            SqlCommand cmd = new SqlCommand(sql, Conn);
          
                    //Add the parameters needed for the SQL query
            cmd.Parameters.AddWithValue("@ProductID", Product_ID());
            cmd.Parameters.AddWithValue("@ProductName", txtProductName.Text);
            cmd.Parameters.AddWithValue("@ProductNumber", txtProductNumber.Text);
            cmd.Parameters.AddWithValue("@ProductDescription", txtProductDescription.Text);
            cmd.Parameters.AddWithValue("@Cost", txtCost.Text);
            cmd.Parameters.AddWithValue("@Markup", txtMarkup.Text);
            cmd.Parameters.AddWithValue("@Unit", txtUnit.Text);
            cmd.Parameters.AddWithValue("@QtyOnHand", txtQtyOnHand.Text);
            cmd.Parameters.AddWithValue("@ShippingWeight", txtShippingWeight.Text);
           
            cmd.Parameters.AddWithValue("@VendorID", ddlVendorID.SelectedItem.Value);
            cmd.Parameters.AddWithValue("@CategoryID", ddlCategoryID.SelectedItem.Value);
          //  cmd.Parameters.AddWithValue("@CategoryID", Int32.Parse(ddlCategoryID.DataValueField));
          //  cmd.Parameters.AddWithValue("@VendorID", Int32.Parse(ddlVendorID.SelectedValue));
            // cmd.Parameters.Add("@VendorID", SqlDbType.Int);
            //cmd.Parameters.AddWithValue("@CategoryID", ddlCategoryID.SelectedValue);
            // cmd.Parameters["@VendorID"].Value = ddlVendorID.SelectedItem.Value;
            //cmd.Parameters.AddWithValue("@VendorID", Int32.Parse(ddlVendorID.SelectedItem.Value));
          //  cmd.Parameters.Add("@CategoryID", SqlDbType.Int);
           // cmd.Parameters["@CategoryID"].Value = ddlCategoryID.SelectedItem.Value;
        //    cmd.Parameters.AddWithValue("@CategoryID", ddlCategoryID.SelectedItem.Value.ToString());
          //  cmd.Parameters.AddWithValue("@CategoryID", ddlCategoryID.SelectedItem.Value);
          //  cmd.Parameters.AddWithValue("@VendorID", ddlVendorID.SelectedItem.Value.ToString());

            //Execute the query

            cmd.ExecuteNonQuery();

            Conn.Close();
            Response.Redirect("Products.aspx");

        }
        
         if (Request.QueryString["MODE"] == null)
         {
             //Declare the connection object
             SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

             //Make the connection 
             Conn.Open();
             //Define you query

             string sql = "INSERT INTO Product(ProductName, ProductNumber, ProductDescription, Cost, Markup, Unit, QtyOnHand, ShippingWeight, VendorID, CategoryID)VALUES(@ProductName, @ProductNumber, @ProductDescription, @Cost, @Markup, @Unit, @QtyOnHand, @ShippingWeight, @VendorID, @CategoryID)";

             //Declare the Command
             SqlCommand cmd = new SqlCommand(sql, Conn);

             //Add the parameters needed for the SQL query


            cmd.Parameters.AddWithValue("@ProductName", txtProductName.Text);
            cmd.Parameters.AddWithValue("@ProductNumber", txtProductNumber.Text);
            cmd.Parameters.AddWithValue("@ProductDescription", txtProductDescription.Text);
            cmd.Parameters.AddWithValue("@Cost", txtCost.Text);
            cmd.Parameters.AddWithValue("@Markup", txtMarkup.Text);
            cmd.Parameters.AddWithValue("@Unit", txtUnit.Text);
            cmd.Parameters.AddWithValue("@QtyOnHand", txtQtyOnHand.Text);
            cmd.Parameters.AddWithValue("@ShippingWeight", txtShippingWeight.Text);
           // cmd.Parameters.AddWithValue("@VendorID", ddlVendorID.SelectedValue);
           // cmd.Parameters.AddWithValue("@CategoryID", ddlCategoryID.SelectedValue);
            cmd.Parameters.AddWithValue("@CategoryID", Int32.Parse(ddlCategoryID.SelectedValue));
            cmd.Parameters.AddWithValue("@VendorID", Int32.Parse(ddlVendorID.SelectedValue));
             //Execute the query

             cmd.ExecuteNonQuery();

             Conn.Close();
             Response.Redirect("Products.aspx");
        
         }
        
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        Response.Redirect("Products.aspx");

    }
}

Posted 21-Mar-12 2:13am

Mike988

Updated 21-Mar-12 3:59am

v4

Add a Solution

Comments

ZurdoDev 21-Mar-12 8:17am

How are you populating ddlCategoryID? Are you databinding or just adding one item at a time?

Mike988 21-Mar-12 8:21am

for new user /
//Loop through the DataReader
ddlCategoryID.Items.Clear();
while (drC.Read())
{
ListItem liC = new ListItem();
liC.Text = drC["Description"].ToString();
liC.Value = drC["CategoryID"].ToString();

ddlCategoryID.Items.Add(liC.Text);

}
drC.Close();
ConnC.Close();

// to open existing user

ddlCategoryID.SelectedItem.Text = drP["Description"].ToString();
ddlCategoryID.SelectedItem.Value = drP["CategoryID"].ToString();

3 solutions

Solution 1

Hi ,

Use

C#

ddlCategoryID.SelectedValue = drP["CategoryID"].ToString();

Insted Of

C#

ddlCategoryID.SelectedItem.Value = drP["CategoryID"].ToString();

Hope This may Resolve your problem

Posted 21-Mar-12 2:18am

Ajith The Great

Updated 21-Mar-12 2:19am

v2

Solution 2

be sure whether you provided DataTextField and DataValueField for ddlCategory
and try :

C#

cmd.Parameters.AddWithValue("@CategoryID",ddlCategoryID.SelectedItem.Value);

or

C#

cmd.Parameters.AddWithValue("@CategoryID",ddlCategoryID.SelectedItem.Value.ToString());

Posted 21-Mar-12 2:25am

member60

Updated 21-Mar-12 2:28am

v3

Comments

Mike988 21-Mar-12 8:40am

is that the right way to do it, i tried your code but still gives me same error

ddlCategoryID.SelectedValue = drP["CategoryID"].ToString();
ddlCategoryID.SelectedItem.Text = drP["Description"].ToString();

ddlVendorID.SelectedValue = drP["VendorID"].ToString();

ddlVendorID.SelectedItem.Text = drP["VendorName"].ToString();

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Xeshan Ahmed · Accepted Answer · 2012-03-21T02:30:00

Solution 3

wel its very simple
simply replace your code

C#

cmd.Parameters.AddWithValue("@CategoryID", ddlCategoryID.SelectedValue);

with this

C#

cmd.Parameters.AddWithValue("@CategoryID",Int32.Parse(ddlCategoryID.SelectedValue));

New Solution

C#

for new user 
//Loop through the DataReader
            ddlCategoryID.Items.Clear();
            while (drC.Read())
            {
                ListItem liC = new ListItem();
                liC.Text = drC["Description"].ToString();
                liC.Value = drC["CategoryID"].ToString();

                ddlCategoryID.Items.Add(liC);
                
            }
            drC.Close();
            ConnC.Close();

// to open existing user
//Simply comment this line
//ddlCategoryID.SelectedItem.Text = drP["Description"].ToString();
 ddlCategoryID.SelectedValue = drP["CategoryID"].ToString();

Posted 21-Mar-12 2:30am

Xeshan Ahmed

Updated 24-Apr-12 3:07am

v3

Comments

Mike988 21-Mar-12 8:39am

it gives me , this error Input string was not in a correct format.
may be my populating is wrong ?

ddlCategoryID.SelectedValue = drP["CategoryID"].ToString();
ddlCategoryID.SelectedItem.Text = drP["Description"].ToString();

ddlVendorID.SelectedValue = drP["VendorID"].ToString();

ddlVendorID.SelectedItem.Text = drP["VendorName"].ToString();

Xeshan Ahmed 21-Mar-12 9:52am

have you populated ddlCategoryID before calling these statements

Xeshan Ahmed 21-Mar-12 9:53am

share your full code so we can understand your problem

Mike988 21-Mar-12 9:59am

i post it up, thank you so much for your help

Xeshan Ahmed 21-Mar-12 10:02am

have you found this helpful ??

Mike988 21-Mar-12 10:11am

after commented that line
when i click edit user , it doesn't select the category.

Xeshan Ahmed 21-Mar-12 10:08am

what is datatype of CategoryID in database ?

Mike988 21-Mar-12 10:09am

it's int

Xeshan Ahmed 21-Mar-12 10:10am

is it giving error still ??

Mike988 21-Mar-12 10:45am

it works now , i just make it ddlCategoryID.Items.Add(liC); instead of ddlCategoryID.Items.Add(liC.text);

Thank you so much.