在VB.NET中使用参数时，条件表达式中的数据类型不匹配 [英] Data type mismatch in criteria expression when using parameters in VB.NET
本文介绍了在VB.NET中使用参数时，条件表达式中的数据类型不匹配的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要在数据库中插入一些字符串，二进制数据和整数。其中一些字符串包含像'（撇号）这样的常量。所以我想我可以插入那些使用参数。当使用下面的代码时，我的标准表达式中的数据类型不匹配错误。
I need to insert some strings, binary data and integers into database. Some of those strings contains the constants like ' (apostrophe). So i thought i can insert those using parameters. When using the code below i had "Data Type Mismatch in Criteria Expression" Error.
Function GetSiteValue() As String
Dim r As String = ""
If ls_Site.Items.Count > 0 Then
For i As Integer = 0 To ls_Site.Items.Count - 1
If i = 0 Then
r = ls_Site.Items(0).ToString
ElseIf i > 0 Then
r &= ";" & ls_Site.Items(i).ToString
End If
Next
End If
Return r
End Function
Function GetDiagnosticsValue() As String
Dim r As String = ""
If ls_Diagnostics.Items.Count > 0 Then
For i As Integer = 0 To ls_Diagnostics.Items.Count - 1
If i = 0 Then
r = ls_Diagnostics.Items(0).ToString
ElseIf i > 0 Then
r &= ";" & ls_Diagnostics.Items(i).ToString
End If
Next
End If
Return r
End Function
Function GetDetails() As Object()
Dim ro As New Object()
Dim List_ As New List(Of Object)
List_.Add(txt_ReportNumber.Text.ToUpper.Replace("-", "/"))
List_.Add(txt_SurName.Text)
List_.Add(txt_PatientName.Text)
List_.Add(txt_Age.Text)
List_.Add(txt_Gender.Text)
List_.Add(txt_Test.SelectedValue)
'List_.Add(GetSiteValue)
List_.Add(txt_AddressLine1.Text)
List_.Add(txt_AddressLine2.Text)
List_.Add(txt_City.Text)
List_.Add(txt_State.Text)
List_.Add(txt_Mobile.Text)
List_.Add(txt_Email.Text)
'List_.Add(GetDiagnosticsValue)
List_.Add(cb_DoctorID.SelectedValue)
List_.Add(txt_HospitalNumber.Text)
List_.Add(txt_PreviousReportNumber.Text)
List_.Add(txt_ReceivedDate.Text)
List_.Add(txt_ReportedDate.Text)
'List_.Add(txt_Report.Text)
'List_.Add(txt_Notes.Text)
If ReportImage1.SelectedImageValidity = True Then
List_.Add(ReportImage1.SelectedName)
Else
List_.Add("")
End If
If ReportImage2.SelectedImageValidity = True Then
List_.Add(ReportImage2.SelectedName)
Else
List_.Add("")
End If
If ReportImage3.SelectedImageValidity = True Then
List_.Add(ReportImage3.SelectedName)
Else
List_.Add("")
End If
If ReportImage4.SelectedImageValidity = True Then
List_.Add(ReportImage4.SelectedName)
Else
List_.Add("")
End If
Return List_.ToArray
End Function
Private Sub btn_OK_Click(sender As Object, e As EventArgs) Handles btn_OK.Click
cnnOLEDB.Open()
Dim Checkup As Object() = CheckFields()
If Checkup(0) = True Then
Dim im1, im2, im3, im4, emptyimg As New MemoryStream()
My.Resources.Empty.Save(emptyimg, Imaging.ImageFormat.Bmp)
If ReportImage1.SelectedImageValidity = True Then
ReportImage1.SelectedImage.Save(im1, Imaging.ImageFormat.Bmp)
End If
If ReportImage2.SelectedImageValidity = True Then
ReportImage2.SelectedImage.Save(im2, Imaging.ImageFormat.Bmp)
End If
If ReportImage3.SelectedImageValidity = True Then
ReportImage3.SelectedImage.Save(im3, Imaging.ImageFormat.Bmp)
End If
If ReportImage4.SelectedImageValidity = True Then
ReportImage4.SelectedImage.Save(im4, Imaging.ImageFormat.Bmp)
End If
Dim imdata1 As Byte() = im1.GetBuffer()
Dim imdata2 As Byte() = im2.GetBuffer()
Dim imdata3 As Byte() = im3.GetBuffer()
Dim imdata4 As Byte() = im4.GetBuffer()
Dim empty As Byte() = emptyimg.GetBuffer()
cmdOLEDB.Connection = cnnOLEDB
Dim Objects As Object() = GetDetails()
'cmdOLEDB.CommandText = String.Format("UPDATE PATIENT SET [Report Number]='{0}',[Sur Name]='{1}',[Patient Name]='{2}',[Age]='{3}',[Gender]='{4}',[Test]='{5}',[Site]=@site,[Address Line 1]='{7}',[Address Line 2]='{8}',[City]='{9}',[State]='{10}',[Mobile]='{11}',[E Mail]='{12}',[Diagnostics]=@diag,[Doctor ID]='{14}',[Hospital Number]='{15}',[Previous Report Number]='{16}',[Received Date]='{17}',[Reported Date]='{18}',[Report Result]=@result,[Notes]=@notes,[Image 1]=@image1,[Image 1 Name]='{21}',[Image 2]=@image2,[Image 2 Name]='{22}',[Image 3]=@image3,[Image 3 Name]='{23}',[Image 4]=@image4,[Image 4 Name]='{24}' WHERE [ID]=" & PatientID & ";", Objects)
cmdOLEDB.CommandText = String.Format("UPDATE PATIENT SET [Report Number]='{0}',[Sur Name]='{1}',[Patient Name]='{2}',[Age]='{3}',[Gender]='{4}',[Test]='{5}',[Site]=@site,[Address Line 1]='{6}',[Address Line 2]='{7}',[City]='{8}',[State]='{9}',[Mobile]='{10}',[E Mail]='{11}',[Diagnostics]=@diag,[Doctor ID]='{12}',[Hospital Number]='{13}',[Previous Report Number]='{14}',[Received Date]='{15}',[Reported Date]='{16}',[Report Result]=@result,[Notes]=@notes,[Image 1]=@image1,[Image 1 Name]='{17}',[Image 2]=@image2,[Image 2 Name]='{18}',[Image 3]=@image3,[Image 3 Name]='{19}',[Image 4]=@image4,[Image 4 Name]='{20}' WHERE [ID]=" & PatientID & ";", Objects)
cmdOLEDB.CommandType = CommandType.Text
Dim p4 As New OleDbParameter("@image1", OleDb.OleDbType.Binary)
Dim p3 As New OleDbParameter("@image2", OleDb.OleDbType.Binary)
Dim p2 As New OleDbParameter("@image3", OleDb.OleDbType.Binary)
Dim p1 As New OleDbParameter("@image4", OleDb.OleDbType.Binary)
Dim site As New OleDbParameter("@site", GetSiteValue())
Dim diag As New OleDbParameter("@diag", GetDiagnosticsValue())
Dim result As New OleDbParameter("@result", txt_Report.Text)
Dim notes As New OleDbParameter("@notes", txt_Notes.Text)
If ReportImage1.SelectedImageValidity = True Then
p4.Value = imdata1
cmdOLEDB.Parameters.Add(p4)
Else
p4.Value = empty
cmdOLEDB.Parameters.Add(p4)
End If
If ReportImage1.SelectedImageValidity = True Then
p3.Value = imdata2
cmdOLEDB.Parameters.Add(p3)
Else
p3.Value = empty
cmdOLEDB.Parameters.Add(p3)
End If
If ReportImage1.SelectedImageValidity = True Then
p2.Value = imdata3
cmdOLEDB.Parameters.Add(p2)
Else
p2.Value = empty
cmdOLEDB.Parameters.Add(p2)
End If
If ReportImage1.SelectedImageValidity = True Then
p1.Value = imdata4
cmdOLEDB.Parameters.Add(p1)
Else
p1.Value = empty
cmdOLEDB.Parameters.Add(p1)
End If
cmdOLEDB.Parameters.AddRange({site, diag, result, notes})
cmdOLEDB.ExecuteNonQuery()
MsgBox("Patient Details Successfully Updated!", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Done :-)")
Me.DialogResult = DialogResult.OK
Me.Close()
Else
MsgBox(Checkup(1), MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Error :-(")
End If
cnnOLEDB.Close()
End Sub
我尝试过：
首先，我使用String.Fomat插入字符串和整数，参数添加二进制数据。它一直运行良好，直到字符串包含常量。所以我选择参数来添加所有信息。
What I have tried:
First i used String.Fomat to insert strings and integers, parameters to add binary data. It worked well until the strings contains the constants. So i choose parameters to add all informations.
推荐答案
你的代码可以通过重写来简化
Your code can be simplified by rewriting
If ls_Site.Items.Count > 0 Then
For i As Integer = 0 To ls_Site.Items.Count - 1
If i = 0 Then
r = ls_Site.Items(0).ToString
ElseIf i > 0 Then
r &= ";" & ls_Site.Items(i).ToString
End If
Next
End If
with
with
If ls_Site.Items.Count > 0 Then
r = ls_Site.Items(0).ToString
For i As Integer = 1 To ls_Site.Items.Count - 1
r &= ";" & ls_Site.Items(i).ToString
Next
End If
您应该学会尽快使用调试器。而不是猜测你的代码在做什么，现在是时候看到你的代码执行并确保它完成你期望的。
调试器允许你跟踪执行逐行检查变量，你会看到它有一个停止做你期望的点。
调试器 - 维基百科，免费的百科全书 [ ^ ]
掌握Visual Studio 2010中的调试 - A初学者指南 [ ^ ]
使用调试器，检查失败条件中使用的变量值。
You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.
The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
With the debugger, check the variables values used in the condition that fail.
这篇关于在VB.NET中使用参数时，条件表达式中的数据类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
