Lanceli


Hi all,

In the end of the post, there is my vba functions and query to create datatable. I got a run-time error message on the red line in the function. It says: [sql native client] string data right truncation. Did I do sometime wrong Thanks for your time!

-L

Sub test()

Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

Dim mConn As ADODB.Connection

'read the file data to bytes
'Specify stream type - we want To get binary data.
BinaryStream.Type = adTypeBinary
'Open the stream
BinaryStream.Open
'Load the file data from disk To stream object
BinaryStream.LoadFromFile file

Dim mCmd As ADODB.Command
Set mCmd = New ADODB.Command
With mCmd

' define query
.CommandText = "INSERT INTO TEST (FDATA) " & _
"VALUES ( )"
.CommandType = adCmdText

' add parameter
.Parameters.Append .CreateParameter("@P1", adVarBinary, _
adParamInput, BinaryStream.Size, BinaryStream.Read)
End With

' open db connection

mConn.ConnectionString = _
"Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=XXXX;Trusted_Connection=yes;"
mCmd.ActiveConnection = mConn

'run-time error: [sql native client] string data right truncation
mCmd.Execute

mConn.Close
End Sub


GO
/****** Object: Table [dbo].[TEST] Script Date: 10/04/2007 11:06:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FDATA] [varbinary](max) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF




Re: How to insert binary to SQL Express by VBA?

Lanceli


problem solved, I used wrong parameter type. Thanks for your time again.

Br,

L