//When i click the update button to update record in database,i get following error : " String or binary data would be truncated. The statement has been terminated." plz help me to solve this problem
using System;
using System.Collections;
using System.Configuration;
using System.Web.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;
public partial class UpdateCatA : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
FillList();
}
}
private void FillList()
{
DropDownList1.Items.Clear();
// Define the Select statement. // Three pieces of information are needed: the unique id and the first and last name.
string selectSQL = "SELECT Ref# FROM CatA";
// Define the ADO.NET objects.
SqlConnection myConn = new SqlConnection();
myConn.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
myConn.Open();
SqlCommand cmd = new SqlCommand(selectSQL, myConn);
SqlDataReader reader;
try
{
myConn.Open();
reader = cmd.ExecuteReader();
// For each item, add the author name to the displayed list box text, and store the unique ID in the Value property.
while (reader.Read())
{
ListItem newItem = new ListItem();
string abc = reader["Serial_No"].ToString();
newItem.Text = abc;
DropDownList1.Items.Add(newItem);
}
reader.Close();
}
catch (Exception err)
{
// lblresult.Text = "Error reading list of References. ";
// lblresult.Text += err.Message;
}
finally
{
myConn.Close();
}
}
protected void calEventDate_SelectionChanged1(object sender, EventArgs e)
{
txtEventDate.Text = calEventDate.SelectedDate.ToString();
this.calEventDate.Visible = false;
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
if (this.calEventDate.Visible == false)
this.calEventDate.Visible = true;
else
this.calEventDate.Visible = false;
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string selectSQL;
selectSQL = "SELECT * FROM CatA ";
selectSQL += "WHERE Serial_No='" + DropDownList1.SelectedItem.Text + "'";
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
// Try to open database and read information.
try
{
con.Open();
reader = cmd.ExecuteReader();
reader.Read();
TextBox17.Text = reader["Equipment_Name"].ToString();
TextBox18.Text = reader["Description"].ToString();
TextBox21.Text = reader["Vendor_Name"].ToString();
TextBox22.Text = reader["Bill_No"].ToString();
txtEventDate.Text = reader["Date_Of_Purchase"].ToString();
Label5.Text = reader["Serial_No"].ToString();
TextBox23.Text = reader["Price"].ToString();
TextBox19.Text = reader["Tendor_No"].ToString();
Label1.Text = reader["Type_Of_Purchase"].ToString();
TextBox24.Text = reader["Qty"].ToString();
//string result = reader["Type_Of_Purchase"].ToString();// place your retrieved column here
if (Label1.Text == "Spot Purchase")
{
RadioButtonList1.ClearSelection();
RadioButtonList1.Items[0].Selected = true;
}
else if (Label1.Text == "Tendor Purchase")
{
RadioButtonList1.ClearSelection();
RadioButtonList1.Items[1].Selected = true;
}
reader.Close();
lblStatus.Text = "";
}
catch (Exception err)
{
lblStatus.Text = "Error in displaying data. ";
lblStatus.Text += err.Message;
}
finally
{
con.Close();
}
}
protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
{
// TextBox19.Text = TextBox19.Text.Replace(" ", "");// for removal of space in tendor numbr column else it will not work properly in checking code 328 to 339
if (TextBox17.Text == "" || TextBox23.Text == "" || txtEventDate.Text == "" || TextBox24.Text == "" )
{
//lblStatus.Text = "***You have missed any field*** ";
Response.Write("<script Language=javascript> alert('***You have missed any field***') </script>");
// lblStatus.Text = "Records require an Ref_no,Description,Purchase Price,Sale Price,Quantity,Warning Quantity,Expiry Date,Vendor.";
return;
}
else if (RadioButtonList1.Text == "Tendor Purchase" && TextBox19.Text == "")
{
Response.Write("<script Language=javascript> alert('***Please enter Tendor Number***') </script>");
return;
}
else if (RadioButtonList1.Text == "Spot Purchase" && TextBox19.Text != "")
{
Response.Write("<script Language=javascript> alert('***Tendor Number is not required for Spot Purchase***') </script>");
return;
}
else
{
string updateSQL;
updateSQL = "UPDATE CatA SET Description = @Description, Vendor_Name = @Vendor_Name, Date_Of_Purchase = @Date_Of_Purchase, DateOfPurchase = @DateOfPurchase, Bill_No = @Bill_No, Price = @Price, Type_Of_Purchase = @Type_Of_Purchase, Tendor_No = @Tendor_No, Qty = @Qty WHERE (Serial_No = @Serial_No)";
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
SqlCommand cmd = new SqlCommand(updateSQL, con);
// con.Open();
//cmd.ExecuteNonQuery();
// Try to open database and execute the update.
int updated = 0;
try
{
con.Open();
//lblStatus.Text = updated.ToString() + " record updated.";
cmd.Parameters.Add("@Equipment_Name", TextBox17.Text);
cmd.Parameters.Add("@Serial_No", Label5.Text);
cmd.Parameters.Add("@Description", TextBox18.Text);
cmd.Parameters.Add("@Vendor_Name", TextBox21.Text);
cmd.Parameters.Add("@Bill_No", TextBox22.Text);
cmd.Parameters.Add("@Date_Of_Purchase", txtEventDate.Text);
cmd.Parameters.Add("@Price", TextBox23.Text);
cmd.Parameters.Add("@Tendor_No", TextBox19.Text);
cmd.Parameters.Add("@Type_Of_Purchase", RadioButtonList1.SelectedItem.Value);
cmd.Parameters.Add("@Qty", TextBox24.Text);
cmd.Parameters.Add("@DateOfPurchase", txtEventDate.Text);
cmd.ExecuteNonQuery();
Response.Write("<script Language=javascript> alert('RECORD UPDATED') </script>");
lblStatus.Text = "***Record Updated.***";
}
catch (Exception err)
{
lblStatus.Text = "Error in updating : ";
lblStatus.Text += err.Message;
}
finally { con.Close(); }
}
}
}
sania khan 0 Newbie Poster
sania khan 0 Newbie Poster
pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster
sania khan 0 Newbie Poster
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.