Inserting JPEG images into an Access database using C# and ADO.NET

[Deepankar]

Hi,
I was trying to change an example for SQL Server to work with Access
db to insert image data. I have everything working except getting the
OleDbParameter type for the image column.

The table in access is :
img (
id number ,
name Text,
img number [Byte]
);

code :
===========
//....
byte[] photo = GetPhoto(photoFilePath);
OleDbConnection Conn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; User Id=;Data
Source=C:\\temp\\testdb.mdb");

OleDbCommand addJpg = new OleDbCommand("update img set img =
@Photo where id = @id", Conn);

OleDbDataAdapter MyDataAdapter = new OleDbDataAdapter();

MyDataAdapter.InsertCommand = addJpg ;

OleDbParameter id = new OleDbParameter ("@id", OleDbType.Integer)
;
id.Value = 1;

OleDbParameter ph = new OleDbParameter ("@Photo",
OleDbType.VarBinary, photo.Length) ;
ph.Value = photo;

MyDataAdapter.InsertCommand .Parameters.Add(ph);
MyDataAdapter.InsertCommand .Parameters.Add(id);

// connect
Conn.Open();
// insert
addJpg.ExecuteNonQuery();

Conn.Close();
//........

Any pointers...
Thanx in Advance
DN

 

[Miha Markic [MVP C#]]

Hi,

Check out this article:
How to read and write a file to or from a BLOB column by using ADO.NET and
Visual C# .NET
http://tinyurl.com/2nvn5

 

[Deepankar]

Hi Miha,
Thx for the site. It helped to solve the problem.
Cheers
DN

Hi,

Check out this article:
How to read and write a file to or from a BLOB column by using ADO.NET and
Visual C# .NET
http://tinyurl.com/2nvn5

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hi,
I was trying to change an example for SQL Server to work with Access
db to insert image data. I have everything working except getting the
OleDbParameter type for the image column.

The table in access is :
img (
id number ,
name Text,
img number [Byte]
);

code :
===========
//....
byte[] photo = GetPhoto(photoFilePath);
OleDbConnection Conn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; User Id=;Data
Source=C:\\temp\\testdb.mdb");

OleDbCommand addJpg = new OleDbCommand("update img set img =
@Photo where id = @id", Conn);

OleDbDataAdapter MyDataAdapter = new OleDbDataAdapter();

MyDataAdapter.InsertCommand = addJpg ;

OleDbParameter id = new OleDbParameter ("@id", OleDbType.Integer)
;
id.Value = 1;

OleDbParameter ph = new OleDbParameter ("@Photo",
OleDbType.VarBinary, photo.Length) ;
ph.Value = photo;

MyDataAdapter.InsertCommand .Parameters.Add(ph);
MyDataAdapter.InsertCommand .Parameters.Add(id);

// connect
Conn.Open();
// insert
addJpg.ExecuteNonQuery();

Conn.Close();
//........

Any pointers...
Thanx in Advance
DN

 

[B Wagner]

Hi,

Check out this article:
How to read and write a file to or from a BLOB column by using ADO.NET and
Visual C# .NET
http://tinyurl.com/2nvn5

Any chance you can post that url in a not tiny form?

 

[Deepankar]

Hi ,
I am trying to remove rows from a table using :
DataTable.Rows.RemoveAt(int index);
or
DataTable.Rows.Remove(DataRow row);
syntax.
I am able to run my test program successfully but none of the rows get
deleted. It works fine by using a delete SQL statement with a Command
object but I am trying to learn other methods todo the same. The
sample code : -

// dataset
DataSet ds = new DataSet() ;

// loads result set into dataset
oleDbDataAdapter.Fill(ds);

// get the table; since we have only one table
DataTable workTable = ds.Tables[0];
// give it a name
workTable.TableName = "coffee";
// how many rows do i have before removing
Console.WriteLine("Number of rows: {0}", workTable.Rows.Count );
for (int i=0; i < workTable.Rows.Count; i++)
{
DataRow myRow = workTable.Rows;
int sup_id = Convert.ToInt32(myRow["sup_id"]);
if ( sup_id == 120 )
{
Console.WriteLine(sup_id);
workTable.Rows.RemoveAt(i);
workTable.AcceptChanges();
}
}

/***
foreach (DataRow myRow in workTable.Rows)
{
int sup_id = Convert.ToInt32(myRow["sup_id"]);
if ( sup_id == 120 )
{
Console.WriteLine(sup_id);
workTable.Rows.Remove(myRow);
workTable.AcceptChanges();
}
}
*** /

// how many rows after removing
Console.WriteLine("Number of rows: {0}", workTable.Rows.Count );

}catch(OleDbException e)
{
Console.WriteLine(e.StackTrace);
}
How do I commit the changes such that the rows are deleted from the
database;
Thx in advance
DN

Hi Miha,
Thx for the site. It helped to solve the problem.
Cheers
DN

Hi,

Check out this article:
How to read and write a file to or from a BLOB column by using ADO.NET and
Visual C# .NET
http://tinyurl.com/2nvn5

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hi,
I was trying to change an example for SQL Server to work with Access
db to insert image data. I have everything working except getting the
OleDbParameter type for the image column.

The table in access is :
img (
id number ,
name Text,
img number [Byte]
);

code :
===========
//....
byte[] photo = GetPhoto(photoFilePath);
OleDbConnection Conn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; User Id=;Data
Source=C:\\temp\\testdb.mdb");

OleDbCommand addJpg = new OleDbCommand("update img set img =
@Photo where id = @id", Conn);

OleDbDataAdapter MyDataAdapter = new OleDbDataAdapter();

MyDataAdapter.InsertCommand = addJpg ;

OleDbParameter id = new OleDbParameter ("@id", OleDbType.Integer)
;
id.Value = 1;

OleDbParameter ph = new OleDbParameter ("@Photo",
OleDbType.VarBinary, photo.Length) ;
ph.Value = photo;

MyDataAdapter.InsertCommand .Parameters.Add(ph);
MyDataAdapter.InsertCommand .Parameters.Add(id);

// connect
Conn.Open();
// insert
addJpg.ExecuteNonQuery();

Conn.Close();
//........

Any pointers...
Thanx in Advance
DN