数据插入难度 [英] A Data Insertion Difficulty

问题描述

我正在设计一个客户端/服务器应用程序，用户可以将其连接到服务器计算机上的数据库。关键是应用程序允许设置不同的用户级别，并且根据用户的级别，应该使用用户或客户端计算机的日期进行插入，否则应该使用服务器计算机上的日期进行插入。

我很困惑如何编码插入部分以满足此需求。



示例：

假设我有一个带有Amount和DatePaid列的表[table1]。这是我使用的代码：

I am designing a client/server application where users connect to a database on the server computer. The point is that the application allows the setting of different user levels and based on the user''s level, the date of the user or client computer should be used for the insertion otherwise the date on the server machine should be used for the insertion.
I am stuck with how to code the part that does the insertion to cater for this.

Example:
Assuming I have a table [table1] with the columns Amount and DatePaid. This is the code I used:

query = "INSERT INTO table1" +
        "(Amount , DatePaid) " +
        "VALUES " +
        "(@Amount , @DatePaid)";

        // Declares CmdString as an object of the SqlCommand object
        // Assuming that con is a valid mysql connections string
        MySqlCommand CmdString = new MySqlCommand(query, con);

        try
        {
            // Specifies the query stored in the CmdString variable as the query  to be executed. Assuming MySqlDataAdap is validly declared and initialized.
            MySqlDataAdap.InsertCommand = CmdString;

            // Associates the named parameter variables with the fields in the table
            // Assuming amt and payDate are valid variables
            MySqlDataAdap.InsertCommand.Parameters.Add(new MySqlParameter("@Amount", amt));
            MySqlDataAdap.InsertCommand.Parameters[0].Value = amt;

            MySqlDataAdap.InsertCommand.Parameters.Add(new MySqlParameter("@DatePaid", payDate));
            MySqlDataAdap.InsertCommand.Parameters[1].Value = 
                (getUserLevel() == "Lower Level" ? "SYSDATE()" : 
                depositDate.ToString());
            //Where SYSDATE() is a MySql function for returning the current server's date and assuming getUserLevel() is a valid function for retrieving the current user level

            // Executes the query
            rowsAffected = MySqlDataAdap.InsertCommand.ExecuteNonQuery();
        }
        catch (MySqlException MyException)
        {
            MessageBox.Show("Message: " + MyException.Message + "\n" +
                            "Source: " + MyException.Source + "\n" +
                            "Number: " + MyException.Number.ToString(),
                            "Inserting Pay",
                            MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

当我执行上面的操作时，我收到错误消息：

字符串无法识别为有效的DateTime。从索引0开始有一个未知单词。



请大家帮我解决这个难题。谢谢

When I execute the above above I get the error message:
The string was not recognize as a valid DateTime. There is a unknown word starting at index 0.

Please folks help me to get round this puzzle. Thanks

推荐答案

由于 DatePaid 字段是Datetime字段，因此在分配相应参数时应使用DateTime：

Since the DatePaid field is a Datetime one, you should use a DateTime when you assign the corresponding parameter :

<br />
<pre lang="c#">MySqlDataAdap.InsertCommand.Parameters[1].Value =<br />
     (getUserLevel() == "Lower Level" ? SYSDATE() : depositDate);</pre><br />

你有多余的代码：

You''ve got redundant code:

MySqlDataAdap.InsertCommand.Parameters.Add(new MySqlParameter("@Amount", amt));
MySqlDataAdap.InsertCommand.Parameters[0].Value = amt;

同样，你添加第二个参数：

Similarly, where you add the second parameter:

MySqlDataAdap.InsertCommand.Parameters.Add(new MySqlParameter("@DatePaid", payDate));
MySqlDataAdap.InsertCommand.Parameters[1].Value =  (getUserLevel() == "Lower Level" ? SYSDATE() :  depositDate.ToString());

您将属性设置为 payDate ，然后再将其设置为 SYSTDATE（）或 depositDate.ToString（）所以你应该决定哪一个是正确的。另外，在最后一个术语中你不需要 .ToString（），因为值是一个对象而不是一个字符串。据我所知 SYSTDATE（） isn''tac＃方法因此也是可疑的，我猜您需要从描述中删除该行代码它做了什么，但我不确定。



如果，在你更改了代码后，你仍然会得到相同的错误信息，你需要检查一下您传入日期付费参数的值。目前发生的是当前日期作为字符串而不是 datetime 传递，框架可以'弄清楚如何将字符串转换为.net日期时间（将字符串更改为不同的对象类型称为解析）。您需要弄清楚解析失败的原因。这可能发生很多原因，例如日期只是无效的2012年12月32日，文化起到了一定作用，例如当美国格式化的日期被提供日期预计英国日期时（例如12/13/2012的13 ^th 12月）或甚至传递的日期为空。在你的情况下，我会google DateTime.Parse 并实际上手动将字符串解析为 datetime 对象并将其作为值而不是其字符串传递。

You set the property to payDate, then set it again to SYSTDATE() or depositDate.ToString() so you should decide which of these is correct. Additionally you don''t need the .ToString() on the final term as the value is an object not a string. As far as I''m aware SYSTDATE() isn''t a c# method so that is suspicious too, my guess is you need to remove that line of code from your description of what it does but I''m not certain.

If, after you''ve changed you code you are still getting the same error message you need examine the value you are passing in to the date paid parameter. What is currently happening is that the currently date is being passed as a string rather than a datetime and the framework can''t figure out how to transform the string into a .net datetime (this change of string to a different object type is called parsing). You need to work out why the parse is failing. There are lots of reasons this can happen e.g. the date is just invalid "32-Dec-2012", culture plays a part for example when an american formatted date is supplied date where a UK date is expected (e.g. 12/13/2012 for the 13^th December) or even the date being passed is null. In your case I''d google for "DateTime.Parse" and actually manually parse the string into a datetime object and pass that as the value not its string.

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