I need to show the image dynamically in Crystal Report from MySQL image path in c# (Visual studio 2008). I have stored the image path in MySQL DB, but couldnt able to display the image on the CR. Code which i have tested is not working. Lots of goggling done already in this, cant find a solutions still. Please help..!!
DataTable dt = new DataTable();
string Student = "SELECT `photo_path` FROM `tc` WHERE `register_no`= '" + Reg_No + "';";
// MySqlConnection con = new MySqlConnection(conn);
//con.Open();
MySqlCommand cmd1 = new MySqlCommand(Student, conn);
MySqlDataReader Detail1 = cmd1.ExecuteReader();
ReportDocument rptDoc = new ReportDocument();
MySqlDataAdapter da = new MySqlDataAdapter(); // .xsd file name
da.SelectCommand = cmd1;
da.Fill(dt);
while (Detail1.Read())
{
string image = Detail1.GetString(0);
}
Detail1.Close();
conn.Close();
dt.Columns.Add("Image", System.Type.GetType("System.Byte[]"));
FileStream fs;
//if (File.Exists(Image))
//{
// fs = new FileStream(Image, FileMode.Open);
//}
//else
//{
// MessageBox.Show("Please File Not Exist");
//}
BinaryReader br = new BinaryReader(fs);
byte[] imgbyte = new byte[fs.Length + 1];
imgbyte = br.ReadBytes(Convert.ToInt32((fs.Length)));
foreach (DataRow dr in dt.Rows)
{
dr["Image"] = imgbyte;
}
TC_Report myReport = new TC_Report();
myReport.SetDataSource(dt);
crystalReportViewer1.ReportSource = myReport;
crystalReportViewer1.Refresh();
I am adding the full code from the form load of crystal report viewer. Now i am getting a error like "you can provide a single value for this parameter" and asks for the discrete value, if i give any value it doesn't show any report . It generates report with data but not the image if i command the lines below the /////////// which i have marked in the code. I really having a very tough time with this crystal report showing image from database. Whats wrong with the code please help..
//creating an object of Report Document class
ReportDocument reportDocument = new ReportDocument();
//creating an object of ParameterField class
ParameterField paramField = new ParameterField();
//creating an object of ParameterFields class
ParameterFields paramFields = new ParameterFields();
//creating an object of ParameterDiscreteValue class
ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
paramField.Name = "StudentTC";
//set the parameter value
paramDiscreteValue.Value = Reg_No;
//add the parameter value in the ParameterField object
paramField.CurrentValues.Add(paramDiscreteValue);
//add the parameter in the ParameterFields object
paramFields.Add(paramField);
//set the parameterfield information in the crystal report
crystalReportViewer1.ParameterFieldInfo = paramFields;
MySqlConnection conn;
MySqlCommand cmd;
MySqlDataAdapter adap;
// MessageBox.Show(Reg_No);
// Code to get data from database
conn = new MySqlConnection("Server=localhost; Database=mcet; User ID=root; Password=; charset=utf8;");
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT T2.photo_path,title, firstname, father_name, mother_name, gender, DATE_FORMAT(dob, '%d-%m-%Y') AS DOB, dobw, nationality, e_mail, religion, caste, community, admission_no, degree, department,DATE_FORMAT(doa, '%d-%m-%Y') AS DOA, DATE_FORMAT(dol, '%d-%m-%Y') AS DOL, lentry FROM student_details T1 LEFT JOIN tc T2 ON T1.register_no = T2.register_no where T1.register_no='" + Reg_No + "' ";
adap = new MySqlDataAdapter();
adap.SelectCommand = cmd;
TC_Dataset StudentDB = new TC_Dataset();
//var StudentDB = new DataSet();
StudentDB.Clear();
adap.Fill(StudentDB, "Student");
// Create a CrystalReport1 object
TC_Report myReport = new TC_Report();
// Set the DataSource of the report
myReport.SetDataSource(StudentDB);
// Set the Report Source to ReportView
crystalReportViewer1.ReportSource = myReport;
// crystalReportViewer1.Refresh();
///////////////////////////////////////////////////////////////////////////////////////////////
DataTable dt = new DataTable();
MySqlDataReader Detail1 = cmd.ExecuteReader();
ReportDocument rptDoc = new ReportDocument();
while (Detail1.Read())
{
string image = Detail1.GetString(0);
}
Detail1.Close();
MySqlDataAdapter da = new MySqlDataAdapter(); //
da.SelectCommand = cmd;
da.Fill(dt);
conn.Close();
dt.Columns.Add("Image", System.Type.GetType("System.Byte[]"));
FileStream fs;
fs = new FileStream("E:\\student\\Photo\\" + Reg_No + ".jpg", FileMode.Open);
//string path = Path.Combine("E:\\student\\Photo\\", Reg_No) + ".jpg";
//if (File.Exists(path))
//{
// fs = new FileStream("E:\\student\\Photo\\" + Reg_No + ".jpg", FileMode.Open);
//}
//else
//{
// MessageBox.Show("Please File Not Exist");
//}
BinaryReader br = new BinaryReader(fs);
byte[] imgbyte = new byte[fs.Length + 1];
imgbyte = br.ReadBytes(Convert.ToInt32((fs.Length)));
foreach (DataRow dr in dt.Rows)
{
dr["Image"] = imgbyte;
}
TC_Report myReport1 = new TC_Report();
myReport1.SetDataSource(dt);
crystalReportViewer1.ReportSource = myReport1;
crystalReportViewer1.Refresh();
@utility, Thanks for helping me out to get this work done. This code is for storing the image as Blob datatype in MySql and bring it into CR using parameter dynamically.
MySqlConnection conn;
MySqlCommand cmd;
MySqlDataAdapter adap;
// Code to get data from database
conn = new MySqlConnection("Server=localhost; Database=dbname; User ID=root; Password=; charset=utf8;");
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT photo_path,title, firstname FROM student_details WHERE `register_no`='" + Reg_No + "' ";
adap = new MySqlDataAdapter();
adap.SelectCommand = cmd;
TC_Dataset StudentDB = new TC_Dataset(); //TC_Dataset is the Dataset name, which you have created.
StudentDB.Clear();
adap.Fill(StudentDB, "TC_dt"); // TC_dt is the DataTable name in the Dataset
// Create a CrystalReport1 object
TC_Report myReport = new TC_Report(); //TC_Report is the name of the Crystal Report (rpt file)
TextObject txt1 = (TextObject)myReport.ReportDefinition.Sections["Section1"].ReportObjects["Text68"]; **// this is used to send the data from text box to CR (Text68) is the textfield name in CR, Year_leaving contains the date of the textbox as string.**
txt1.Text = Year_leaving;
TextObject txt2 = (TextObject)myReport.ReportDefinition.Sections["Section1"].ReportObjects["Text21"];
txt2.Text = reason_leaving;
// Set the DataSource of the report
myReport.SetDataSource(StudentDB);
// Set the Report Source to ReportView
crystalReportViewer1.ReportSource = myReport; //crystalReportViewer1 is the CR Viewer name which is in the form