将数据插入数据库中的问题 [英] problem in inserting data into database

问题描述

嗨.
我用以下代码编写了一条插入语句:

Hi.
I have written an insert statement with the below code:

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();
    }
}

但是在调试和跟踪到达SerialNumber的代码(在DB中为Int)时，会发生此错误:

FormatException未被用户代码处理
输入字符串的格式不正确.

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.

推荐答案

您在这里遇到了一些问题.

1-为什么在不返回任何内容时调用executescalar? ExecuteNonQuery.

2-永远不要调用Convert.ToInt32.使用int.TryParse，如果您的文本不是数字，则不会引发异常，这显然不是't

3-切勿在您的表示层中放入这么多的数据库代码.放无.而是调用数据层.您的其余代码看起来很专业，但是在这里不是.

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.

这篇关于将数据插入数据库中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！