当我的桌子上没有任何nvarchar时，如何解决“将数据类型nvarchar转换为数字的错误”？

I've been under some problems regarding data type nvarchar to numeric error. Since I'm still beginning with SQL and C#, I was wondering if someone could lead me to some valid conclusions.

关于数据类型nvarchar到数字错误，我遇到了一些问题。由于我还是从SQL和C＃开始，我想知道是否有人可以引导我得出一些有效的结论。

SQL code:

SQL代码：

CREATE TABLE [dbo].[CustomerMembership] 
(
    [CM_ID]            VARCHAR(255) NOT NULL,
    [CustomerID]       INT NOT NULL,
    [MembershipID]     INT NOT NULL,
    [DateFrom]         DATETIME NOT NULL,
    [Months]           VARCHAR(2) NOT NULL,
    [DateTo]           DATETIME NOT NULL,
    [ChargesPerMonth]  DECIMAL(18,2) NOT NULL,
    [DiscountPer]      DECIMAL(3,0) NOT NULL,
    [VATPer]           DECIMAL(3,0) NOT NULL,
    [VATAmount]        DECIMAL(3,0) NOT NULL,
    [ServiceTaxPer]    DECIMAL(3,0) NOT NULL,
    [ServiceTaxAmount] DECIMAL(3,0) NOT NULL,
    [TotalCharges]     DECIMAL(18,2) NOT NULL,
    [BillDate]         DATETIME NOT NULL,
    [SubTotal]         DECIMAL(18,2) NOT NULL,
    [GrandTotal]       DECIMAL(18,2) NOT NULL,
    [TotalPaid]        DECIMAL(18,2) NOT NULL,
    [DiscountAmount]   DECIMAL(3,0) NOT NULL,

    CONSTRAINT [PK_CustomerMembership] 
        PRIMARY KEY CLUSTERED ([CM_ID] ASC)
)

C# code:

C＃代码：

cb = @"insert into CustomerMembership
       (CM_ID,BillDate,CustomerID,MembershipID,DateFrom,
        Months,DateTo,ChargesPerMonth,TotalCharges,
        DiscountPer,DiscountAmount,SubTotal,VATPer,VATAmount,
        ServiceTaxPer,ServiceTaxAmount,GrandTotal,TotalPaid) VALUES 
       (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,
        @d12,@d13,@d14,@d15,@d16,@d17,@d18)";
cc.cmd = new SqlCommand(cb);
cc.cmd.Connection = cc.con;

cc.cmd.Parameters.AddWithValue("@d1",txtMembershipID.Text);
cc.cmd.Parameters.AddWithValue("@d2", dtpBillDate.Value);
cc.cmd.Parameters.AddWithValue("@d3", txtM_ID.Text);
cc.cmd.Parameters.AddWithValue("@d4", txtMembershipTypeID.Text);
cc.cmd.Parameters.AddWithValue("@d5", dtpDateFrom.Value);
cc.cmd.Parameters.AddWithValue("@d6", txtMonths.Text);
cc.cmd.Parameters.AddWithValue("@d7", dtpDateTo.Value);
cc.cmd.Parameters.AddWithValue("@d8", txtChargesPerMonth.Text);
cc.cmd.Parameters.AddWithValue("@d9", txtTotalCharges.Text);
cc.cmd.Parameters.AddWithValue("@d10", txtDiscountPer.Text);
cc.cmd.Parameters.AddWithValue("@d11", txtDiscountAmount.Text);
cc.cmd.Parameters.AddWithValue("@d12", txtSubTotal.Text);
cc.cmd.Parameters.AddWithValue("@d13", txtVATPer.Text);
cc.cmd.Parameters.AddWithValue("@d14", txtVATAmount.Text);
cc.cmd.Parameters.AddWithValue("@d15", txtServiceTaxPer.Text);
cc.cmd.Parameters.AddWithValue("@d16", txtServiceTaxAmount.Text);
cc.cmd.Parameters.AddWithValue("@d17", txtGrandTotal.Text);
cc.cmd.Parameters.AddWithValue("@d18", txtTotalPaid.Text);

cc.cmd.ExecuteReader();
cc.con.Close();

st1 = lblUser.Text;
st2 = "Foi adicionada uma nova mensalidade com o id '" + txtMembershipID.Text + "' ao membro '" + txtMemberName.Text + "'";
cf.LogFunc(st1, System.DateTime.Now, st2);
btnSave.Enabled = false;

MessageBox.Show("Gravado com sucesso", "Registo", MessageBoxButtons.OK, MessageBoxIcon.Information);

After the comments:

评论后：

cc.con = new SqlConnection(cs.DBConn);
cc.con.Open();
string cb = "insert into CustomerMembership
            (CM_ID,BillDate,CustomerID,MembershipID,DateFrom,Months,DateFrom,
             ChargesPerMonth,TotalCharges,DiscountPer,DiscountAmount,
             SubTotal,VATPer,VATAmount,ServiceTaxPer,
             ServiceTaxAmount,GrandTotal,TotalPaid) VALUES  
           (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,
            @d13,@d14,@d15,@d16,@d17,@d18)";
