Saving Blob Data To File

[Guest]

I have a VB 6 program that I have saved an audio file to using DAO and the
appendchunk as is shown at http://support.microsoft.com/kb/103257/EN-US/. ; I
have it working fine in that app (it saves to an access database and I can
save the data back to a file using getchunk and everything works correctly).

The issue is that I am now trying to access the database in VB.NET 2003
through an OleDBDataAdapter (Jet 4.0 connection) and can't get it to work
properly. After running my code, the file is there, but can't get it to play
in any audio player, and the file size of the file is double that of the file
I saved to the database in the VB 6 app.

I am using code that is shown at
http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and I am using an OleDBDataAdapter to access the database.

Does anyone know how to save data from an OLE field in an access database in
ADO.NET in VB.NET using OleDBDataAdapter when the data in the OLE field was
originally recorded by a VB program using DAO and the appendchunk function?

Or does anyone know the opposite, a better way using DAO only and VB6 to
save an MP3 file to an OLE field in an access database using a method that I
can easily save the data to a MP3 file in the same VB6 app and the VB.NET app
I have which uses an OleDBDataAdapter?

 

[Paul Clement]

¤ I have a VB 6 program that I have saved an audio file to using DAO and the
¤ appendchunk as is shown at http://support.microsoft.com/kb/103257/EN-US/. ; I
¤ have it working fine in that app (it saves to an access database and I can
¤ save the data back to a file using getchunk and everything works correctly).
¤
¤ The issue is that I am now trying to access the database in VB.NET 2003
¤ through an OleDBDataAdapter (Jet 4.0 connection) and can't get it to work
¤ properly. After running my code, the file is there, but can't get it to play
¤ in any audio player, and the file size of the file is double that of the file
¤ I saved to the database in the VB 6 app.
¤
¤ I am using code that is shown at
¤ http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and I am using an OleDBDataAdapter to access the database.
¤
¤ Does anyone know how to save data from an OLE field in an access database in
¤ ADO.NET in VB.NET using OleDBDataAdapter when the data in the OLE field was
¤ originally recorded by a VB program using DAO and the appendchunk function?
¤
¤ Or does anyone know the opposite, a better way using DAO only and VB6 to
¤ save an MP3 file to an OLE field in an access database using a method that I
¤ can easily save the data to a MP3 file in the same VB6 app and the VB.NET app
¤ I have which uses an OleDBDataAdapter?

I believe the counterpart to the KB article you posted would be the AppendChunk/GetChunk methods. So
for example, if you saved the file using AppendChunk and ADO (or DAO) you should be able to retrieve
the file using the code from the KB article you posted.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

From what I have been told, there is no getchunk in ADO.net so that adds to
the issue. I used the appendchunk and getchunk in my VB6 app with DAO and
that works great. But with no getchunk in ADO.net, I am guessing the code I
am using in VB.NET 2003 does not record the file the same way as if I use
getchunk.

In VB6 for reading from the database, the code I used is (I already have a
recordset to the correct record):

Dim NumBlocks As Integer, DestFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String

' Get the size of the field.
FileLength = rsQuestions.Fields("SoundBite").FieldSize()
If FileLength = 0 Then
Exit Sub
End If

' Calculate number of blocks to write and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize

' Remove any existing destination file.
DestFile = FreeFile
Open "Temp2.mp3" For Output As DestFile
Close DestFile

' Open the destination file.
Open "Temp2.mp3" For Binary As DestFile

' Write the leftover data to the output file.
FileData = rsQuestions.Fields("SoundBite").GetChunk(0, LeftOver)
Put DestFile, , FileData

' Write the remaining blocks of data to the output file.
For i = 1 To NumBlocks
' Reads a chunk and writes it to output file.
FileData = rsQuestions.Fields("SoundBite").GetChunk((i - 1) *
BlockSize _
+ LeftOver, BlockSize)
Put DestFile, , FileData
Next i

Close DestFile


The only thing I can think of is that the code above is recording the file
differently than the following code that I have tried in VB.net but I am not
sure what I need to change in the following vb.net code to get the same
results:

