Insert a file ito sql-database via Excel wo filestream

[Norbert Wilke]

Hi,

i try to insert a file (*.pdf) wich is referenced in an xl-sheet into a sql
database.
the required fileformat is blob.

I found the following code for doing this in VB.

Is there anything that can do the same in excel (like here the filestream)

THX for any help

Nobby

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand

Dim SQL As String

Dim FileSize As UInt32
Dim rawData() As Byte
Dim fs As FileStream

conn.ConnectionString = "server=127.0.0.1;" _
& "uid=root;" _
& "pwd=12345;" _
& "database=test"

Try
fs = New FileStream("c:\image.png", FileMode.Open, FileAccess.Read)
FileSize = fs.Length

rawData = New Byte(FileSize) {}
fs.Read(rawData, 0, FileSize)
fs.Close()

conn.Open()

SQL = "INSERT INTO file VALUES(NULL, ?FileName, ?FileSize, ?File)"

cmd.Connection = conn
cmd.CommandText = SQL
cmd.Parameters.Add("?FileName", strFileName)
cmd.Parameters.Add("?FileSize", FileSize)
cmd.Parameters.Add("?File", rawData)

cmd.ExecuteNonQuery()

MessageBox.Show("File Inserted into database successfully!", _
"Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

conn.Close()
Catch ex As Exception
MessageBox.Show("There was an error: " & ex.Message, "Error", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

 

[Tom Ogilvy]

The code you show doesn't appear to be straight VB. Perhaps it is VB.Net.


http://support.microsoft.com/kb/258038/en-us
How To Access and Modify SQL Server BLOB Data by Using the ADO Stream Object

may work.

 

[Norbert Wilke]

hello Tom,

thanks for the hint.
I had to change the code a bit and now it works fine.

Norbert