<%@ Page Language="C#" AutoEventWireup="true" CodeFile="loanmaster.aspx.cs" Inherits="loanmaster" %>
in it I have a c# code to submit the data using SQL SERVER 2008 while executing the page after filling the entry through the
submit button.i have an error in the code line{ a= cm.ExecuteNonQuery();} .The error is String or binary data would be truncated.
the statement has been terminated.
Similarly while updating the database I have also seen this error.Sir please tell me how to solve it.
The C# code is as below :
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
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;
public partial class loanmaster : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
clrctl();
if (Session["mode"].ToString() == "update")
{
string sql = "select loanname,description,minlamt,maxlamt,loaninterest,loantype,remark from loaninfo where id=" + Session["id"].ToString() + "";
SqlDataAdapter da = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
da.Fill(ds, "a");
DataTable dt = ds.Tables["a"];
foreach (DataColumn col in dt.Columns)
{
foreach (DataRow row in dt.Rows)
{
txtloanname.Text = row[0].ToString();
txtdescription.Text = row[1].ToString();
ddlminlamt.Text = row[2].ToString();
ddlmaxlamt.Text = row[3].ToString();
txtloaninterest.Text = row[4].ToString();
ddlloantype.Text = row[5].ToString();
txtremark.Text = row[6].ToString();
break;
}
}
}
}
}
//gd
SqlConnection cnn = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=LMS;Data Source=SHESH-PC\SHESHSQLEXPRESS");
public void clrctl()
{
txtloanname.Text = "";
txtdescription.Text = "";
// ddlminlamt.Text = "";
// ddlmaxlamt.Text = "";
txtloaninterest.Text = "";
// ddlloantype.Text = "";
txtremark.Text = "";
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
/* if (txtloanname.Text == "")
{
lblmsg.Text = "Enter the loan name";
txtloanname.Focus();
return;
}
if (txtdescription.Text == "")
{
lblmsg.Text = "Enter the description";
txtdescription.Focus();
return;
}
if (ddlminlamt.Text == "")
{
lblmsg.Text = "Enter the minimum loan amount";
ddlminlamt.Focus();
return;
}
if (ddlmaxlamt.Text == "")
{
lblmsg.Text = "Enter the maximum loan amount";
ddlminlamt.Focus();
return;
}
if (txtloaninterest.Text == "")
{
lblmsg.Text = "Enter the interest of loan";
txtloaninterest.Focus();
return;
}
if (ddlloantype.Text == "")
{
lblmsg.Text = "Enter the type of loan";
ddlminlamt.Focus();
return;
}
if (txtremark.Text == "")
{
lblmsg.Text = "Enter the Remark.";
txtremark.Focus();
return;
}*/
if (Session["mode"].ToString() == "save")
{
//insert into
string sql = "insert into loaninfo(loanname,description,minlamt,maxlamt,loaninterest,loantype,remark,entrydate,entryperson)values('" + txtloanname.Text + "','" + txtdescription.Text + "','" + ddlminlamt.SelectedItem.ToString() + "','" + ddlmaxlamt.SelectedItem.ToString() + "', " + txtloaninterest.Text + ",'" + ddlloantype.SelectedItem.ToString() + "' ,'" + txtremark.Text + "','" + DateTime.Now.ToString() + "','" + Session["loginname"].ToString() + "')";
SqlCommand cm = new SqlCommand();
cm.Connection = cnn;
cm.CommandType = CommandType.Text;
cm.CommandText = sql;
int a = 0;
cnn.Open();
a = cm.ExecuteNonQuery();
cnn.Close();
if (a == 0)
{
lblmsg.Text = "Data is not saved.";
}
else
{
lblmsg.Text = "Data is saved.";
clrctl();
}
}
else
{
//update the loaninfo
string sql ="update loaninfo set loanname ='"+txtloanname+"',description ='"+txtdescription.Text+"',minlamt='"+ddlminlamt.SelectedItem.ToString()+"',maxlamt='"+ddlmaxlamt.SelectedItem.ToString()+"',loaninterest="+txtloaninterest.Text+",loantype='"+ ddlloantype.SelectedItem.ToString() +"',remark='"+txtremark.Text+"'where id ="+ Session["id"].ToString() +"";
SqlCommand cm = new SqlCommand();
cm.Connection = cnn;
cm.CommandType = CommandType.Text;
cm.CommandText = sql;
int a = 0;
cnn.Open();
a = cm.ExecuteNonQuery();
cnn.Close();
if (a == 0)
{
lblmsg.Text = "DATA NOT UPDATED";
}
else
{
lblmsg.Text = "DATA UPDATED";
clrctl();
}
}
}
protected void txtloanname_TextChanged(object sender, EventArgs e)
{
}
protected void txtdescription_TextChanged(object sender, EventArgs e)
{
}
protected void ddlminlamt_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void ddlmaxlamt_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void txtloaninterest_TextChanged(object sender, EventArgs e)
{
}
protected void ddlloantype_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void txtremark_TextChanged(object sender, EventArgs e)
{
}
}
Jignesh TrivediPosted Apr 22, 2012, 11:25 PM
mostly this error comes when your size of input is exceed.
Example I have one table and it has one column say name and size of name column is varchar(20), now i am trying to insert name in this table with length 25. at that time sql server throw this error.
So recommended that chat your input and database size.
hope this help.
SenthilkumarPosted Apr 23, 2012, 12:05 AM
The reason for this error is clear.
When you set the size to your column in the sql server table, then you need to do insert/update with the required size of the data.
Support, you have a table called register
CREATE TABLE Register
(
ID INT IDENTITY(1,1)
,Name VARCHAR(10)
)
INSERT INTO Register(Name) VALUES('AAAAABBBBBBCCCCCC')
Here the actual size of the Name column is 10 and you are trying to insert 15 chars, then you will get string binary truncated.
Shesh MishraPosted Apr 22, 2012, 11:56 PM