I'm sorry if I'm breaking any rules, but I'm a newbie and I just found out today that I've to put my code using the Code tags because otherwise I might not receive any help (that, and the fact I didn't find any way to edit my original post, pretty weird, I'm used to a lot of forums but I didn't see any way to do it here
)
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):
CODE
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
' This is the final part of the function
StoreDocument = True
Set recDatos = Nothing
ErrHdlr:
If Err.Number <> 0 Then
MsgBox Err.Desc
End If
End Function
Any help will be appreciated
Thanks
, I searched the Web today and found a post made yesterday on the MSDN forums that just give the answer to this problem, you've to change the default CursorLocation on the RecordSet from adUseServer to adUseClient and that's it, I can store any type of Document now using both drivers
| 