I am experiencing the String was not recognized as a valid DateTime when inserting a record into SQL, from a website. This only happens maybe once out of 200 or so record inserts.
我遇到从网站将记录插入SQL时,字符串未被识别为有效的DateTime。这种情况可能只发生在200个左右的记录插入中。
Basically, the the site asks for a date input from the user and the date should always be in MM/DD/YYYY. I have put TextBox masks and Regular expressions in place to make sure of this, also having tested several times myself.
基本上,网站要求用户输入日期,日期应始终为MM / DD / YYYY。我已经将TextBox掩码和正则表达式放在适当位置以确保这一点,我自己也进行了多次测试。
However, there are still intermittent cases where the site throws the exception during SQL store.
但是,仍存在间歇性情况,即站点在SQL存储期间抛出异常。
My setup includes a web server and then a separate SQL server. (Maybe time difference between both?)
我的设置包括一个Web服务器,然后是一个单独的SQL服务器。 (也许两者之间的时差?)
My code for SQL insertion for the date input is as follows...
我输入日期输入的SQL代码如下...
sqlCmd.Parameters.AddWithValue("@PatientDateOfService", Convert.ToDateTime(PatientDateOfServiceLabel.Text));
I use this same template (and stored procedure on my SQL server) on other sites hosted on the same web server with no problem. The issue only occurs on one particular site. I got to thinking maybe this was a time-zone or culture related issue.
我在同一个Web服务器上托管的其他站点上使用相同的模板(以及我的SQL服务器上的存储过程),没有任何问题。该问题仅发生在一个特定站点上。我想这可能是一个时区或文化相关的问题。
After some extensive research, I was wondering if using the CultureInfo class may help me. I am pondering on the following change to my code and was wondering what others may think as well..
经过一番广泛的研究,我想知道使用CultureInfo课程是否可以帮助我。我正在考虑对我的代码进行以下更改,并且想知道其他人可能会想到什么......
// Call a new instance of the CultureInfo method and set to en-US
CultureInfo USCultureInfo = new CultureInfo("en-US");
// Insert the date with newly added CultureInfo instance
sqlCmd.Parameters.AddWithValue("@PatientDateOfService", Convert.ToDateTime(PatientDateOfServiceLabel.Text, USCultureInfo));
Might this help in resolving my issue?
这有助于解决我的问题吗?
-Thanks
1 个解决方案
#1
0
Although I cannot seem to find any articles to support this assertion, the AddWithValue method automatically converts strings to valid SQL-Server DateTime values. It is safer, however to explicitly denote the data type by using the Parameter.Add method instead, ex.
虽然我似乎无法找到任何支持此断言的文章,但AddWithValue方法会自动将字符串转换为有效的SQL-Server DateTime值。但更安全的是,通过使用Parameter.Add方法明确表示数据类型,例如。
sqlCmd.Parameters.Add(PatientDateOfServiceLabel.Text, SqlDbType.DateSqlDbType.Date);
#1
0
Although I cannot seem to find any articles to support this assertion, the AddWithValue method automatically converts strings to valid SQL-Server DateTime values. It is safer, however to explicitly denote the data type by using the Parameter.Add method instead, ex.
虽然我似乎无法找到任何支持此断言的文章,但AddWithValue方法会自动将字符串转换为有效的SQL-Server DateTime值。但更安全的是,通过使用Parameter.Add方法明确表示数据类型,例如。
sqlCmd.Parameters.Add(PatientDateOfServiceLabel.Text, SqlDbType.DateSqlDbType.Date);