Insert a file ito sql-database via Excel wo filestream
 

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

Insert a file ito sql-database via Excel wo filestream
 

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.

--
Regards,
Tom Ogilvy



"Norbert Wilke" wrote in message
...
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

Insert a file ito sql-database via Excel wo filestream
 

hello Tom,

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

Norbert


"Tom Ogilvy" schrieb im Newsbeitrag
...
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.

--
Regards,
Tom Ogilvy



"Norbert Wilke" wrote in message
...
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