Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible to handle database insert, update and select in excel? | Excel Worksheet Functions | |||
How to insert Excel sheet to a DB2 database | Excel Programming | |||
Insert record into Excel database using a custom VBA form? | Excel Programming | |||
Open a workbook in the background (like filestream) | Excel Programming | |||
How can I insert 4000 rows of Excel data into a sql database | Excel Programming |