Click here to Skip to main content
15,888,106 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
First and foremost, I apologize for my grammatical errors; my first language is Persian (Iran).

I use the following code to insert the image and retrieve it from the Access database, and I have no problem retrieving it after inserting the image.

C#
BitmapImage BM;
private void UploadButton_PreviewMouseLeftButtonUp(object sender, MouseButtonEventArgs e)
{
 System.Windows.Forms.OpenFileDialog OpenFileDialog = new System.Windows.Forms.OpenFileDialog();
 OpenFileDialog.AutoUpgradeEnabled = false;
 if (App.EnumLanguage.Equals(AllLanguage.English))
 {
     OpenFileDialog.Title = "Selecting Image";
 }
 else
 {
     OpenFileDialog.Title = "انتخاب تصویر";
 }
 OpenFileDialog.Filter = "JPG(*.jpg)|*.jpg|BMP(*.bmp)|*.bmp|GIF(*.gif)|*.gif|PNG(*.png)|*.png|All Files|*.*";
 if (OpenFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
 {
     var IMG = System.Drawing.Image.FromFile(OpenFileDialog.FileName);
     BM = new BitmapImage(new Uri(OpenFileDialog.FileName));
     BitmapImage BitMapImage = new BitmapImage();
     BitMapImage.BeginInit();
     System.IO.MemoryStream MemoryStream = new System.IO.MemoryStream();
     IMG.Save(MemoryStream, System.Drawing.Imaging.ImageFormat.Bmp);
     MemoryStream.Seek(0, System.IO.SeekOrigin.Begin);
     BitMapImage.StreamSource = MemoryStream;
     BitMapImage.EndInit();
     BookImage.Source = BitMapImage;
     OpenFileDialog.Dispose();
 }
}
private static byte[] ImageToBytes(BitmapImage image)
{
 byte[] Data;
 JpegBitmapEncoder JpegEncoder = new JpegBitmapEncoder();
 JpegEncoder.Frames.Add(BitmapFrame.Create(image));
 using (System.IO.MemoryStream MS = new System.IO.MemoryStream())
 {
     JpegEncoder.Save(MS);
     Data = MS.ToArray();
 }
 return Data;
}
private BitmapImage GetImageFromBytes(byte[] bytes)
{
 System.IO.MemoryStream Stream = new System.IO.MemoryStream();
 Stream.Write(bytes, 0, bytes.Length);
 Stream.Position = 0;
 System.Drawing.Image img = System.Drawing.Image.FromStream(Stream);
 BitmapImage bitImage = new BitmapImage();
 bitImage.BeginInit();
 System.IO.MemoryStream MS = new System.IO.MemoryStream();
 img.Save(MS, System.Drawing.Imaging.ImageFormat.Jpeg);
 MS.Seek(0, System.IO.SeekOrigin.Begin);
 bitImage.StreamSource = MS;
 bitImage.EndInit();
 return bitImage;
}
private void Add_Button_PreviewMouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
 OleDbConnect.Open();
 if (BM != null)
 {
     byte[] Image_Bytes = ImageToBytes(BM);
     OleDbParameter Parameter = new OleDbParameter();
     Parameter.OleDbType = OleDbType.Binary;
     Parameter.ParameterName = "Image";
     Parameter.Value = Image_Bytes;
     OleDbCommand OleDbCommand_Insert = new OleDbCommand("Insert Into [BookTable](BookName,Publisher,Category,IDNumber,Status,HistoryTaken,RecipientName,ReturnDate,BookImage)values('" + BookName_TextBox.Text + "','" + Publisher_TextBox.Text + "','" + Category_ComboBox.Text + "','" + IDNumber_TextBox.Text + "','" + Status_ComboBox.Text + "','" + HistoryTaken_TextBox.Text + "','" + RecipientName_TextBox.Text + "','" + ReturnDate_TextBox.Text + "',@Image)", OleDbConnect);
     OleDbCommand_Insert.Parameters.Add(Parameter);
     OleDbCommand_Insert.ExecuteScalar();
 }
 OleDbConnect.Close();
}

