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

2 Replies - 8689 Views - Last Post: 13 December 2006 - 09:11 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 27 November 2006 - 08:27 AM

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 :unsure:)

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

	' 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

Is This A Good Question/Topic? 1

Replies To: Storing documents on image fields using sql server 2005

#2 KeyWiz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 8
  • View blog
  • Posts: 438
  • Joined: 26-October 06

Re: Storing documents on image fields using sql server 2005

Posted 28 November 2006 - 05:30 PM

I believe the problem is in your sql string.

I have found in the past that to retrieve as you are attempting, I had to create a varriable as Long
and use that in the sql statement.

Dim myVar as Long 
myVar = 1

''' lots of code '''

strSQL = "SELECT ID, DOCUMENTO FROM DOCUMENT_TABLE WHERE ID = " & myVar
'							P.S. don't leave out this space ^



The code may be interpreting the 1 as a character in your string, while adding the Long Variable to the end of the
string causes it to be calculated as a value.

This post has been edited by KeyWiz: 28 November 2006 - 05:40 PM

Was This Post Helpful? 0
  • +
  • -

#3 dino_rpg  Icon User is offline

  • New D.I.C Head

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

Re: Storing documents on image fields using sql server 2005

Posted 13 December 2006 - 09:11 AM

Sorry for not replaying earlier (lot of work)... I did tried your suggestion, but unfortunately it didn't work either... thanks anyway

But I've good news :), 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

Public Const adUseClient = 3

...

recDatos.CursorLocation = adUseClient


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1