Storing images in Microsoft SQL 2000 using Stored Procedures

First of all, you are going to get many comments here, as to why this (storing BLOB's in a table) is a bad idea and that you should instead store the paths to the files in the table.  However, I suspect you have already balanced the pros and cons for this and from personal experience, I can tell you that there are some times that it is advantageous to take this approach.  

Having said that, I think I understand your problem exactly, but need to know a few things:
1.  I have always used an Image column and not a varbinary.
2. Try posting all the relevant code and I will try and duplicate the problem.

Anthony

Anthony, many thanks for not patronising with the storing of a file path!  We have been using that for some time but accessibility and maintainability wise, it just doesn't work for us.  We have read so many articles about the storage of photographs in the database, but I agree with you, there are occasions when this is necessary and we feel that this is one of them.   The database has the data type image, using the data conversion types between SQL data types and ADO, it is suggested that VarBinary is the equivalent.

Here is the ASP code we are using, ignore the lack of dimensions and setting variable back to nothing, just trying to keep it simple:
<%

Set objConn = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")

objConn.Provider = "SQLOLEDB"
objConn.ConnectionString = "Provider=sqloledb; Network Library=DBMSSOCN; Data Source=DBServer; Initial Catalog=DBName; User ID=sa; Password=password"
objConn.Open

objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "StoredProcName"

Set objCmd.ActiveConnection = objConn

set fs = server.CreateObject("adodb.Stream")      
fs.Type = 1 'Binary
fs.Open
fs.LoadFromFile  "Path to file to be imported"

objCmd.Parameters.Append objCmd.CreateParameter("binData", adVarBinary, adParamInput, 16, fs.Read)
objCmd.Parameters.Append objCmd.CreateParameter("RefNo", adinteger, adParamInput, , 1)
      
objCmd.Execute

'If you rem out the above three lines and 'unrem' the ones below, you get the image from the file displayed
'Response.Expires = 0
'Response.Buffer = TRUE
'Response.Clear
'Response.ContentType = "image/gif"
'Response.BinaryWrite fs.read

%>

I will take a look at your code at home tonight.

Anthony

Many thanks
Simon

One additional thing I noticed:
You are not closing the Stream object (or setting it to Nothing for that matter).  This is unrelated to the problem but could cause you some grief, if you try and delete the file after importing it into SQL Server.

The same can be said about the Connection object although may be later in the code.

So your code should look something like this:
<%

Set objConn = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")

objConn.Provider = "SQLOLEDB"
objConn.ConnectionString = "Provider=sqloledb; Network Library=DBMSSOCN; Data Source=DBServer; Initial Catalog=DBName; User ID=sa; Password=password"
objConn.Open

objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "StoredProcName"

Set objCmd.ActiveConnection = objConn

set fs = server.CreateObject("adodb.Stream")    
fs.Type = adTypeBinary                                         '<------- Change this line
fs.Open
fs.LoadFromFile  "Path to file to be imported"

objCmd.Parameters.Append objCmd.CreateParameter("binData", adVarBinary, adParamInput, fs.Size, fs.Read)
objCmd.Parameters.Append objCmd.CreateParameter("RefNo", adinteger, adParamInput, , 1)
   
objCmd.Execute, , adExecuteNoRecords    '<------- Change this line
Set objCmd = nothing                                '<------- Add this line

'If you rem out the above three lines and 'unrem' the ones below, you get the image from the file displayed
Response.Expires = 0
Response.Buffer = TRUE
Response.Clear
Response.ContentType = "image/gif"
Response.BinaryWrite fs.read

fs.close                                          '<------- Add this line
Set fs = Nothing                            '<------- Add this line

objConn.Close                               '<------- Add this line
Set objConn = Nothing                  '<------- Add this line

%>

Anthony

One final thing:
You may need to reset the pointer of the Stream object prior to doing a second read, as in:
fs.Position = 0
Response.BinaryWrite fs.read


Anthony

Anthony, you're a star, it's funny how it always ends up being simple things, but it's the not being able to see the wood for the trees.  All the other bits you've suggested, I just stripped out to make the code look easier to read.  Although I didn't really know about the adExecuteNoRecords option, well worth knowing.

Many thanks for your help, much appreciated.

Regards
Simon.