Error inserting record.String or binary data would be truncated. The statement has been terminated.

  • Thread starter Thread starter Icon Iconoclast
  • Start date Start date
I

Icon Iconoclast

Can someone please tell me what's wrong here? I'm trying to insert data into
an AUTHORS table.

au_id id:varchar(11)au_lname varchar(40)
au_fname varchar(20)phone
char(12)address
varchar(40) Allow null
city varchar(20) Allow null
state char(2) Allow null
zip char(5) Allow null
contract bitprotected void cmdInsertNew_Click(object sender, EventArgs e)
{

if (txtUniqueID.Text == "" || txtFirstName.Text == "" ||
txtLastName.Text == "")
{
lbl.Text = "Records require ID, first name and last name.";
return;
}

string insertSQL;
insertSQL = "insert into authors (au_id, au_fname, au_lname, phone,
address, city, state, zip, contract) values ('";
insertSQL += txtUniqueID.Text + "','" + txtFirstName.Text + "','" +
txtLastName.Text + "','" + txtPhone.Text + "','" + txtAddress + "','" +
txtCity + "','";
insertSQL += txtState.Text + "','" + txtZIP +"','"+
Convert.ToInt16(chkContract.Checked)+"')";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand(insertSQL, con);

int added = 0;
try
{
con.Open();
lblMessage.Text = "Opened Successfully";
added = com.ExecuteNonQuery();
lbl.Text = added.ToString() + "records inserted";
}
catch (Exception err)
{
lbl.Text = "Error inserting record." + err.Message;
}
finally
{
con.Close();
}
}
 
1) Do not build concatenated SQL. Always use Parameters. This deals with the
framing errors and other issues as well as preventing SQL injection attacks.
2) See 1.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com/blog/billva
____________________________________________________________________________________________
 
Back
Top