Click here to Skip to main content
15,905,914 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi.
I have written an insert statement with the below code:

C#
protected void SubmitButton(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection();
    SqlTransaction trans = null;

    conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBMarashi"].ConnectionString;
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    conn.Open();

    trans = cmd.Connection.BeginTransaction();
    try
    {
        cmd.CommandText = "INSERT INTO Persons (FirstName,LastName,FatherName,PersonelCode,SerialNumber,NationalNumber,BirthDate_S,PassportNumber,Nationality,Phone,Mobile,PostalCode,Email,HomeAddress) values(@FirstName,@LastName,@FatherName,@PersonelCode,@SerialNumber,@NationalNumber,@BirthDate_S,@PassportNumber,@Nationality,@Phone,@Mobile,@PostalCode,@Email,@HomeAddress)";
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstNameTextBox.Text;
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastNameTextBox.Text;
        cmd.Parameters.Add("@FatherName", SqlDbType.NVarChar).Value = FatherNameTextBox.Text;
        cmd.Parameters.Add("@PersonelCode", SqlDbType.Int).Value = Convert.ToInt32(personelCodeTextBox.Text);
        cmd.Parameters.Add("@SerialNumber", SqlDbType.Int).Value = Convert.ToInt32(SerialNumTextBox.Text);
        cmd.Parameters.Add("@NationalNumber", SqlDbType.Int).Value = Convert.ToInt32(nationalNumTextBox.Text);
        cmd.Parameters.Add("@BirthDate_S", SqlDbType.Date).Value = Convert.ToDateTime(BirthDateTextBox.Text);
        cmd.Parameters.Add("@PassportNumber", SqlDbType.Int).Value = Convert.ToInt32(PassNumTextBox0.Text);
        cmd.Parameters.Add("@Nationality", SqlDbType.Int).Value = Convert.ToInt32(NationalityTextBox.Text);
        cmd.Parameters.Add("@Phone", SqlDbType.Int).Value = Convert.ToInt32(PhoneTextBox0.Text);
        cmd.Parameters.Add("@Mobile", SqlDbType.Int).Value = Convert.ToInt32(MobileTextBox1.Text);
        cmd.Parameters.Add("@PostalCode", SqlDbType.Int).Value = Convert.ToInt32(PostalCodeTextBox.Text);
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = (EmailTextBox1.Text);
        cmd.Parameters.Add("@HomeAddress", SqlDbType.NVarChar).Value = (AddressTextBox.Text);

        trans.Commit();
        cmd.ExecuteScalar();
    }
    catch (SqlException se)
    {
        try
        {
            trans.Rollback();
        }
        catch { Page.Title = "SqlException:" + se.Message; }

        try
        {
            trans.Rollback();
        }
        catch (Exception ex)
        {
            Page.Title = "Exception:" + ex.Message;
        }
    }
    finally
    {
        conn.Close();
    }
}


but in debugging and tracing the code when it reaches to SerialNumber(which is Int in DB)this error occures :

FormatException was unhandled by user code
Input string was not in a correct format.
Posted

1 solution

You have a few issues here.

1 - why call executescalar when you're not returning anything ? ExecuteNonQuery.

2 - NEVER call Convert.ToInt32. Use int.TryParse, it will not throw an exception if your text is not a number, which apparently, it isn't

3 - NEVER put this much DB code in to your presentation layer. Put NONE. Call a data layer instead. The rest of your code looks quite professional, but doing this here, is not.
 
Share this answer
 
Comments
mohammad ehsan 21-Jul-12 3:54am    
thank you so much .
but would you please be more specefic about "NEVER put this much DB code in to your presentation layer".
what is the presentation layer?
Christian Graus 21-Jul-12 3:55am    
your aspx.cs files, the files that handle the user interface, is the presentation layer. If it's in a dll or just in classes, your database and business logic should not be in the presentation layer. It's called 3-tiered, or sometimes n-tiered, development.
Ranjith Reddy CSE 21-Jul-12 5:36am    
How to create a cs file using business logic layer and how can we call it from aspx.cs page.

Please help.
Christian Graus 21-Jul-12 5:39am    
You move the code to a class, and you create instances of those classes and call the methods on them.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900