CODEWITHSUNDEEP
c#sql-server
kapo
kapo

Reputation: 13

'System.Data.SqlClient.SqlException' thrown

protected void Button1_Click(object sender, EventArgs e)
{
        SqlConnection con = new SqlConnection(str);

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Companyregister";
        cmd.Parameters.AddWithValue("@Company_name", txtname.Text);
        cmd.Parameters.AddWithValue("@Register_no", txtreg_no.Text);
        cmd.Parameters.AddWithValue("@Type", DropDownList1.Text);
        cmd.Parameters.AddWithValue("@Address", txtadrs.Text);
        cmd.Parameters.AddWithValue("@Email", txtemail.Text);
        cmd.Parameters.AddWithValue("@Contact_no", txtphone.Text);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        Response.Write("Register succesful");
}

Stored procedure:

CREATE PROCEDURE [dbo].Companyregister
    @Company_name varchar(50),
    @Register_no varchar(50),
    @Type varchar(50),
    @Address varchar(50),
    @Email varchar(50),
    @Contact_no varchar(50)
AS
    insert into company_reg (Company_name, Register_no, Type, Address, Email, Contact_no) 
    values (@Company_name, @Register_no, @Type, @Address, @Email, @Contact_no)

    RETURN 0

Error:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: String or binary data would be truncated.

Upvotes: 1

Views: 430

Answers (3)

Shakir Ahamed
Shakir Ahamed

Reputation: 1308

Try like this,

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;     /// this is optional if you declare @ID in your store procedure 
cmd.Parameters.Add("@Company_name", SqlDbType.VarChar).Value = txtname.Text;
cmd.Parameters.Add("@Register_no", SqlDbType.VarChar).Value= txtreg_no.Text;
cmd.Parameters.Add("@Type", SqlDbType.VarChar).Value = DropDownList1.Text;
cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = txtadrs.Text;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = txtemail.Text;
cmd.Parameters.Add("@Contact_no", SqlDbType.VarChar).Value = txtphone.Text;

And check your variable size and name

Upvotes: 0

Richa Garg
Richa Garg

Reputation: 1926

The following error indicates that one or more of your data field is NOT able to fit in the DB field. For eg, may be your address is of 60 characters but your DB size for it is only 50 characters.

String or binary data would be truncated.

To solve this, first check if the data you are inserting complies to the sizes defined in you DB. To catch this more easily, define your parameters to stored procedure as follows:

cmd.Parameters.Add("@Company_name", SqlDbType.VarChar, 50).Value = txtname.Text;
cmd.Parameters.Add("@Register_no", SqlDbType.VarChar, 50).Value = txtreg_no.Text;
cmd.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = DropDownList1.Text;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 50).Value = txtadrs.Text;
cmd.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = txtemail.Text;
cmd.Parameters.Add("@Contact_no", SqlDbType.VarChar, 50).Value = txtphone.Text;

Upvotes: 1

Zac Faragher
Zac Faragher

Reputation: 1001

A good way to start investigating exceptions is to wrap your code in try/catch blocks. This will allow you to dig down in the Exception and hopefully get a more meaningful description of the error. Submitting the Exception as part of your question will help the community in finding the cause.

Use a try/catch block as follows

try{
    // code goes here
}
catch (Exception e) // this block is only entered when an exception is thrown in the try block
{
    Console.WriteLine(e.ToString()); // print the exception description to the Console (if console application)
}

As suggested in the comments, try executing the Stored Procedure using the same parameters and see if it's successful.

Upvotes: 0

Related Questions