\\\ retrieving information from Access Database
OleDbCommand OleDCmd = new OleDbCommand("Select * From BookTable Where IDNumber='" + Search_ComboBox.Text.Trim() + "'", OleDbConnect);
OleDCmd.CommandType = System.Data.CommandType.Text;
OleDbConnect.Open();
OleDbDataReader DataReader = OleDCmd.ExecuteReader();
while (DataReader.Read())
{
  BookName_TextBox.Text = DataReader[0].ToString();
  Publisher_TextBox.Text = DataReader[1].ToString();
  Category_ComboBox.Text = DataReader[2].ToString();
  IDNumber_TextBox.Text = DataReader[3].ToString();
  Status_ComboBox.Text = DataReader[4].ToString();
  HistoryTaken_TextBox.Text = DataReader[5].ToString();
  RecipientName_TextBox.Text = DataReader[6].ToString();
  ReturnDate_TextBox.Text = DataReader[7].ToString();
  BitmapImage BMP = GetImageFromBytes((byte[])DataReader[8]);
  BookImage.Source = BMP;
}


But when I use the following commands to update the image and other information, this error is displayed when I want to retrieve the information.

C#
byte[] Image_Bytes = ImageToBytes(BM);
OleDbParameter Parameter = new OleDbParameter();
Parameter.OleDbType = OleDbType.Binary;
Parameter.ParameterName = "Image";
Parameter.Value = Image_Bytes;
OleDbCommand OleDbCommand_Update = new OleDbCommand("Update [BookTable] Set BookName='"+BookName_TextBox.Text.Trim()+"',Publisher='"+Publisher_TextBox.Text.Trim()+"',Category='"+Category_ComboBox.Text.Trim()+"',Status='"+Status_ComboBox.Text.Trim()+"',HistoryTaken='"+HistoryTaken_TextBox.Text.Trim()+"',RecipientName='"+RecipientName_TextBox.Text.Trim()+"',ReturnDate='"+ReturnDate_TextBox.Text.Trim() +"',BookImage='"+ (BitmapImage)BookImage.Source + "'Where IDNumber='" + IDNumber_TextBox.Text.Trim()+ "'", OleDbConnect);
                 OleDbCommand_Update.Parameters.Add(Parameter);
                 OleDbCommand_Update.ExecuteScalar();


System.ArgumentException: 'Parameter is not valid.' Line 6 gives an error

C#
 private BitmapImage GetImageFromBytes(byte[] bytes)
{
System.IO.MemoryStream Stream = new System.IO.MemoryStream();
Stream.Write(bytes, 0, bytes.Length);
Stream.Position = 0;
System.Drawing.Image img = System.Drawing.Image.FromStream(Stream);\\System.ArgumentException: 'Parameter is not valid.'
BitmapImage bitImage = new BitmapImage();
bitImage.BeginInit();
System.IO.MemoryStream MS = new System.IO.MemoryStream();
img.Save(MS, System.Drawing.Imaging.ImageFormat.Jpeg);
MS.Seek(0, System.IO.SeekOrigin.Begin);
bitImage.StreamSource = MS;
bitImage.EndInit();
return bitImage;
}


In short: in insert command i have no problem to retrieve image but in update command i have problem to retrieve image.
In my opinion, the Update command does not save the image properly in the Access database.

Thanks

What I have tried:

