操作数类型碰撞：int与插入日期不兼容 [英] Operand type clash: int is incompatible with date in insertion

问题描述

我的代码： -

我的sql date formate是YYYY-mm-dd

my code :-
my sql date formate is YYYY-mm-dd

ed = 1;
            con = new SqlConnection(conn);
            UID = Login.UID;
            DateTime dt = DateTime.Now;
            if (txtTFee.Text != "" || txtpending.Text != "" || txtinst .Text != "" || txtamt .Text != "" || txtbalance .Text != "" || txtcourse .Text != "" || txtdpay .Text != "")
            {
                cmd = new SqlCommand("insert into Fee values(@date,@Regno,@CID,@Feepaid,@tfee,@pend,1,@uid,@crdate,@upuid,@update,1)", con);
                con.Open();
                cmd.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate.Text));
                cmd.Parameters.AddWithValue("@Regno", comboBox1.SelectedValue.ToString());
                cmd.Parameters.AddWithValue("@CID", Convert.ToInt32(lblcid.Text));
                cmd.Parameters.AddWithValue("@Feepaid", Convert.ToDouble(txtamt.Text));
                cmd.Parameters.AddWithValue("@tfee", Convert.ToDouble(txtfeepaid.Text));

                cmd.Parameters.AddWithValue("@pend", Convert.ToDouble(txtpending.Text));
                // cmd.Parameters.AddWithValue("@ps", 1);

                cmd.Parameters.AddWithValue("@uid", UID);
                cmd.Parameters.AddWithValue("@crdate", dt);
                cmd.Parameters.AddWithValue("@upuid", UID);
                cmd.Parameters.AddWithValue("@update", dt);
                cmd.ExecuteNonQuery();
                con.Close();

我尝试过： < br $> b $ b

What I have tried:

ed = 1;
            con = new SqlConnection(conn);
            UID = Login.UID;
            DateTime dt = DateTime.Now;
            if (txtTFee.Text != "" || txtpending.Text != "" || txtinst .Text != "" || txtamt .Text != "" || txtbalance .Text != "" || txtcourse .Text != "" || txtdpay .Text != "")
            {
                cmd = new SqlCommand("insert into Fee values(@date,@Regno,@CID,@Feepaid,@tfee,@pend,1,@uid,@crdate,@upuid,@update,1)", con);
                con.Open();
                cmd.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate.Text));
                cmd.Parameters.AddWithValue("@Regno", comboBox1.SelectedValue.ToString());
                cmd.Parameters.AddWithValue("@CID", Convert.ToInt32(lblcid.Text));
                cmd.Parameters.AddWithValue("@Feepaid", Convert.ToDouble(txtamt.Text));
                cmd.Parameters.AddWithValue("@tfee", Convert.ToDouble(txtfeepaid.Text));

                cmd.Parameters.AddWithValue("@pend", Convert.ToDouble(txtpending.Text));
                // cmd.Parameters.AddWithValue("@ps", 1);

                cmd.Parameters.AddWithValue("@uid", UID);
                cmd.Parameters.AddWithValue("@crdate", dt);
                cmd.Parameters.AddWithValue("@upuid", UID);
                cmd.Parameters.AddWithValue("@update", dt);
                cmd.ExecuteNonQuery();
                con.Close();

推荐答案

这里有两个问题，其中一个你注意到了。

我首先要处理另一个。

永远不要使用Convert.ToDateTime或Convert.ToDouble来处理用户输入 - 如果用户使用了miskeys，这是你的应用程序崩溃的一个秘诀 - 和在一种形式上有很多输入，机会很高。如果我填写了所有内容并且应用程序崩溃了，因为我在第一个框中输错了日期，它会让我感到厌烦...

总是使用DateTime.TryParse等来报告问题用户然后从方法返回给他们一个修理它的机会。



现在你遇到了问题。

当你穿上列出要插入的列名，SQL以固定顺序插入 - 与它在SSMS的列视图中列出的相同。它不会尝试按类型进行任何匹配，也不会跳过任何列，例如它处理的IDENTITY列。

可能你有一个INT ID列，它被设置为IDENTITY，而SQL试图将DateTime值插入该列：因此错误。总是列出您想要SELECT或影响的列是一个好习惯，它还使您的代码在未来更能抵抗数据库更改，并减少SELECT不必要的数据量，从而提高应用程序性能。

Two problems here, one of which you have noticed.
I'll deal with the other one first.
Never use Convert.ToDateTime, or Convert.ToDouble to handle user input - it's a recipe for your app to crash if they user miskeys - and with that many inputs on one form, the chances are high. And it would annoy the heck out of me if I filled in all that and the app crashed because I mistyped the date in the first box...
Always use DateTime.TryParse etc instead, and report problems to users then return from the method to give them a chance to fix it.

Now the problem you are having.
When you don't list the column names you are inserting into, SQL inserts in a fixed order - the same as it lists them in the "Columns" view in SSMS. It doesn't attempt to do any "matching up" by type, or skip any columns such as an IDENTITY column that it handles anyway.
Probably, you do have an INT ID column, which is set as IDENTITY, and SQL is trying to insert a DateTime value to that column: hence the error. It's good practice to always list the columns you want to SELECT or affect, it also makes your code more resistant to database changes in the future, and reduces the amount of data you SELECT uneccesarily, improving application performance.

cmd = new SqlCommand("INSERT INTO Fee (MyDate, RegNo, CID, FeePaid, TFee, PEND, UID, CRDate, UPUID, UPDate) VALUES (@date, @Regno, @CID, @Feepaid, @tfee, @pend, 1, @uid, @crdate, @upuid, @update, 1)", con);

这篇关于操作数类型碰撞：int与插入日期不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！