operand type clash: image is incompatable with varchar

[lidds]

I am adding a image to an SQL server, which works fine, however when I try to update the image in the database I get the following error:

operand type clash: image is incompatable with varchar

By the looks of things it happens when trying to update the image in the database, however if I am inserting a new entry then everything works OK. Can anyone tell me what I am doing wrong, below is my vb.net code and stored procedure. I am using oledb instead of sql due to the fact that it works with botj SQL server and Oracle.

VB.Net code

'Read image into byte 
                    ' save image to stream... 
                    Dim clsStream1 As New System.IO.MemoryStream
                    Me.picNewImage.Image.Save(clsStream1, System.Drawing.Imaging.ImageFormat.Jpeg)

                    ' read bytes from stream... 
                    Dim emptyByte As Byte = Nothing
                    Dim b As Byte() = DirectCast(Array.CreateInstance(GetType(Byte), clsStream1.Length), Byte())
                    clsStream1.Position = 0
                    clsStream1.Read(b, 0, b.Length)
                    clsStream1.Close()

                    Dim myCmd As New OleDb.OleDbCommand("spUpdateCommAndImg")
                    myCmd.CommandType = CommandType.StoredProcedure
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@ID", OleDb.OleDbType.VarChar)).Value = MRCSData.Instance.historyID
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commTag", OleDb.OleDbType.VarChar)).Value = strTag
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commTitle", OleDb.OleDbType.VarChar)).Value = strTitle
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commDisp", OleDb.OleDbType.VarChar)).Value = strDisp
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commType", OleDb.OleDbType.VarChar)).Value = strType
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commDate", OleDb.OleDbType.VarChar)).Value = strDeadline
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commArea", OleDb.OleDbType.VarChar)).Value = strArea
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commModRef", OleDb.OleDbType.VarChar)).Value = strModRef
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commOrig", OleDb.OleDbType.VarChar)).Value = strOrig
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commDesc", OleDb.OleDbType.VarChar)).Value = strDesc
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commAction", OleDb.OleDbType.VarChar)).Value = strAction
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commObj", OleDb.OleDbType.VarChar)).Value = strObjectives
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@modifyDate", OleDb.OleDbType.VarChar)).Value = Date.Now.ToString("dd/MM/yyyy hh:mm:ss tt")
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commImage", OleDb.OleDbType.LongVarBinary, b.Length, ParameterDirection.Input, False, emptyByte, emptyByte, "commImage", DataRowVersion.Current, b))
                    myCmd.Parameters.Add(New OleDb.OleDbParameter("@commWorkShare", OleDb.OleDbType.VarChar)).Value = strWorkShare

                    Me.lblStatus.Text = "Saving information to database, please wait..."
                    Me.progressStat.Value = 3
                    Try
                        myDB.DoMyUpdate(myCmd)
                    Catch ex As Exception
                        myDB.DisConnect()
                        MessageBox.Show(ex.Message)
                        Me.lblStatus.Text = "Ready..."
                        Me.progressStat.Value = 0
                    End Try

Stored procedure:

CREATE PROCEDURE [dbo].[spUpdateCommAndImg] @ID as varchar(4), @commTag as varChar(255), @commTitle as varchar(255), @commDisp as varchar(255),
@commType as varchar(255), @commDate as varchar(25), @commArea as varchar(100), @commModRef as varchar(100),
@commOrig as varchar(100), @commDesc as text, @commAction as text, @commObj as varchar(100), @modifyDate as varchar(50),@commWorkShare as varchar(100), @commImage as image
AS
UPDATE commentsTbl SET commTag=@commTag, commTitle=@commTitle, commDisp=@commDisp, commType=@commType, commDate=@commDate, commArea=@commArea,
commModRef=@commModRef, commOrig=@commOrig, commDesc=@commDesc, commAction=@commAction, commObj=@commObj, modifyDate=@modifyDate, workShare=@commWorkShare, commImage=@commImage  WHERE [ID]=@ID
GO

Thanks in advance

Simon

 

[jmcilhinney]

Firstly, you're really going about creating your parameters the long way. This:

myCmd.Parameters.Add(New OleDb.OleDbParameter("@commTag", OleDb.OleDbType.VarChar)).Value = strTag

could be replaced with this:

myCmd.Parameters.AddWithValue("@commTag", strTag)

Secondly, it is very bad form to store date and time data in text fields. SQL Server has a column type for dates and VB.NET has a type for dates, so what possible reason could you have for converting a Date to a String to store in a text field?

Thirdly, why are you using OleDb to access SQL Server? Unless you have a good reason to do otherwise you should be using SqlClient.

Finally, I fail to see why you're using that constructor to create your @commImage parameter. I would expect that AddWithValue would work just fine in that case too. The parameter type will be inferred from the type of the value. I'm not sure whether changing that will fix your problem but there's no point over-complicating things because you just introduce more places an error could creep in. If AddWithValue doesn't work then the most complex it should need to be would be:

myCmd.Parameters.Add("@commImage", OleDb.OleDbType.LongVarBinary).Value = b