日期转换错误 [英] Date Conversion error

问题描述

我正在使用Vb.net 2010.我编写了保存记录的代码。我使用Date Picker控件来记录日期。

  Dim  str  As  字符串 =   
 
  If （Saveflag =  保存）然后 
 str =  插入Employee_Detail值（& txtSrn.Text&  ，'& txtTitle.Text&  '，'& txtFullName.Text&  '，'& dtDOJ.Value&  '，& cmbEclass.SelectedValue&  ，& cmbDesignation.SelectedValue&  ，& cmbAppStatus.SelectedValue&  ，& cmbFaculty.SelectedValue&  ，& cmbCampus.SelectedValue&  ，& cmbInstitute.SelectedValue&  ，'& dtDOB.Value&  '，& txtYears.Text&  ，'& dtDORHR.Value&  '，'& dtDOREllu.Value&  '，& cmbPositionClass.SelectedValue&  ，& cmbDept.SelectedValue&  ，& cmbSalaryGrade.SelectedValue&  ，& cmbScale.SelectedValue&  ，& txtAssnSalary.Text&  ，'& dtConfirmationDate.Value&  '，'& txtNorms.Text&  '，& txtSymbiExp.Text&  ，& txtIndusExp.Text&  ，& txtTeachingExp.Text&  ，& cmbWorkStatus.SelectedValue&  ，'& txtPan.Text&  '）&   
 cnt = cnt +  1  
 CurRec = cnt 
 
 如果（obj.SetQuery（str）=  True ）然后 
 MessageBox.Show（ 一条记录& Saveflag&  成功，< span class =code-string>  Employee_Detail，MessageBoxButtons.OK，MessageBoxIcon.Information）
 LockButton（ True ）
 LockText（ True ）

  Dim  cmd  As  新 SqlCommand（）
  Dim  con  As  新 SqlConnection 
  Dim 模式 As   Boolean  
 con = SetCon（）
 cmd.CommandText = strQuery 
 cmd。 Connection = con 
 如果 cmd.ExecuteNonQuery>  0  然后 
模式= 真 
 否则 
模式= 错误 
 结束 如果 
 con.Close（）
 返回模式

系统抛出错误：从字符串转换日期和/或时间时转换失败。 forcmd.CommandText = strQuery这句话

解决方案

好吧，是的，它可能会。

不要连接用于构建SQL命令的字符串。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。

它们使您的代码更具可读性，更可靠，并且它们阻止我通过在您的应用程序中输入来破坏您的数据库...



此外，列出您要插入的字段 - 再次，它使您的代码更可靠，并且它更安全。

 使用 con 作为 新 SqlConnection（strConnect） 
 con.Open（）
 使用 com 作为 新 SqlCommand（  INSERT INTO myTable（myColumn1，myColumn2）VALUES（@ C1，@ C2），con）
 com.Parameters.AddWithValue（  @ C1，myValueForColumn1）
 com.Parameters.AddWithValue（  @ C2，myValueForColumn2）
 com.ExecuteNonQuery（）
 结束 使用 
 结束 使用 
 

然后，使用DateTime.TryParse将日期转换为DateTime值，并在尝试插入记录之前向用户报告任何问题 - 这样你就可以给他们一个更准确的错误，帮助他们做对。然后你将DateTime值直接传递给SQL，这样就没有更多的转换可以了，而且一切正常。



它改进了你的代码，摆脱了你的问题，未来的问题还有很多......





我还不清楚，你还想说什么。你可以在我的代码中向我展示一个示例吗？



我不会坐在这里将所有难以阅读的代码转换为正确的代码代码：我没用它，所以我不会花时间在它上面。

但是......上面的代码，如果写在你的代码中将是：

 str =  插入Employee_Detail值（& ; myValueForColumn1&  ，'& myValueForColumn2&  '） 

所以你应该可以从中解决这个问题。

I am using Vb.net 2010. I written code to SAVE the records.I used Date Picker control to record dates.

Dim str As String = ""

If (Saveflag = "Save") Then
    str = "Insert Into Employee_Detail values(" & txtSrn.Text & ",'" & txtTitle.Text & "','" & txtFullName.Text & "','" & dtDOJ.Value & "'," & cmbEclass.SelectedValue & "," & cmbDesignation.SelectedValue & "," & cmbAppStatus.SelectedValue & "," & cmbFaculty.SelectedValue & "," & cmbCampus.SelectedValue & "," & cmbInstitute.SelectedValue & ",'" & dtDOB.Value & "'," & txtYears.Text & ",'" & dtDORHR.Value & "','" & dtDOREllu.Value & "'," & cmbPositionClass.SelectedValue & "," & cmbDept.SelectedValue & "," & cmbSalaryGrade.SelectedValue & "," & cmbScale.SelectedValue & "," & txtAssnSalary.Text & ",'" & dtConfirmationDate.Value & "','" & txtNorms.Text & "'," & txtSymbiExp.Text & "," & txtIndusExp.Text & "," & txtTeachingExp.Text & "," & cmbWorkStatus.SelectedValue & ",'" & txtPan.Text & "')" & ""
    cnt = cnt + 1
    CurRec = cnt

If (obj.SetQuery(str) = True) Then
    MessageBox.Show("One Record" & Saveflag & " Successfully", "Employee_Detail", MessageBoxButtons.OK, MessageBoxIcon.Information)
    LockButton(True)
    LockText(True)

Dim cmd As New SqlCommand()
        Dim con As New SqlConnection
        Dim Mode As Boolean
        con = SetCon()
        cmd.CommandText = strQuery
        cmd.Connection = con
        If cmd.ExecuteNonQuery > 0 Then
            Mode = True
        Else
            Mode = False
        End If
        con.Close()
        Return Mode

System throws an error: "Conversion failed when converting date and/or time from character string." for "cmd.CommandText = strQuery" this statement

解决方案

Well, yes, it probably will.
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
They make your code more readable, are more reliable, and they prevent me from destroying your database by typing into your application...

In addition, list the fields you are trying to insert into - again, it makes your code more reliable, and it's a lot safer.

Using con As New SqlConnection(strConnect)
    con.Open()
    Using com As New SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con)
        com.Parameters.AddWithValue("@C1", myValueForColumn1)
        com.Parameters.AddWithValue("@C2", myValueForColumn2)
        com.ExecuteNonQuery()
    End Using
End Using

Then, convert your dates to DateTime values using DateTime.TryParse and report any problems to the user before you even try inserting a record - that way you can give them a more accurate error which helps them get it right. You then pass the DateTime values directly to SQL so there is no more conversion to do and it all works.

It improves your code, gets rid of your problem and a number of future problems as well...


"Still I am not clear, what are you trying to say. Can you show me example in my code?"

I'm not going to sit here and convert all that hard-to-read code to proper code: I have no use for it, so I'm not going to spend my time on it.
But...The code above, if written in your code would be:

str = "Insert Into Employee_Detail values("& myValueForColumn1 & ",'" & myValueForColumn2 & "')"

So you should be able to work it out from that.

这篇关于日期转换错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！