将Picturebox中的图像与SQL Image数据类型进行比较

Question

我有一段代码，可以将Picturebox中的图像（在VB6中）保存到数据类型为Image的SQL数据库中，以下是输出结果：

Column Name = Picture

我的问题是如何将这里的图片

与我的SQL数据库进行比较？ 我的目标是检查image3中的图像是否存在于我的数据库中。

这是我的代码，但它没有起作用。

Dim arrImageByte() As Byte
Dim strPhotoPath As String
strPhotoPath = Image3.Picture & ".jpg"
Set rs = New ADODB.Recordset

Open strPhotoPath For Binary As #1
ReDim arrImageByte(FileLen(strPhotoPath))
        fNum = FreeFile()
        Open strPhotoPath For Binary As #fNum
        Get #fNum, , arrImageByte
        Close fNum

   Text1.Text = FreeFile
   rs.Open "select * from tbl_image with (nolock) where CONVERT(varbinary,[picture]) = '" & Text1.Text & "'", sql, 1, 1, 1


If rs.RecordCount = 0 Then
   MsgBox "Image exist"
Else
   MsgBox "Image does not exist."
End If

我认为最好的方法是将image3转换成二进制（Picture Column），然后执行选择命令。

请帮帮我，谢谢。

非常感谢！

- Shadow Fiend

---

你尝试过使用存储过程来实现吗？这样你可能会修剪你的消息等。 - Selim Balci

Answer 1

这应该可以工作了，我做了一些更改，希望这能帮到你。

1. I am using a command as I never trust the data to be safe, even if only I will ever use the program
2. I removed WITH(NOLOCK) this is bad for indexes and can decrease performance.

3. changed your if statement to be >0 instead of =0 because if you got results it would show image does not exist.

   Dim arrImageByte() As Byte
   Dim strPhotoPath As String
   strPhotoPath = Image3.Picture & ".jpg"
   Set rs = New ADODB.Recordset
   
   Open strPhotoPath For Binary As #1
   ReDim arrImageByte(FileLen(strPhotoPath))
           fNum = FreeFile()
           Open strPhotoPath For Binary As #fNum
           Get #fNum, , arrImageByte
           Close fNum
   
      Text1.Text = FreeFile
      Set cmd = New ADODB.Command
      cmd.ActiveConnection = sql
      cmd.CommandText ="SELECT * FROM tbl_image where " & _ 
          "CONVERT(varbinary,[picture]) = CONVERT(varbinary,?)"
      cmd.Parameters(1)=Text1.Text
      rs = cmd.Execute()
   'Change this
   If rs.RecordCount > 0 Then 'instead of =
      MsgBox "Image exist"
   Else
      MsgBox "Image does not exist."
   End If
   

- Jpsh