C#
byte[] Image_Bytes = ImageToBytes(BM);
OleDbParameter Parameter = new OleDbParameter();
Parameter.OleDbType = OleDbType.Binary;
Parameter.ParameterName = "Image";
Parameter.Value = Image_Bytes;
OleDbCommand OleDbCommand_Update = new OleDbCommand("Update [BookTable] Set BookName='"+BookName_TextBox.Text.Trim()+"',Publisher='"+Publisher_TextBox.Text.Trim()+"',Category='"+Category_ComboBox.Text.Trim()+"',Status='"+Status_ComboBox.Text.Trim()+"',HistoryTaken='"+HistoryTaken_TextBox.Text.Trim()+"',RecipientName='"+RecipientName_TextBox.Text.Trim()+"',ReturnDate='"+ReturnDate_TextBox.Text.Trim() +"',BookImage='"+ (BitmapImage)BookImage.Source + "'Where IDNumber='" + IDNumber_TextBox.Text.Trim()+ "'", OleDbConnect);
OleDbCommand_Update.Parameters.Add(Parameter);
OleDbCommand_Update.ExecuteScalar();
Posted
Updated 5-Apr-22 7:29am
v4
Comments
[no name] 14-Feb-21 14:16pm    
Just try updating "only" the image (first) ... you have too much going on for "I'm just updating the image".
Reza jafery 14-Feb-21 14:30pm    
If the user needs to update both (texts and image) at the same time, I want my app to have this feature.

Simple: you updated your DB with bad data. Start here: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^]

And then you also need to change the whole of your app to prevent the SQL Injection you have made it wide open to. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
 
Share this answer
 
C#
OleDbCommand OleDbCommand_Update = new OleDbCommand("Update [BookTable] Set BookName='"+BookName_TextBox.Text.Trim()+"',Publisher='"+Publisher_TextBox.Text.Trim()+"',Category='"+Category_ComboBox.Text.Trim()+"',Status='"+Status_ComboBox.Text.Trim()+"',HistoryTaken='"+HistoryTaken_TextBox.Text.Trim()+"',RecipientName='"+RecipientName_TextBox.Text.Trim()+"',ReturnDate='"+ReturnDate_TextBox.Text.Trim() +"',BookImage='"+ (BitmapImage)BookImage.Source + "'Where IDNumber='" + IDNumber_TextBox.Text.Trim()+ "'", OleDbConnect);

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Secondary problem, it is impossible to know what is exactly your query because it depend on the values of the parameters.
Only the debugger can show what is the real query.
 
Share this answer
 
Hooray, hooray I found the solution (I found it myself).if IDNumber data type as be number data type in Access Database, this code works 100%.
Just Replace this code...

C#
OleDbCommand OleDbCommand_Update = new OleDbCommand("Update [BookTable] Set BookName='"+BookName_TextBox.Text.Trim()+"',Publisher='"+Publisher_TextBox.Text.Trim()+"',Category='"+Category_ComboBox.Text.Trim()+"',Status='"+Status_ComboBox.Text.Trim()+"',HistoryTaken='"+HistoryTaken_TextBox.Text.Trim()+"',RecipientName='"+RecipientName_TextBox.Text.Trim()+"',ReturnDate='"+ReturnDate_TextBox.Text.Trim() +"',BookImage='"+ "@Image" + "'Where IDNumber='" + IDNumber_TextBox.Text.Trim()+ "'", OleDbConnect);


With the following code (this is how the UPDATE Query command should be written)

C#
OleDbCommand OleDbCommand_Update = new OleDbCommand("Update [BookTable] Set BookImage=Image,BookName='" +BookWindow.BookName_TextBox.Text.Trim() + "',Publisher='" + BookWindow.Publisher_TextBox.Text.Trim() + "',Category='" + BookWindow.Category_ComboBox.Text.Trim() + "',Status='" + BookWindow.Status_ComboBox.Text.Trim() + "',HistoryTaken='" + BookWindow.HistoryTaken_TextBox.Text.Trim() + "',RecipientName='" + BookWindow.RecipientName_TextBox.Text.Trim() + "',ReturnDate='" + BookWindow.ReturnDate_TextBox.Text.Trim() + "'Where IDNumber=" + BookWindow.IDNumber_TextBox.Text.Trim(), OleDbConnect);


I tested a lot until I finally got the result. Thanks a lot for your help.
 
Share this answer
 
v3
Comments
Richard Deeming 17-Feb-21 6:43am    
You have ignored the advice of the previous two solutions, and left your code vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900