cc.cmd = new SqlCommand(cb);
cc.cmd.Connection = cc.con;
cc.cmd.Parameters.Add("@d1", SqlDbType.Int, 50).Value = txtMembershipID.Text ;
cc.cmd.Parameters.Add("@d2", SqlDbType.DateTime).Value = dtpBillDate.Value;
cc.cmd.Parameters.Add("@d3", SqlDbType.VarChar, 50).Value = txtM_ID.Text;
cc.cmd.Parameters.Add("@d4", SqlDbType.VarChar, 50).Value = txtMembershipTypeID.Text;
cc.cmd.Parameters.Add("@d5", SqlDbType.DateTime).Value = dtpDateFrom.Value;
cc.cmd.Parameters.Add("@d6", SqlDbType.VarChar, 2).Value = txtMonths.Text;
cc.cmd.Parameters.Add("@d7", SqlDbType.DateTime).Value = dtpDateTo.Value;
cc.cmd.Parameters.Add("@d8", SqlDbType.VarChar, 50).Value = txtChargesPerMonth.Text;
cc.cmd.Parameters.Add("@d9", SqlDbType.VarChar, 50).Value = txtTotalCharges.Text;
cc.cmd.Parameters.Add("@d10", SqlDbType.VarChar, 50).Value = txtDiscountPer.Text;
cc.cmd.Parameters.Add("@d11", SqlDbType.VarChar, 50).Value = txtDiscountAmount.Text;
cc.cmd.Parameters.Add("@d12", SqlDbType.VarChar, 50).Value = txtSubTotal.Text;
cc.cmd.Parameters.Add("@d13", SqlDbType.VarChar, 50).Value = txtVATPer.Text;
cc.cmd.Parameters.Add("@d14", SqlDbType.VarChar, 50).Value = txtVATAmount.Text;
cc.cmd.Parameters.Add("@d15", SqlDbType.VarChar, 50).Value = txtServiceTaxPer.Text;
cc.cmd.Parameters.Add("@d16", SqlDbType.VarChar, 50).Value = txtServiceTaxAmount.Text;
cc.cmd.Parameters.Add("@d17", SqlDbType.VarChar, 50).Value = txtGrandTotal.Text;
cc.cmd.Parameters.Add("@d18", SqlDbType.VarChar, 50).Value = txtTotalPaid.Text;

cc.cmd.ExecuteReader();
cc.con.Close();
st1 = lblUser.Text;
st2 = "Foi adicionada uma nova mensalidade com o id '" + txtMembershipID.Text + "' ao membro '" + txtMemberName.Text + "'";
cf.LogFunc(st1, System.DateTime.Now, st2);
btnSave.Enabled = false;
MessageBox.Show("Gravado com sucesso", "Registo", MessageBoxButtons.OK, MessageBoxIcon.Information);

2 个解决方案

#1

Answering your updated code:

回答您的更新代码：

Your line

你的路线

cc.cmd.Parameters.Add("@d1", SqlDbType.Int, 50).Value = txtMembershipID.Text ;

is expecting a int to be passed in, you are passing in a string from the text box. You need to convert the string to a int before you assign the value.

期望传入一个int，你从文本框传入一个字符串。在分配值之前，需要将字符串转换为int。

//This can throw a exception if the text is not a valid int.
cc.cmd.Parameters.Add("@d1", SqlDbType.Int).Value = int.Parse(txtMembershipID.Text) ;

The same thing needs to be done for all your decimal and datetime entries too for their respective data types.

对于各自的数据类型，也需要对所有小数和日期时间条目执行相同的操作。

#2

You need to pass parameters of the exact datatype expected by the columns that you want to update. For example the column CustomerID in the datatable is of type INT. The parameter for this field should be an integer

您需要传递要更新的列所期望的确切数据类型的参数。例如，数据表中的CustomerID列是INT类型。该字段的参数应为整数

Consider also that all these conversions should be done with appropriate check against the user input. You cannot trust your user to type exactly a number when you expect a number.

还要考虑所有这些转换应该通过对用户输入的适当检查来完成。当您期望一个数字时，您无法信任您的用户输入完全正确的数字。

 .... string columns could get directly from the Text property
 .... date columns need a DateTime parameter as well as int columns

 int memberID;
 if(!Int32.TryParse(txtMembershipID.Text, out memberID))
 {
    MessageBox.Show("Invalid number");
    return;
 }
 cc.cmd.Parameters.Add("@d3", SqlDbType.Int).Value = memberID;

Also, to avoid confusion with all these parameter placeholders with an quasi-identical name, I suggest to name the parameters in a more recognizable way. You could use the same name of the column with the @ prefix. So when you set the values you have a more clear view of your code.

另外，为了避免与具有准相同名称的所有这些参数占位符混淆，我建议以更易识别的方式命名参数。您可以使用与@前缀相同的列名称。因此，当您设置值时，您可以更清晰地查看代码。

#1