Searching Sql Table for Image using byte array parameter
-
Hi All Im trying desparately to search a sql table image field passing in a parameter which is a byte array. I am having no luck whatsoever. I get this error when i run my code below :The data types image and image are incompatible in the equal to operator.
Public Sub SearchByteArray(ByVal ByteArr() As Byte) Dim strSQL As String Dim oConn As SqlConnection Dim oCmd As SqlCommand Dim oBLOBParam As SqlParameter Dim str As String = "" ' Try ' Create and open connection object oConn = New SqlConnection("data source=XXX;initial catalog=CustomApps;user id=sa;pwd=xxx;") oConn.Open() ' Insert statement ' Notice that @BLOBValue is a placeholder for the actual data strSQL = "SELECT BlobField from MyImageTable where BlobField = (@BLOBValue)" ' Create a command object oCmd = oConn.CreateCommand() ' Set SQL statement oCmd.CommandText = strSQL ' Create a command parameter oBLOBParam = New SqlParameter("@BLOBValue", SqlDbType.Image, ByteArr.Length, ParameterDirection.Input) ' Finally, set the actual data oBLOBParam.Value = ByteArr ' Add this parameter to the command oCmd.Parameters.Add(oBLOBParam) ' Execute SQL statement MessageBox.Show(oCmd.ExecuteNonQuery()) ' Close the connection oConn.Close() End SubCan anyone help or assist please ? -
Hi All Im trying desparately to search a sql table image field passing in a parameter which is a byte array. I am having no luck whatsoever. I get this error when i run my code below :The data types image and image are incompatible in the equal to operator.
Public Sub SearchByteArray(ByVal ByteArr() As Byte) Dim strSQL As String Dim oConn As SqlConnection Dim oCmd As SqlCommand Dim oBLOBParam As SqlParameter Dim str As String = "" ' Try ' Create and open connection object oConn = New SqlConnection("data source=XXX;initial catalog=CustomApps;user id=sa;pwd=xxx;") oConn.Open() ' Insert statement ' Notice that @BLOBValue is a placeholder for the actual data strSQL = "SELECT BlobField from MyImageTable where BlobField = (@BLOBValue)" ' Create a command object oCmd = oConn.CreateCommand() ' Set SQL statement oCmd.CommandText = strSQL ' Create a command parameter oBLOBParam = New SqlParameter("@BLOBValue", SqlDbType.Image, ByteArr.Length, ParameterDirection.Input) ' Finally, set the actual data oBLOBParam.Value = ByteArr ' Add this parameter to the command oCmd.Parameters.Add(oBLOBParam) ' Execute SQL statement MessageBox.Show(oCmd.ExecuteNonQuery()) ' Close the connection oConn.Close() End SubCan anyone help or assist please ?Hi, I am not an SQL or database expert but I would be very surprised if SQL could do record selection based on BLOB content. I do have one suggestion: you could add an 'imageHash' field to your table, then with an app, calculate and store the image hash value one. Any algorithm would be fine. Later on, when you want to search for an image, calculate its hash value, then search for the hash value. NB: there is a slim chance a hash search may result in more than one match, so you would still need code to compare the bytes if you want to be absolutely sure you got the right image. :)
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets