Mehdi Rostami Mehdi Rostami - 1 month ago 10
C# Question

How to create a dynamic query in a local report(.rdlc) in c#

In my application I have 3 report.rdlc files that records are not different
I am creating a query in a form with options buttons, checkboxes and combobox values

string qry = "";
SqlCeConnection cnn = new

SqlCeConnection(Properties.Settings.Default.ConnectionString.ToString());
SqlCeCommand cmd = new SqlCeCommand();

if (radioButton1.Checked == true)
{
qry = @"Select Did,Cid,Source,Destination,Sid,cost,sdate,Driver.fname+' '+Driver.lname as driver,payed from Service,Driver where Service.Did=Driver.id";
}
else if (radioButton2.Checked == true)
{
button3.Enabled = true;
qry = @"Select Did,Cid,Source,Destination,Sid,cost,sdate,Driver.fname+' '+Driver.lname as driver,payed from Service,Driver where Service.Did=Driver.id and Did=@param ";

cmd.Parameters.Add("@param", CmbDriver.SelectedValue);
}

if (payedRB.Checked == true)
{
qry += @" and Service.payed=1";
}
else if (notpayedRB.Checked == true)
{
qry += @" and Service.payed=0";
}

if (txtDate1.Text != "" && txtDate2.Text != "")
{
qry += @" and Service.sdate between @pdate1 AND @pdate2";
cmd.Parameters.Add("@pdate1", SqlDbType.NVarChar, 20).Value = txtDate1.Text.Trim();
cmd.Parameters.Add("@pdate2", SqlDbType.NVarChar, 20).Value = txtDate2.Text.Trim();
state = 3;
}
else if (txtDate1.Text != "")
{
qry += @" and Service.sdate >= @pdate1";
cmd.Parameters.Add("@pdate1", SqlDbType.NVarChar, 20).Value = txtDate1.Text.Trim();
state = 1;
}
else if (txtDate2.Text != "")
{
qry += @" and Service.sdate <= @pdate2";
cmd.Parameters.Add("@pdate2", SqlDbType.NVarChar, 20).Value = txtDate2.Text.Trim();
state = 2;
}

if (activdr.Checked == true)
{
qry += @" and Driver.isActive=1";
}
else
{
qry += @" and Driver.isActive=0";
}

qry += @" ORDER BY Service.sdate desc,Service.Sid desc";

cmd.Connection = cnn;
cmd.CommandText = qry;
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);


cnn.Open();

adapter.Fill(dt);......


I used reporttype variable in option buttons event to know which
report will be shown in report viewer. and set selected records to
data grid viewer

my reports get data from a temp table that is compatible with record's
field. before this I insert all records( that will be printed in the report
viewer) into temp table;

How can I transfer records into reports without temp table;

Answer

In local reports it is not possible to have the report itself query the database. Your application needs to query the database and pass the results to the report as a data source.

You need to fill a data set now and pass this as a DataSource to the report. The report needs to define a respective data source to provide values for the fields.