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(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(cb_DoctorID.SelectedValue)
List_.Add(txt_HospitalNumber.Text)
List_.Add(txt_PreviousReportNumber.Text)
List_.Add(txt_ReceivedDate.Text)
List_.Add(txt_ReportedDate.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]='{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
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.
Submit an article or tip