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);
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)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
Conn.Open();
string sql = "SELECT * FROM Vendor";
SqlCommand cmd = new SqlCommand(sql, Conn);
SqlDataReader dr = null;
dr = cmd.ExecuteReader();
ddlVendorID.Items.Clear();
while (dr.Read())
{
ListItem li = new ListItem();
li.Text = dr["VendorName"].ToString();
li.Value = dr["VendorID"].ToString();
ddlVendorID.Items.Add(li.Text);
ddlVendorID.DataValueField = li.Value;
}
dr.Close();
Conn.Close();
SqlConnection ConnC = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
ConnC.Open();
string sqlC = "SELECT * FROM lu_Category";
SqlCommand cmdC = new SqlCommand(sqlC, ConnC);
SqlDataReader drC = null;
drC = cmdC.ExecuteReader();
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);
Label1.Text=ddlCategoryID.SelectedItem.Text;
}
drC.Close();
ConnC.Close();
if (Request.QueryString["MODE"] != null)
{
txtCost.Text = "";
txtProductDescription.Text = "";
txtMarkup.Text = "";
txtProductNumber.Text = "";
txtProductName.Text = "";
txtQtyOnHand.Text = "";
txtShippingWeight.Text = "";
txtUnit.Text = "";
SqlConnection ConnP = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
ConnP.Open();
string sqlP = "SELECT * FROM Product, Vendor, lu_Category Where ProductID=@ProductID AND Vendor.VendorID = Product.VendorID AND lu_Category.CategoryID = Product.CategoryID";
SqlCommand cmdP = new SqlCommand(sqlP, ConnP);
cmdP.Parameters.AddWithValue("@ProductID", Product_ID());
SqlDataReader drP = null;
drP = cmdP.ExecuteReader();
if (drP.Read() == false)
{
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();
drP.Close();
ConnP.Close();
}
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
if (Request.QueryString["MODE"] != null)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
Conn.Open();
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";
SqlCommand cmd = new SqlCommand(sql, Conn);
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.ExecuteNonQuery();
Conn.Close();
Response.Redirect("Products.aspx");
}
if (Request.QueryString["MODE"] == null)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
Conn.Open();
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)";
SqlCommand cmd = new SqlCommand(sql, Conn);
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("@CategoryID", Int32.Parse(ddlCategoryID.SelectedValue));
cmd.Parameters.AddWithValue("@VendorID", Int32.Parse(ddlVendorID.SelectedValue));
cmd.ExecuteNonQuery();
Conn.Close();
Response.Redirect("Products.aspx");
}
}
protected void btnCancel_Click(object sender, EventArgs e)
{
Response.Redirect("Products.aspx");
}
}