Saving an image in SQL error 1

[Malchik]

Hi,

I am trying to save images in an sql table and then use it on a CrystalReport. Here is the code I am using. The system gives me an error : Incorrect syntax near 'sp_sp_InsertImage' I double checked my parameters and field. I am pretty sure it is the way I call the SP... could anyone help me on this. Thanks

Table
Shift VARCHAR(10)
Batch SMALLINT
Seq SMALLINT
COF VARBINARY(8000)
UID BIGINT

Code:
Dim bCOF As Byte()
Dim sPath As String = "\\imgSvrSecure1\Images\"
Dim cmd As Data.SqlClient.SqlCommand
Dim cnn As data.SqlClient.SqlConnection

cnn = New data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings.Item("MyODBC").ToString)

If Dir(sPath & "D989-009320.tif" ) <> "" Then
bCOF = GetImageInBytes(sPath & "D989-009320.tif" )
End If

cmd = New data.SqlClient.SqlCommand("sp_InsertImage ", cnn)
cmd.Parameters.AddWithValue("@Shift", 'F009x78')
cmd.Parameters.AddWithValue("@Batch", 1)
cmd.Parameters.AddWithValue("@Seq", 1)
cmd.Parameters.AddWithValue("@COF", bCOF)
cmd.Parameters.AddWithValue("@UID", Session("LOGINID"))

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
cmd = Nothing
cnn = Nothing



Here is the function:
Private Function GetImageInBytes(ByVal sFile As String) As Byte()
Dim FS As IO.FileStream
Dim BR As IO.BinaryReader
FS = New IO.FileStream(sFile, IO.FileMode.Open, IO.FileAccess.Read)
BR = New IO.BinaryReader(FS)
GetImageInBytes = BR.ReadBytes(FS.Length)
BR.Close()
FS.Close()

FS = Nothing
BR = Nothing
End Function


Mal'chik

 

[jbenson001]

Try adding this:
cmd.CommandType = Data.CommandType.StoredProcedure

Also, It is NOT a good idea to prefix your stored procedure names with "sp_".
Behind the scenes in sql server, it will look in the master db for that stored procedure name, and then in the db you are actually in. This cause overhead, and can cause problems if you name your sp with a system sp name. "sp_" is reserved for system stored procedures.

 

[Malchik]

Thanks for the info. You are right about the sp_, my mistake

Mal'chik

 

[jbenson001]

No problem.. You'd be surprise at how many people do that because of what they see in the db. But most do not realize the overhead and other issuse that can arise.