Write Blob to Access DB

  • Thread starter Thread starter Ken Pinard
  • Start date Start date
K

Ken Pinard

I am trying to store a blob (large text data) into an Access database.
ADO.Net wants an update sql statement. Which I do not know how to write for
blobs.

I tried including ADODB to open a recordset and assign values to fields. But
that told me the item property was readonly.

I have searched and searched and everything comes up on how to retreive
BLobs. Not how to put them in.

Any help would be greatly appreciated,

Ken Pinard
 
I saw something about this while researching another topic bit earlier
today. I recall the trick was to use a byte array in your language.
Then to do an INSERT command with a "parameterized query", and set the
parameter value for the blob via the PARAMETERS collection on the
command. I hope I remember this correctly... it seemed unapparent to
me, so I tried to remember the approach for myself.

HTH - Lee
 
Interesting,

I am not sure how to do this. Can parameters be used in the Jet engine?

I will keep looking, I appreciate the heads up on this.

Thank you,
Ken
 
Hi Ken,

Here is an example of an insert into binary column (OLEObject):

this.oleDbInsertCommand2.CommandText = "INSERT INTO Blobed(YourBinaryColumn)
VALUES (?)";

this.oleDbInsertCommand2.Connection = this.oleDbConnection1;

this.oleDbInsertCommand2.Parameters.Add(new
System.Data.OleDb.OleDbParameter("YourBinaryColumn",
System.Data.OleDb.OleDbType.VarBinary, 0, "YourBinaryColumn"));



oleDbConnection1.Open();

try

{

oleDbInsertCommand2.Parameters[1].Value= new byte[]{1, 2, 3, 4};

oleDbInsertCommand2.ExecuteNonQuery();

}

finally

{

oleDbConnection1.Close();

}
 
Hi
The below code might helps you to add/update the Blob info
to the Access DataBase.
''''''''' code starts here ''''''''''''''''''''''''''''''''

FileStream fsCurimage = new
FileStream
("c:\\EIP.JPG",FileMode.OpenOrCreate,FileAccess.Read);
byte[] RawData = new byte
[fsCurimage.Length];
fsCurimage.Read
(RawData,0,Convert.ToInt32(fsCurimage.Length));
fsCurimage.Close();

OleDbConnection ole_CONN = new
OleDbConnection();
ole_CONN.ConnectionString =
oleDbConnection1.ConnectionString;
ole_CONN.Open();

OleDbDataAdapter ole_Adapter = new
OleDbDataAdapter("Select * From ImgTable",ole_CONN);
DataSet ds = new DataSet();
DataRow dr ;

try
{
// ole_CMD.CommandText
= "Insert Into Img values('" + RawData + "')";
// ole_CMD.ExecuteNonQuery();
ole_Adapter.Fill(ds);
dr = ds.Tables[0].NewRow();
dr["Img"] = RawData;
ds.Tables[0].Rows.Add(dr);
if (ds.HasChanges())
{ ds.AcceptChanges();
}

MessageBox.Show(ds.Tables
[0].Rows.Count.ToString());
}
catch(Exception ee)
{
MessageBox.Show(ee.ToString
());
}

'''''''''''''''' End of Code ''''''''''''''''''''''''''''

Thanks
srinvias moorthy
 
¤ I am trying to store a blob (large text data) into an Access database.
¤ ADO.Net wants an update sql statement. Which I do not know how to write for
¤ blobs.
¤
¤ I tried including ADODB to open a recordset and assign values to fields. But
¤ that told me the item property was readonly.
¤
¤ I have searched and searched and everything comes up on how to retreive
¤ BLobs. Not how to put them in.
¤
¤ Any help would be greatly appreciated,

I will assume that you are working with an Access Memo field data type:

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 MemoField=? WHERE [record id] = 1",
AccessConnection)
Dim StringValue As String = "Very Long String Value"
Dim QueryParameter As New OleDbParameter("@LongText", OleDbType.LongVarWChar,
Len(StringValue), ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current,
StringValue)
AccessCommand.Parameters.Add(QueryParameter)
AccessConnection.Open()
AccessCommand.ExecuteNonQuery()
AccessConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top