bastanu
AWF VIP
- Local time
- Today, 00:02
- Joined
- Apr 13, 2010
- Messages
- 1,406
Hi all,
I am curious if anyone can spot anything wrong with the following code used to add files (PDFs, pictures, etc.) to a SQL table. Column is defined as varbinary(max). Same code used to work OK for both SQL server and Oracle back-ends. The records get created but the problem seems to be with strStream.Read as it returns Null. Just before that line I check the strStream.Size in the intermediate window and it returns the correct size for the picture file I am attempting to load. All records show null in that column and attempting to retrieve the file using stream.write fails.
Thanks!
Vlad
I am curious if anyone can spot anything wrong with the following code used to add files (PDFs, pictures, etc.) to a SQL table. Column is defined as varbinary(max). Same code used to work OK for both SQL server and Oracle back-ends. The records get created but the problem seems to be with strStream.Read as it returns Null. Just before that line I check the strStream.Size in the intermediate window and it returns the correct size for the picture file I am attempting to load. All records show null in that column and attempting to retrieve the file using stream.write fails.
Code:
Private Sub cmdAddFileToBlob_Click()
Dim sConnectionString As String, RS As ADODB.Recordset, cnnConnection As ADODB.Connection, sFileName As String
Dim strStream As ADODB.Stream, sFileType As String
Dim f As Object
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Show = True Then
sFileName = f.selecteditems(1)
Else
sFileName = ""
End If
On Error Resume Next
If sFileName = "" Then Exit Sub
sFileType = Mid(sFileName, InStrRev(sFileName, ".") + 1)
Set cnnConnection = New ADODB.Connection
Set RS = New ADODB.Recordset
sConnectionString = CurrentDb.TableDefs("tbl_ChapterFiles").Connect
sConnectionString = Replace(sConnectionString, "ODBC;", "")
cnnConnection.Open (sConnectionString)
RS.Open "Select * from tbl_ChapterFiles ", cnnConnection, adOpenKeyset, adLockOptimistic
Set strStream = New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
strStream.LoadFromFile sFileName
RS.AddNew
RS.Fields("ChapterFile").Value = strStream.Read
RS.Fields("ChapterID") = Me.ChapterID
RS.Fields("UploadedON") = Now()
RS.Fields("UploadedBY") = Forms![frmMainSwitchboard]![UserName]
RS.Fields("FILENAME") = StripLast(sFileName)
RS.Fields("FILETYPE") = sFileType
RS.Update
Set strStream = Nothing
RS.Close
cnnConnection.Close
Set cnnConnection = Nothing
Set RS = Nothing
Me.sfrmChapterFiles.Form.Requery
procExitSub:
Exit Sub
procNoPicture:
GoTo procExitSub
End Sub
Thanks!
Vlad