Copy Db Image from one table to another

[ddrandy]

I am having Trouble coping an image type datafield from on table to another. I am using the following code but receive and error on the insert. I am user SQL Server 2000.

Any suggestions would be greatly appreciated.

Thanks

copysrccmd.CommandText = "SELECT * FROM tbl1 where fld1 = '" & LSTCLASS.List(idx) & "'"
copysrccmd.CommandType = adCmdText
Set copysrcrs = copysrccmd.Execute

Const conChunkSize = 100
VbQuote = Chr(34)

Dim ImgOffset As Long
Dim ImgLogoSize As Long
Dim varImg As Variant
Dim varChunk As Variant
Dim SQLString As String
ImgLogoSize = copysrcrs("Properties").ActualSize
Do While ImgOffset < ImgLogoSize
varChunk = copysrcrs("Properties").GetChunk (conChunkSize)
varImg = varImg & varChunk
ImgOffset = ImgOffset + conChunkSize
Loop


SQLString = "INSERT INTO tbl2 values ('" & copysrcrs.Fields(0).Value & "','" & _
copysrcrs.Fields(1).Value & "','" & varImg & "')"

copydestcmd.CommandText = SQLString
copydestcmd.CommandType = adCmdText
copydestcmd.Execute

 

[bjd4jc]

Since you didn't post what error you were recieving, here is a stabb in the dark...

Change Your Code to Something Like this. I have never actually done this so it may not be completely right...

Dim parm1 As ADODB.Parameter
Dim parm2 As ADODB.Parameter
Dim parm3 As ADODB.Parameter

dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO tbl2 values (?, ?, ?)" 

Set parm1 = New ADODB.Parameter
parm1.Type = adVarChar 'whatever type this is
parm1.Size = 10 'however big it is (if varchar or binary)
parm1.Name = "Field0"
cmd.Parameters.Append parm1

Set parm2 = New ADODB.Parameter
parm2.Type = adVarchar
parm2.Size = 10
parm2.Name = "Field1"
cmd.Parameters.Append Parm2

Set parm3 = New ADODB.Parameter
parm3.Type = adLongVarBinary
parm3.Name = "@img"
parm3.Size = 16

Do While ImgOffset < ImgLogoSize
     parm3.AppendChunk copysrcrs("Properties").GetChunk     (conChunkSize)
     varImg = varImg & varChunk
     ImgOffset = ImgOffset + conChunkSize
Loop

cmd.Parameters.Append Parm3

cmd.Execute

Like I said, I have actually never done this before and I am sure the above code has an error or two as I just quick typed it into here. It would be nice to know what the error is and what the other fields are, etc. You didn't give a whole lot of detail... which is probably what you hadn't got a response up til now... see faq222-2244

 

[garths2]

Here is how I would do it. I would use two recordsets. You also need to use labels when referencing fields because the order is not predictable when using "SELECT *"

' begin code

Dim fnum As Integer
Dim bytesLeft As Long
Dim bytes As Long
Dim tmp() As Byte
Dim readRS As New ADODB.Recordset
Dim writeRS As New ADODB.Recordset
Dim sql2 As String
Dim sql As String
Dim conn As New ADODB.Connection
Const ChunkSize = 512000


sql = "SELECT * FROM tbl1 where fld1 = '" & LSTCLASS.List(idx) & "'"


readRS.CursorLocation = adUseClient
readRS.Open sql, conn, adOpenStatic, adLockReadOnly, adCmdText
writeRS.Open "tbl2", conn, adOpenDynamic, adLockPessimistic, adCmdTable


If readRS.Fields("Properties").Type = adLongVarBinary Then
If Err.Number = 0 Then
If (readRS.Fields("Properties").Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "Field does not support the GetChunk method."
End If
bytesLeft = readRS.Fields("Properties").ActualSize
writeRS.AddNew
Do While bytesLeft
bytes = bytesLeft
If bytes > ChunkSize Then bytes = ChunkSize
tmp = readRS.Fields("Properties").GetChunk(bytes)
writeRS.Fields("Properties").AppendChunk tmp
bytesLeft = bytesLeft - bytes
Loop
Close #fnum
End If
writeRS.Fields("extraField").Value = readRS.Fields("extraField").Value
writeRS.Update
readRS.Close
writeRS.Close
Else
MsgBox "Queried field must be a BLOB data type."
End If

' end code

There are probably a lot of typos but hopefuly you get the idea.
-GS

 

[ddrandy]

Thanks for the response and suggestion. I will try to be more detailed next time. The initial code gave me the error: -2147217900. Unclosed Quotation mark before the Character string '?'.
My table has 3 fields. Char(20), Char(1), Image.
I am not sure how the image is stored. It is not picture. The application I am useing sets it from a form I fill out.
This is the actual value of one of the records.
0xC03500005E4D524920202020202020202020202020207E4D41494E7E544F4F4C537E5644455349474E7E4D4E5544455349477E54424C44455349477E5244455349474E7E574644455349474E7E5744455349474E7E5245504F5254537E4F5054494F4E537E53504F4F4C45527E5252507E52455052545356527E525054517E

I tried your code and received the following error.
error: -2147467259. Invalid use fo default parameter.


Thanks

 

[ddrandy]

Thanks Garths2, The code is working great as far as I can tell.