How to insert binary image into MS-Access using.net

[androoo]

I have a source database which stores the main image.
I read the image out as this :

Dim strSQL As String
Dim objData As clsDatabase
Dim dr As OleDb.OleDbDataReader

strSQL += "select * from picstable" ' this is a foxpro database
'open object for database work
objData = objData.GetInstance

dr = objData.GetDatareader(strSQL)
While dr.Read
objWriter.Write(dr.Item("picdata"))
end while

So now i have the data i need to copy it into another (Ms-Access)
database..

I tried this..
While dr.Read

strConnStr = ConfigurationSettings.AppSettings("connString")
Dim objConn As New OleDbConnection(strConnStr)
objConn.Open()

Dim cmd As New OleDbCommand("INSERT INTO prodpics(pic) VALUES (?)",
objConn)
Dim bytearray() As Byte

cmd.Parameters.Add(dr.Item("picdata"), OleDbType.Binary).Value =
bytearray

objConn.Open()
cmd.ExecuteNonQuery()
objConn.Close()
end while

I get a huge error message, im new to paramterised queries such as this
so any help appreciated
error is :

System.Reflection.AmbiguousMatchException: No accessible overloaded
'OleDbParameterCollection.Add' can be called without a narrowing
conversion. at
Microsoft.VisualBasic.CompilerServices.VBBinder.set_InternalThrow(Exception
Value) at
Microsoft.VisualBasic.CompilerServices.VBBinder.BindToMethod(BindingFlags
bindingAttr, MethodBase[] match, Object[]& args, ParameterModifier[]
modifiers, CultureInfo culture, String[] names, Object& ObjState) at
Microsoft.VisualBasic.CompilerServices.VBBinder.InvokeMember(String
name, BindingFlags invokeAttr, Type objType, IReflect objIReflect,
Object target, Object[] args, ParameterModifier[] modifiers,
CultureInfo culture, String[] namedParameters) at
Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o,
Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack) at eXcommerce.WebForm1.Button2_Click(Object sender,
EventArgs e) in www...

 

[Tom John]

On 13 Jul 2005 07:07:51 -0700, "androoo" <[email protected]>
wrote:

<snip>

Have you looked at Cor's example on this subject:

http://groups-beta.google.com/group/microsoft.public.dotnet.languages.vb/msg/9bbd27ce29590856?hl=en&

Or

http://groups.google.co.uk/group/mi...valid+parameter&rnum=1&hl=en#fa5e5a89b895f812

Hope this helps

Tom

 

[Paul Clement]

¤ I have a source database which stores the main image.
¤ I read the image out as this :
¤
¤ Dim strSQL As String
¤ Dim objData As clsDatabase
¤ Dim dr As OleDb.OleDbDataReader
¤
¤ strSQL += "select * from picstable" ' this is a foxpro database
¤ 'open object for database work
¤ objData = objData.GetInstance
¤
¤ dr = objData.GetDatareader(strSQL)
¤ While dr.Read
¤ objWriter.Write(dr.Item("picdata"))
¤ end while
¤
¤ So now i have the data i need to copy it into another (Ms-Access)
¤ database..
¤
¤ I tried this..
¤ While dr.Read
¤
¤ strConnStr = ConfigurationSettings.AppSettings("connString")
¤ Dim objConn As New OleDbConnection(strConnStr)
¤ objConn.Open()
¤
¤ Dim cmd As New OleDbCommand("INSERT INTO prodpics(pic) VALUES (?)",
¤ objConn)
¤ Dim bytearray() As Byte
¤
¤ cmd.Parameters.Add(dr.Item("picdata"), OleDbType.Binary).Value =
¤ bytearray
¤
¤ objConn.Open()
¤ cmd.ExecuteNonQuery()
¤ objConn.Close()
¤ end while
¤
¤ I get a huge error message, im new to paramterised queries such as this
¤ so any help appreciated
¤ error is :
¤
¤ System.Reflection.AmbiguousMatchException: No accessible overloaded
¤ 'OleDbParameterCollection.Add' can be called without a narrowing
¤ conversion. at
¤ Microsoft.VisualBasic.CompilerServices.VBBinder.set_InternalThrow(Exception
¤ Value) at
¤ Microsoft.VisualBasic.CompilerServices.VBBinder.BindToMethod(BindingFlags
¤ bindingAttr, MethodBase[] match, Object[]& args, ParameterModifier[]
¤ modifiers, CultureInfo culture, String[] names, Object& ObjState) at
¤ Microsoft.VisualBasic.CompilerServices.VBBinder.InvokeMember(String
¤ name, BindingFlags invokeAttr, Type objType, IReflect objIReflect,
¤ Object target, Object[] args, ParameterModifier[] modifiers,
¤ CultureInfo culture, String[] namedParameters) at
¤ Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o,
¤ Type objType, String name, Object[] args, String[] paramnames,
¤ Boolean[] CopyBack) at eXcommerce.WebForm1.Button2_Click(Object sender,
¤ EventArgs e) in www...

