I said you are a legend, your solution is working fine for me. Finally i have only one question now i have a DataSet with multiple number of Tables here i want to bind all table records into the .rdlc file, now i get this error
An error occurred during local report processing. The definition of the report 'Main Report' is invalid. The Value expression for the textbox ‘imgName’ refers to the field ‘imgName’. Report item expressions can only refer to fields within the current
data set scope or, if inside an aggregate, the specified data set scope.
imgName is the columnname in Table2. Where will i change the code for MultipleTables. Please guide me
Thanks in advance
Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
J.Jeyaseelan
I'm not sure how you did the binding for multiple tables and what exactly do you want to generate. You should check the following things:
In RdlGenerator.cs there is a method CreateDataSet(). This one create a dataset "MyData". If you do create multiple datasets for your report then you must specify for the Table created what dataset it should use. You cannot mix datasets and refer them for
a specified table within the report. When you want to use imgName in a certain table the dataset associated with the table should contain that field. So, when you decide to create mutiple tables (using different datasets) in your report you must change public
Rdl.TableType CreateTable() and add a parameter dataset name. You need to add to the table items list: ItemsChoiceType21.DataSetName to make sure that your table uses data from the right data set.
I'm not sure that I understood exaclty what you intend to do, but I hope this will help.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Really thanks for your immediate response. Assume I have only one DataSet this have 2 Tables each table have only one record so that i change my .aspx.cs code like this
m_dataSet = new
DataSet();SqlConnection con =
new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JDB1ConnectionString1"].ToString().Trim());
con.Open();
SqlDataAdapter sda =
new SqlDataAdapter("Select imgId,imgData From User_Images", con);
sda.Fill(m_dataSet,"Table1");
List<string> allFields = GetAvailableFields();
SqlDataAdapter sda1 =
new SqlDataAdapter("Select imgName from User_images", con);
sda1.Fill(m_dataSet,
"Table2");
List<string> selectedFields =
new List<string>();//= GetSelectedFields();
selectedFields.Add("imgId");
selectedFields.Add(
"imgData");
selectedFields.Add("imgName");
if (m_rdl != null)
m_rdl.Dispose();
m_rdl = GenerateRdl(allFields, selectedFields);
DumpRdl(m_rdl);
ShowReport();
}
catch (Exception ex)
{
throw ex;
}
}
Then it raise an error as
Could you please help me how to pass multiple tables with one dataset
Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
J.Jeyaseelan
You should grab data in one select only. Something like:
Select imgId, imgData, imgName From User_Images
and use only one dataset. What you did in ShowReport() by adding two times the reportdatasource ("MyData") is not correct and only the first table will be taken into account. I don't understand why do you use two selects? I see that you select data from
the same table. Is that right?
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Thanks for your response. I come to your way for example i get the imgName from other table namely Table2. here user_images and Table2 has no relationships, then i should write a different query and add different number of table(Each
table have only one record but different number of columns) to a DataSet right. This time how to display all dataset records into the rdlc. Could you please tell me how to do this? (In my previous post why i write a query as retrieve data from same table
it's just for example).
How i expect the output is
Table 1
ImgId ImgData
Data1 Data1
ImgName
Data1
Table 2
ImgPath ImgSize
Data2 Data2
ImgType
Data2
So one more question is how to place a field in new line? Please help me..
Thanks in advance.
Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
J.Jeyaseelan
Please tell how the report looks in the end. Is this a report that contains one list of items with an image on every row? It is important to know how the report looks in order to decide how to manage the report datasource(s). What you did post as the last
code example for report datasources is not enough to have a good understanding of what you try to present in the report.
Cheers,
Florin Labou
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Thanks for your reply actualy first add two tables to the DataSet
private
void OpenDataFile()
{
try
{
m_dataSet = new
DataSet();SqlConnection con =
new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JDB1ConnectionString1"].ToString().Trim());
con.Open();
SqlDataAdapter sda =
new SqlDataAdapter("Select imgId,imgData From User_Images", con);
sda.Fill(m_dataSet,"Table1");
List<string> allFields = GetAvailableFields();
SqlDataAdapter sda1 =
new SqlDataAdapter("Select imgName from User_images", con);
sda1.Fill(m_dataSet,
"Table2");
List<string> selectedFields =
new List<string>();//= GetSelectedFields();
selectedFields.Add("imgId");
selectedFields.Add(
"imgData");
selectedFields.Add("imgName");
if (m_rdl != null)
m_rdl.Dispose();
m_rdl = GenerateRdl(allFields, selectedFields);
DumpRdl(m_rdl);
ShowReport();
}
private List<string> GetAvailableFields()
{
DataTable dataTable = m_dataSet.Tables[0];
List<string> availableFields =
new List<string>();
for (int i = 0; i < dataTable.Columns.Count; i++)
jeyaseelan@a...
Contributor
5124 Points
2025 Posts
Re: Dynamic Reports with sql reporting services
Sep 17, 2008 08:48 AM|LINK
Hi Florinlabou,
I said you are a legend, your solution is working fine for me. Finally i have only one question now i have a DataSet with multiple number of Tables here i want to bind all table records into the .rdlc file, now i get this error
An error occurred during local report processing. The definition of the report 'Main Report' is invalid. The Value expression for the textbox ‘imgName’ refers to the field ‘imgName’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
imgName is the columnname in Table2. Where will i change the code for MultipleTables. Please guide me
Thanks in advance
J.Jeyaseelan
florinlabou
Contributor
3120 Points
496 Posts
Re: Dynamic Reports with sql reporting services
Sep 17, 2008 09:26 AM|LINK
I'm not sure how you did the binding for multiple tables and what exactly do you want to generate. You should check the following things:
In RdlGenerator.cs there is a method CreateDataSet(). This one create a dataset "MyData". If you do create multiple datasets for your report then you must specify for the Table created what dataset it should use. You cannot mix datasets and refer them for a specified table within the report. When you want to use imgName in a certain table the dataset associated with the table should contain that field. So, when you decide to create mutiple tables (using different datasets) in your report you must change public Rdl.TableType CreateTable() and add a parameter dataset name. You need to add to the table items list: ItemsChoiceType21.DataSetName to make sure that your table uses data from the right data set.
I'm not sure that I understood exaclty what you intend to do, but I hope this will help.
jeyaseelan@a...
Contributor
5124 Points
2025 Posts
Re: Dynamic Reports with sql reporting services
Sep 17, 2008 10:26 AM|LINK
Hi Florinlabou,
Really thanks for your immediate response. Assume I have only one DataSet this have 2 Tables each table have only one record so that i change my .aspx.cs code like this
private void ShowReport(){
this.ReportViewer1.Reset(); this.ReportViewer1.LocalReport.LoadReportDefinition(m_rdl); this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("MyData", m_dataSet.Tables[0])); this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("MyData", m_dataSet.Tables[1])); //this.ReportViewer1.RefreshReport();}
private void OpenDataFile(){
try{
m_dataSet = new DataSet();SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JDB1ConnectionString1"].ToString().Trim());con.Open();
SqlDataAdapter sda = new SqlDataAdapter("Select imgId,imgData From User_Images", con); sda.Fill(m_dataSet,"Table1"); List<string> allFields = GetAvailableFields(); SqlDataAdapter sda1 = new SqlDataAdapter("Select imgName from User_images", con);sda1.Fill(m_dataSet,
"Table2"); List<string> selectedFields = new List<string>();//= GetSelectedFields(); selectedFields.Add("imgId");selectedFields.Add(
"imgData"); selectedFields.Add("imgName");
if (m_rdl != null)m_rdl.Dispose();
m_rdl = GenerateRdl(allFields, selectedFields);
DumpRdl(m_rdl);
ShowReport();
}
catch (Exception ex){
throw ex;}
}
Then it raise an error as
Could you please help me how to pass multiple tables with one dataset
J.Jeyaseelan
florinlabou
Contributor
3120 Points
496 Posts
Re: Dynamic Reports with sql reporting services
Sep 17, 2008 10:39 AM|LINK
You should grab data in one select only. Something like:
Select imgId, imgData, imgName From User_Images
and use only one dataset. What you did in ShowReport() by adding two times the reportdatasource ("MyData") is not correct and only the first table will be taken into account. I don't understand why do you use two selects? I see that you select data from the same table. Is that right?
jeyaseelan@a...
Contributor
5124 Points
2025 Posts
Re: Dynamic Reports with sql reporting services
Sep 17, 2008 10:56 AM|LINK
Hi Florinlabou,
Thanks for your response. I come to your way for example i get the imgName from other table namely Table2. here user_images and Table2 has no relationships, then i should write a different query and add different number of table(Each table have only one record but different number of columns) to a DataSet right. This time how to display all dataset records into the rdlc. Could you please tell me how to do this? (In my previous post why i write a query as retrieve data from same table it's just for example).
How i expect the output is
Table 1
ImgId ImgData
Data1 Data1
ImgName
Data1
Table 2
ImgPath ImgSize
Data2 Data2
ImgType
Data2
So one more question is how to place a field in new line? Please help me..
Thanks in advance.
J.Jeyaseelan
florinlabou
Contributor
3120 Points
496 Posts
Re: Dynamic Reports with sql reporting services
Sep 17, 2008 11:13 AM|LINK
You can easily write a single query on two tables like this:
Select imgId, imgData, imgName From User_Images, Table2
where Table2 is your second table, you should replace it with the exact table name.
jeyaseelan@a...
Contributor
5124 Points
2025 Posts
Re: Dynamic Reports with sql reporting services
Sep 17, 2008 12:16 PM|LINK
Hi Florinlabou,
Please tell me how to bind the n number of tables to the .rdlc. Now i change the code like this
private void ShowReport(){
this.ReportViewer1.Reset(); this.ReportViewer1.LocalReport.LoadReportDefinition(m_rdl); for (int i = 0; i < 2; i++){
this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("MyData", m_dataSet.Tables[i]));}
}
Now the column is displaying without Data. Please guide me.
J.Jeyaseelan
jeyaseelan@a...
Contributor
5124 Points
2025 Posts
Re: Dynamic Reports with sql reporting services
Sep 18, 2008 04:58 AM|LINK
Hai Florinlabou,
Are you there? please guide me how to design like this?
J.Jeyaseelan
florinlabou
Contributor
3120 Points
496 Posts
Re: Dynamic Reports with sql reporting services
Sep 18, 2008 06:19 AM|LINK
Hi Jeyaseelan,
Please tell how the report looks in the end. Is this a report that contains one list of items with an image on every row? It is important to know how the report looks in order to decide how to manage the report datasource(s). What you did post as the last code example for report datasources is not enough to have a good understanding of what you try to present in the report.
Cheers,
Florin Labou
jeyaseelan@a...
Contributor
5124 Points
2025 Posts
Re: Dynamic Reports with sql reporting services
Sep 18, 2008 08:09 AM|LINK
Hai Florinlabou,
Thanks for your reply actualy first add two tables to the DataSet
private void OpenDataFile(){
try{
m_dataSet = new DataSet();SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JDB1ConnectionString1"].ToString().Trim());con.Open();
SqlDataAdapter sda = new SqlDataAdapter("Select imgId,imgData From User_Images", con); sda.Fill(m_dataSet,"Table1"); List<string> allFields = GetAvailableFields(); SqlDataAdapter sda1 = new SqlDataAdapter("Select imgName from User_images", con);sda1.Fill(m_dataSet,
"Table2"); List<string> selectedFields = new List<string>();//= GetSelectedFields(); selectedFields.Add("imgId");selectedFields.Add(
"imgData"); selectedFields.Add("imgName");
if (m_rdl != null)m_rdl.Dispose();
m_rdl = GenerateRdl(allFields, selectedFields);
DumpRdl(m_rdl);
ShowReport();
}
private List<string> GetAvailableFields(){
DataTable dataTable = m_dataSet.Tables[0]; List<string> availableFields = new List<string>(); for (int i = 0; i < dataTable.Columns.Count; i++){
availableFields.Add(dataTable.Columns[i].ColumnName);
}
availableFields.Add("imgName");return availableFields;}
private void ShowReport(){
this.ReportViewer1.Reset();
this.ReportViewer1.LocalReport.LoadReportDefinition(m_rdl); for (int i = 0; i < 2; i++){
this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("MyData", m_dataSet.Tables[i]));}
//this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("MyData", m_dataSet));}
This is my code, here how can i pass Two Tables, that is my problem. Could you please explain me how to pass two tables
J.Jeyaseelan