Storing documents on image fields using sql server 2005

Everytime I try to store a document on this field, I receive a weird e

Page 1 of 1

0 Replies - 6787 Views - Last Post: 18 October 2006 - 09:18 AM Rate Topic: -----

#1 dino_rpg  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 18-October 06

Storing documents on image fields using sql server 2005

Posted 18 October 2006 - 09:18 AM

Hi, I've this code which is part of a full developed application, it works pretty fine with SQL Server 2000, ORACLE and Access but everytime I tried to make it work with SQL Server 2005 it gives me the following error:
ErrCode = -2147467259
ErrDesc = [Microsoft][SQL Native Client]Invalid attribute value

the tricky thing is that the error is returned only if I use the SQL Native Client driver, because using the same SQL Server 2005 database but the SQL 2000 driver (SQL Server version: 2000.85.1117.00) the code works (guess what kind of driver has our client =D)

The error is returned no matther the type or size of the file I'm trying to store (I've tried with .jpg and .txt files, with sizes from 1kb up to 35kb)

The code is the following (I remove some unnecesary stuff like the specific connection parameters, the connection works very well, I can insert/update data and open recordsets without problems):

Public Function StoreDocument(ByVal oRecord As Object) As Boolean ' oRecord as ADODB.RecordSet
Dim lngOffSet As Long
Dim lngChunkSize As Long
Dim lngDocumentSize As Long
Dim Options As Long
Dim strSQL As String
Dim ConnectionString As String
Dim UserID As String
Dim PassWord As String
Dim varChunk As Variant
Dim cnnDocumentConnection As Object ' ADODB.Connection
Dim recDatos As Object ' ADODB.Recordset

On Error GoTo ErrHdlr
' This is a common Connection using ODBC, the parameters are a valid connection String in the form:
' "DSN=" & msODBC & ";UID=" & UserID & ";PWD=" & PassWord
' and Options is not set (-1 is the default value used in this case)
Set cnnDocumentConnection = CreateObject("ADODB.Connection")
Call cnnDocumentConnection.Open(ConnectionString, UserID, PassWord, Options)

' Opens the recordset to insert the document
Set recDatos = CreateObject("ADODB.RecordSet")
strSQL = "SELECT ID, DOCUMENTO FROM DOCUMENT_TABLE WHERE ID = 1"
Call recDatos.Open(strSQL, cnnDocumentConnection, adOpenDynamic, adLockOptimistic) ' adOpenKeyset
If recDatos Is Nothing Then
MsgBox "Error"
End
End If

If recDatos.EOF Then
' If no Record was found, we add a New record for inserting the DOCUMENT (which is the case with this error)
recDatos.AddNew
recDatos("ID") = 1
End If

' I've tried two approaches, either passing the IMAGE type field directly between recordsets (the data is on the source recordset)
' or using ADO's GetChunck and AppendChunck methods, but both approaches failed the same way
' Approach #1- Passing the whole field between recordsets
recDatos!DOCUMENTO = oRecord!DOCUMENTO

' Approach #2- Using GetChunck and AppendChunck
lngOffSet = 0
lngChunkSize = 100
lngDocumentSize = oRecord!DOCUMENTO.ActualSize
Do While lngOffSet < lngDocumentSize
varChunk = oRecord!DOCUMENTO.GetChunk(lngChunkSize)
lngOffSet = lngOffSet + lngChunkSize

recDatos!DOCUMENTO.AppendChunk varChunk
Loop

' Both methods fail here, giving the following error no matter what file type or file size I try to store:
' ErrCode = -2147467259
' ErrDesc = [Microsoft][SQL Native Client]Invalid attribute value
Call recDatos.Update

StoreDocument = True
Set recDatos = Nothing

ErrHdlr:
If Err.Number <> 0 Then
MsgBox Err.Desc
End If
End Function

Any help will be appreciated

Thanks

Is This A Good Question/Topic? 0
  • +

Page 1 of 1