Good day, gurus of this well-known site. I am using vb.net 2013 Ultimate and SQL 2012 and can someone show/link me how to save and retrieve a word document from SQL database? Thanks
I'm not up to SQL 2012 yet (I'm using 2008) but this works for me.
My table is
FileName VARCHAR(255) (PK) WordDoc VARBINARY(MAX)
To get the data into the table you have to read the Word document into a Byte array as binary. When you want to recreate the document you must write it as binary. The two routines to read and write the files are
Private Function ReadWordDoc(filename As String) As Byte() Dim fs As New System.IO.FileStream(filename, IO.FileMode.Open) Dim br As New System.IO.BinaryReader(fs) Dim data() As Byte = br.ReadBytes(fs.Length) br.Close() fs.Close() Return data End Function Private Sub WriteWordDoc(filename As String, data As Byte()) Dim fs As New System.IO.FileStream(filename, IO.FileMode.Create) Dim bw As New System.IO.BinaryWriter(fs) bw.Write(b) bw.Close() fs.Close() End Sub
Note that you should add your own error checkinig as required. Although I named the routines to work with Word docs, they will work for any file type.
Here is the code to save a document to the database and to retrieve a previously saved document.
Private Sub btnStore_Click(sender As System.Object, e As System.EventArgs) Handles btnStore.Click Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;") Dim cmd As New SqlCommand("", con) Dim file As String = "D:\temp\test.docx" Dim doc() As Byte = ReadWordDoc(file) cmd.CommandText = "INSERT INTO WordDocs (FileName,WordDoc) VALUES(@FILE,@DOC)" cmd.Parameters.AddWithValue("@FILE", file) cmd.Parameters.AddWithValue("@DOC ", doc) con.Open() cmd.ExecuteNonQuery() con.Close() End Sub Private Sub btnRetrieve_Click(sender As System.Object, e As System.EventArgs) Handles btnRetrieve.Click Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;") Dim cmd As New SqlCommand("", con) Dim file As String = "D:\temp\test.docx" cmd.CommandText = "SELECT * FROM WordDocs WHERE FileName = @FILE" cmd.Parameters.AddWithValue("@FILE", file) con.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader If rdr.Read Then WriteWordDoc(file, rdr("WordDoc")) Else MsgBox(file & " not found") End If rdr.Close() con.Close() End Sub
Edited 2 Years Ago by Reverend Jim
The reverend's solution should work with MS-SQL. Basically, a Word file is a BLOB of unknown size. If you aren't using MS-SQL, then the definition and possibly access will differ. I don't think the solution will work for Oracle, for example.
Sir, after retrieving the file from database can it be open again in ms word?
Not directly. First you must write the data to a Word file, then you can open it with Word.
Hey, so I wanna ask how I need to create a method who will remove word if in that word is 2 same chars. Example: "Potato" in this word there is a 2 "o" chars so this word will need to be removed. "Forum" in this word there is no ...
Hi I'm having a problem implementing a mini shopping cart drop down in the header to show the user all the products they have in their shopping cart. It seems the only solution for this is Ajax, and I've looked all over and can't find anything that I could possibly ...
Hi. Im using vb 2010. I want to dynamically add textboxes to my form by clicking on a button. I've google searched and so far this code worked:
Private Sub btn_addline_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_addline.Click Dim txtB1 As New TextBox Dim i For i = ...