Visual Basic Syntax - Store MS Word File in SQL Server Database

Author

Store MS Word File in SQL Server Database

Manish Sawjiani

2005-02-23, 4:03 pm

Hello Experts,
My application needs me to create a word file from the form itself and store
the same instantly in a SQL Server Database. I am using ADO (Without
Datacontrol)
(i am doing the following dim rs as ADODB.Recordset ......)

would greatly appreciate a help or some sample in this regard.

Thanks a Lot in Advance
Manish Sawjiani

--
Three Cheers to technet for the Help!

Manish Sawjiani

2005-03-01, 9:00 am

I have found the answer to this

if anybody wants a full solution i shall be happy to give to them

regards
Thanks
Manish

"Manish Sawjiani" wrote:

> Hello Experts,
> My application needs me to create a word file from the form itself and store
> the same instantly in a SQL Server Database. I am using ADO (Without
> Datacontrol)
> (i am doing the following dim rs as ADODB.Recordset ......)
>
> would greatly appreciate a help or some sample in this regard.
>
> Thanks a Lot in Advance
> Manish Sawjiani
>
>
> --
> Three Cheers to technet for the Help!

Jan Hyde

2005-03-01, 4:02 pm

Manish Sawjiani <ManishSawjiani@discussions.microsoft.com>'s
wild thoughts were released on Tue, 1 Mar 2005 03:05:02
-0800 bearing the following fruit:

>I have found the answer to this
>
>if anybody wants a full solution i shall be happy to give to them

Why not post it anyway, someone might come accross your post
w

s or months from now.

J
[color=darkred]
>regards
>Thanks
>Manish
>
>"Manish Sawjiani" wrote:
>

Jan Hyde (VB MVP)

--
If you have a lot of tension and you get a headache,
do what it says on the aspirin bottle:
Take two and keep away from children. (Barbie Jo)

[Abolish the TV Licence - http://www.tvlicensing.biz/]

Manish Sawjiani

2005-03-05, 8:58 am

I agree Jan and this is how I did it. If there are any comments please feel
to contact me i will be happy to help out anyone in this regard

1. Dropped an Ole Control on the Form

2. Then I have 1 button start with the following commands
2a) OLE1.CreateEmbed "C:\Test.Doc"
The temorary word file will get loaded in the OLE Control
2b) Ole1.Action = 7
This one launches word and you can type whatever you want
and from words file menu click update. This will update your ole control.

3) I have one button to save it in the Ole

3. I have a button now to save the Document in file. Well you see first you
will have to make a temporary file out of your ole control and then append it
to the db. I am using a function call WriteBlobToDB

Private Sub cmdSave_Click()
Dim FileNum As Integer
Dim strsql As String
Dim fname As String
fname = "c:\temp\TEST.OLE"
' Get file number.
FileNum = FreeFile
' Open file to be saved.
Open "c:\temp\TEST.OLE" For Binary As #FileNum
' Save the file. This is the command that transfer to file
OLE1.SaveToFile FileNum
' Close the file.
Close #FileNum
If rsAnnex.RecordCount = 0 Then
rsAnnex.AddNew
rsAnnex.Fields!vno_prefix = Trim(cboPrefix.Text)
rsAnnex.Fields!vno = Trim(txtNumber.Text)
rsAnnex.Update
' The First Parameter is the Recordset, Secondone is the Field Number
' and the third is the temporary file name
Call WriteBlobToDB(rsAnnex, 2, fname)
rsAnnex.Update
rsAnnex.Close
Else
Call WriteBlobToDB(rsAnnex, 2, fname)
rsAnnex.Update
rsAnnex.Close
End If
End Sub

4. Now I have another button to read from database I read and create a
temporary file and this temporary file i load it to the OLE Control

Private Sub cmdAnnexRead_Click()
Dim strsql As String
Dim FileNum As Integer
Dim fname As String
If rsAnnex.RecordCount <> 0 Then
fname = ReadBlobFromDB(rsAnnex, 2)

' Get file number.
FileNum = FreeFile
' Open the file.
Open fname For Binary As #FileNum
' Read the file.
OLE1.ReadFromFile FileNum
' Close the binary file.
Close #FileNum
End If
cmdAnnexSave.Enabled = True
OLE1.Action = 7
End Sub

'' These are the functions I have created in the MODULE WITH Some constants
and they are as follows

Dim chunk() As Byte
Const conChunkSize = 256

Public Function ReadBlobFromDB(rsDoc As ADODB.Recordset, fld As Integer) As
String
Dim i As Integer, noFrag As Integer
Dim fileno As Integer
Dim imgsize As Long, offset
Dim fname As String
imgsize = rsDoc.Fields(fld).ActualSize
noFrag = imgsize \ conChunkSize
offset = imgsize Mod conChunkSize
If noFrag = 0 Then
ReDim chunk(offset)
Else
ReDim chunk(conChunkSize)
End If
fname = "C:\Temp\outfromdb.ole"
fileno = FreeFile
Open fname For Binary As fileno
For i = 1 To noFrag
chunk = rsDoc.Fields(fld).GetChunk(conChunkSize)
Put #fileno, , chunk
Next
If offset > 0 Then
chunk() = rsDoc.Fields(fld).GetChunk(offset)
Put #fileno, , chunk
End If
Close fileno
ReadBlobFromDB = fname
end function

Public Function WriteBlobToDB(rsDoc As ADODB.Recordset, fld As Integer,
fname As String)
Dim lOffset1 As Long
Dim nFragmentOffset1 As Integer
Dim nHandle
Dim lChunks As Long
Dim lSize As Long
Dim i As Integer

nHandle = FreeFile

Open fname For Binary As nHandle
lSize = LOF(nHandle)
If nHandle = 0 Then
Close nHandle
End If
lChunks = lSize / conChunkSize
nFragmentOffset1 = lSize Mod conChunkSize

ReDim chunk(nFragmentOffset1)
Get nHandle, , chunk()
rsDoc.Fields(fld).AppendChunk chunk()
ReDim chunk(conChunkSize)

For i = 1 To lChunks
Get nHandle, , chunk()
rsDoc.Fields(fld).AppendChunk chunk()
Next

End Function

Sponsored Links