How do I Insert Images into a SQL Server Table?

[JavaCowboy]

I have a table that has fields formatted as "image" with image data inside of them in SQL Server 2000. I would like to add an image of my own in the new record, but I can't figure out how. I'm unable to do it at the GUI Window.

How do I insert an image into this table? If I need to use and SQL statement, what is the syntax that I need to use?

 

[bassguy]

It is generally a bad Idea to try and store Blob data in a sql server...it is a better idea to store the path of an image in the table then link to the image in your presentation layer.

bassguy

 

[Guest_imported]

if you now VB here's some source code to help you ...
put it in a form, add some references, like 'ADODB' and "script_run_time", 2 buttons, and 3 textbox's and it may work.
do not forget to create the table in sqlserver, and to give the connection string!
****//******************


Option Explicit
Const BlocK = 1024 ' Get 1Kbytes

Private Sub Command1_Click()
Dim oCnn As New ADODB.Connection
On Error Resume Next
oCnn.ConnectionString = Text3
oCnn.Open

If SaveFileInSqlServer(Text1, oCnn) Then
MsgBox "OK", vbInformation
Else
MsgBox "ERROR", vbCritical
End If
End Sub

Function SaveFileInSqlServer(sFullPath As String, oCnn As ADODB.Connection) As Boolean
Dim oRs As New ADODB.Recordset
Dim oFs As New FileSystemObject
Dim DataFile() As Byte


On Error GoTo Error
SaveFileInSqlServer = False

If Not oFs.FileExists(sFullPath) Then
MsgBox "File doesn't exists!", vbCritical
Exit Function
End If
If oCnn.State = 0 Then
MsgBox "Connection isn't Active!", vbCritical
Exit Function
End If

oCnn.BeginTrans

oRs.Open "SELECT FILENAME, DATA FROM TAB_FILES", oCnn, adOpenStatic, adLockOptimistic, adCmdText

With oRs
If .EOF Then
.AddNew
.Fields("FILENAME&quot.Value = oFs.GetFileName(sFullPath)
Open sFullPath For Binary As #1
While Seek(1) < LOF(1)
Erase DataFile
If (LOF(1) - Seek(1)) < BlocK Then
ReDim DataFile(LOF(1) - Seek(1))
Else
ReDim DataFile(BlocK)
End If
Get #1, Seek(1), DataFile()
.Fields(&quot;DATA&quot.AppendChunk (DataFile)
Wend
.Update
Else
.Fields(&quot;FILENAME&quot.Value = oFs.GetFileName(sFullPath)
Open sFullPath For Binary As #1
While Seek(1) < LOF(1)
Erase DataFile
If (LOF(1) - Seek(1)) < BlocK Then
ReDim DataFile(LOF(1) - Seek(1))
Else
ReDim DataFile(BlocK)
End If
Get #1, Seek(1), DataFile()
.Fields(&quot;DATA&quot.AppendChunk (DataFile)
Wend
.Update
End If
End With

Close #1
oRs.Close
oCnn.CommitTrans

SaveFileInSqlServer = True
Exit Function
Error:
If Err.Number = -2147217865 Then _
MsgBox &quot;You have to create a table like this:&quot; & vbNewLine & _
&quot; CREATE TABLE [TAB_FILES] ( &quot; & vbNewLine & _
&quot; [FILENAME] [varchar] (500) NOT NULL , &quot; & vbNewLine & _
&quot; [DATA] [image] NOT NULL ) &quot; & vbNewLine & _
&quot; GO &quot;, vbExclamation
End Function


Function GetFileFromSqlServer(sFullPath As String, oCnn As ADODB.Connection) As Boolean
Dim oRs As New ADODB.Recordset
Dim oFs As New FileSystemObject
Dim DataFileVar As Variant
Dim DataFile() As Byte

On Error GoTo Error
GetFileFromSqlServer = False


If oCnn.State = 0 Then
MsgBox &quot;Connection isn't Active!&quot;, vbCritical
Exit Function
End If

oCnn.BeginTrans

oRs.Open &quot;SELECT FILENAME, DATA FROM TAB_FILES&quot;, oCnn, adOpenStatic, adLockOptimistic, adCmdText

With oRs
If Not .EOF Then
If Len(Trim$(.Fields(1).Value)) = 0 Then
.Close
Exit Function
End If
If oFs.FileExists(sFullPath) Then oFs.DeleteFile sFullPath, True
Open sFullPath For Binary As #1
While Not IsNull(DataFileVar)
Erase DataFile
DataFileVar = .Fields(1).GetChunk(BlocK)
If Not IsNull(DataFileVar) Then
DataFile = DataFileVar
Put #1, , DataFile
End If
Wend
Close #1
End If
End With

oRs.Close

GetFileFromSqlServer = True
Exit Function
Error:
If Err.Number = -2147217865 Then _
MsgBox &quot;You have to create a table like this:&quot; & vbNewLine & _
&quot; CREATE TABLE [TAB_FILES] ( &quot; & vbNewLine & _
&quot; [FILENAME] [varchar] (500) NOT NULL , &quot; & vbNewLine & _
&quot; [DATA] [image] NOT NULL ) &quot; & vbNewLine & _
&quot; GO &quot;, vbExclamation
End Function

Private Sub Command2_Click()
Dim oCnn As New ADODB.Connection
On Error Resume Next
oCnn.ConnectionString = Text3
oCnn.Open

If GetFileFromSqlServer(Text2, oCnn) Then
MsgBox &quot;OK&quot;, vbInformation
Else
MsgBox &quot;ERROR&quot;, vbCritical
End If
End Sub