You should be able to adapt the following to your code:

Sub WriteBlobToAccess()

Dim SourceFilePath As String
SourceFilePath = "e:\My Documents\Greenstone.bmp"
Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")
Dim AccessCommand As New OleDbCommand("UPDATE Table1 SET OLEField=? WHERE [record id] = 1",
AccessConnection)
Dim FileStreamObject As New System.IO.FileStream(SourceFilePath, IO.FileMode.Open,
IO.FileAccess.Read)
Console.Write(FileStreamObject.Length)
Dim PictureByteArray(CType(FileStreamObject.Length() - 1, Integer)) As Byte
FileStreamObject.Read(PictureByteArray, 0, PictureByteArray.Length)
FileStreamObject.Close()
Dim QueryParameter As New OleDbParameter("@Picture", OleDbType.LongVarBinary,
PictureByteArray.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current,
PictureByteArray)
AccessCommand.Parameters.Add(QueryParameter)
AccessConnection.Open()
AccessCommand.ExecuteNonQuery()
AccessConnection.Close()

End Sub


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[androoo]

Thanks for all the help so far, im getting more success with reading
out from different databases with your examples but writing into access
is still causing some problems.
I followed your examples and adapted the code to this to insert a image
into access.

While dr.Read

objWriter.Write(dr.Item("picdata"))
bmpFile = New Bitmap(stmFile)
stmFile.Read(byt, 0, byt.Length)

strConnStr =
ConfigurationSettings.AppSettings("connString")
Dim objConn As New OleDbConnection(strConnStr)
objConn.Open()

Dim DBCmd As New OleDb.OleDbCommand("UPDATE exproduct
SET pic=@pic WHERE id = 1207", objConn)
Dim P As New OleDb.OleDbParameter("@pic",
OleDb.OleDbType.LongVarBinary, byt.Length, ParameterDirection.Input,
False, 0, 0, Nothing, DataRowVersion.Current, byt)
DBCmd.Parameters.Add(P)
DBCmd.ExecuteNonQuery()
stmFile.Close()
End While

The code runs through and doesnt fail and gives the impression its
working, however there is nothing added to the database... can you see
anything obvious im doing wrong, im a little out of my depth with this
!

Thanks again!

 

[Paul Clement]

¤ Thanks for all the help so far, im getting more success with reading
¤ out from different databases with your examples but writing into access
¤ is still causing some problems.
¤ I followed your examples and adapted the code to this to insert a image
¤ into access.
¤
¤ While dr.Read
¤
¤ objWriter.Write(dr.Item("picdata"))
¤ bmpFile = New Bitmap(stmFile)
¤ stmFile.Read(byt, 0, byt.Length)
¤
¤ strConnStr =
¤ ConfigurationSettings.AppSettings("connString")
¤ Dim objConn As New OleDbConnection(strConnStr)
¤ objConn.Open()
¤
¤ Dim DBCmd As New OleDb.OleDbCommand("UPDATE exproduct
¤ SET pic=@pic WHERE id = 1207", objConn)
¤ Dim P As New OleDb.OleDbParameter("@pic",
¤ OleDb.OleDbType.LongVarBinary, byt.Length, ParameterDirection.Input,
¤ False, 0, 0, Nothing, DataRowVersion.Current, byt)
¤ DBCmd.Parameters.Add(P)
¤ DBCmd.ExecuteNonQuery()
¤ stmFile.Close()
¤ End While
¤
¤ The code runs through and doesnt fail and gives the impression its
¤ working, however there is nothing added to the database... can you see
¤ anything obvious im doing wrong, im a little out of my depth with this
¤ !

How have you verified that nothing has been written to the database? What is the data type you are
using to store the binary image?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[androoo]

I am using an OLEObject type.
I tested by adding an image in manually and testing the code that reads
the image.
When I insert manually the code returns the image. When I insert with
the the above code it is empty,,,, is there anything abvious you can
see wrong with the code ?

Thanks for your help

 

[Paul Clement]

¤ I am using an OLEObject type.
¤ I tested by adding an image in manually and testing the code that reads
¤ the image.
¤ When I insert manually the code returns the image. When I insert with
¤ the the above code it is empty,,,, is there anything abvious you can
¤ see wrong with the code ?

What if you use the example I posted to read it in from a file? Does that code work?


Paul
~~~~
Microsoft MVP (Visual Basic)