Dim SystemConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFileName & ";User ID=Admin;Jet " _
& "OLEDBatabase Password=****"
Dim cn As New System.Data.OleDb.OleDbConnection(SystemConnString)
cn.Open()

Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT SoundBite from "
& strTableName & " where [QuestionNumber] = " & CurrentQuestion, cn)
Dim dr As System.Data.OleDb.OleDbDataReader = cmd.ExecuteReader
dr.Read()
Dim b(dr.GetBytes(0, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
dr.GetBytes(0, 0, b, 0, b.Length)
dr.Close()
cn.Close()
Dim fs As New System.IO.FileStream("SoundBite.mp3", IO.FileMode.Create,
IO.FileAccess.Write)
fs.Write(b, 0, b.Length)
fs.Close()

dr.Close()
cn.Close()


Any ideas? Is there any way I can adjust the vb.net code with ado.net to be
able to replicate what the code in vb6 does even though there is no getchunk
function? Or with the way I am attempting to save the data in ado.net to a
file, is there a way to replicate this functionality in a vb6 program using
DAO with or without getchunk?

I have been searching the internet for a solution for the last couple of
days and no luck. The only solutions listed for Blob data in vb6 is to use
appendchunk and everything listed for vb.net shows what I just posted or
something similar with the message that getchunk and appendchunk are not
available in ado.net. So I am out of ideas as to why the .net code results
in a file that is double the size of what was originally recorded and then
doesn't work in any audio player like media player.

If you have any other ideas, information, links, anything, please reply.

Mark

 

[arthurjr07]

This is the sample code from msdn on how to save image to database.

' PREPARE DATA TO BE PASSED TO DATABASE:
' You only need to save the filename, not the entire path.
Therefore,
' Split the path, creating an array of strings. Make sure you
pass in
' the delimiter. Then reverse the array so that you can assign
the
' first string in the array to the SQL parameter.
Dim arrFilename() As String = Split(lblFilePath.Text, "\")
arrFilename.Reverse(arrFilename)

' The SQL Server Image datatype is a binary datatype.
Therefore, to save
' it to the database you must convert the image to an array of
bytes. You
' could use a FileStream object to open the image file and then
read it to
' the stream, but a MemoryStream with the Image.Save method is
a bit easier.
Dim ms As New MemoryStream()
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
Dim arrImage() As Byte = ms.GetBuffer

' Close the stream object to release the resource.
ms.Close()

' Display a status message saying that we're attempting to
connect.
' This only needs to be done the very first time a connection
is
' attempted. After we've determined that MSDE or SQL Server is
' installed, this message no longer needs to be displayed.
Dim frmStatusMessage As New frmStatus()
If Not didPreviouslyConnect Then
frmStatusMessage.Show("Connecting to SQL Server")
End If

' Attempt to connect to the local SQL server instance, and a
local
' MSDE installation (with Northwind).
Dim isConnecting As Boolean = True
While isConnecting

Try
' The SqlConnection class allows you to communicate
with SQL Server.
' The constructor accepts a connection string as an
argument. This
' connection string uses Integrated Security, which
means that you
' must have a login in SQL Server, or be part of the
Administrators
' group for this to work.
Dim northwindConnection As New
SqlConnection(connectionString)
Dim strSQL As String = _
"INSERT INTO Picture (Filename, Picture)" & _
"VALUES (@Filename, @Picture)"

' A SqlCommand object is used to execute the SQL
statement.
Dim cmd As New SqlCommand(strSQL, northwindConnection)
With cmd
' Add parameters required by SQL statement.
PictureID is an
' identity field (in Microsoft Access, an
AutoNumber field),
' so you only need to pass values for the two
remaining fields.
.Parameters.Add(New SqlParameter("@Filename", _
SqlDbType.NVarChar, 50)).Value = arrFilename(0)
.Parameters.Add(New SqlParameter("@Picture", _
SqlDbType.Image)).Value = arrImage
End With

' Open the connection, execute the command, and close
the
' connection. It is more efficient to ExecuteNonQuery
when data
' is not being returned.
northwindConnection.Open()
cmd.ExecuteNonQuery()
northwindConnection.Close()

' Data has been successfully submitted, so break out of
the loop
' and close the status form.
isConnecting = False
didPreviouslyConnect = True
frmStatusMessage.Close()
MessageBox.Show(arrFilename(0) & " saved to the
database.", _
"Image Save Status", MessageBoxButtons.OK, _
MessageBoxIcon.Information)

Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception
Error!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit While
Catch exc As Exception
If connectionString = SQL_CONNECTION_STRING Then
' Couldn't connect to SQL Server. Now try MSDE.
connectionString = MSDE_CONNECTION_STRING
frmStatusMessage.Show("Connecting to MSDE")
Else
' Unable to connect to SQL Server or MSDE
frmStatusMessage.Close()
MessageBox.Show(CONNECTION_ERROR_MSG, _
"Connection Failed!", MessageBoxButtons.OK,
_
MessageBoxIcon.Error)
End
End If
End Try
End While

 

[Cor Ligthert [MVP]]

Mark,

As it not has to be an OleDb field but a Blob field.

http://www.vb-tips.com/default.aspx?ID=0bf3f72d-b722-459d-8a46-38b5a2f7fdf0

The part with the picturebox in it is only to show and has no function.

I hope this helps,

Cor

 

[Guest]

Thanks Cor and Arthurjr07. I will give both suggestions a try and see if I
can't get the data to save properly. Thanks for the help.

Mark