下拉列表值不是文本 [英] drop down list value not text

问题描述

大家好，
我有一个下拉列表，可读取用户数据并根据ID(值)选择正确的类别.并且它工作正常，但是当我尝试更改此类别并将其更新到数据库时，它给了我错误，因为它试图将类别文本而不是value(ID)


错误消息(将nvarchar值"EXE Technologies"转换为数据类型int时转换失败.)

我想输入ID而不是名称.

打开数据.它显示类别名称并具有值(数字)

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)

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

我在哪里尝试更新.它给了我错误.

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

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

这是完整的代码.

here is the full code.

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");

    }
}

推荐答案

非常简单
只需替换您的代码

wel its very simple
simply replace your code

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

与此

with this

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

新解决方案

New Solution

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(); 

使用

Hi ,

Use

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

插入了

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

希望这可以解决您的问题

Hope This may Resolve your problem

请确保您是否为ddlCategory提供了DataTextField和DataValueField
并尝试:

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

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

或

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

这篇关于下拉列表值不是文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！