none
is this possible? RRS feed

  • Question

  • can i create a regular report without creating a dataset on the designer? just using this:

            DataTable dt = new DataTable();

            SqlConnection cn = new SqlConnection("Data Source=localhost;Initial Catalog=NorthWind;Integrated Security=True");

     

            private void button1_Click(object sender, EventArgs e)

            {

                string[] strings = new string[] { "Buenos Aires", "Berlin", "New York" };//any number you want

                dt.Clear();

                string sqlQuery = "SELECT  CustomerID, CompanyName, ContactName, ContactTitle, City, Phone FROM Customers WHERE City in(";

                foreach (string s in strings)

                {

                    sqlQuery += "'" + s + "',";

                }

                sqlQuery = sqlQuery.Substring(0, sqlQuery.Length - 1) + ")";

                SqlCommand cm = new SqlCommand(sqlQuery, cn);//use OleDbCommand instead of SqlCommand

                SqlDataAdapter da = new SqlDataAdapter(cm);

                da.Fill(dt);

            }


    If it's really necessary create on the dataaset designer and all table adapters on the  designer, can i change the contente of the query manually ("runtime" in my form code)?

    I am asking this because i've created a dataset with designer and just added a table adapter, but i need to insert a complicated query that i just can implemate hard coding (on form code).

    Thanks
    Wednesday, January 9, 2008 2:50 PM

Answers

  • Thank U Lisa, that help me , but i've had to change a couple of things and it's now live and running!

           Dim sqlConn As OleDb.OleDbConnection = New
    OleDb.OleDbConnection("Provider=IBOLE.Provider.v4;Data
    Source=c:\praia.fdb;Persist Security Info=True;User ID=sysdba")
           Dim Dataset1 As New DataSet()

           Dim squery As String = " SELECT * FROM ... "

           Dim Actividade_Reservas_ArtigoTabl
    eAdapter As
    OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(squery, sqlConn)
           Actividade_Reservas_ArtigoTableAdapter.MissingSchemaAction =
    MissingSchemaAction.AddWithKey
           Actividade_Reservas_ArtigoTableAdapter.Fill(Dataset1,
    "Actividade_Reservas_Artigo")
           ReportViewer1.LocalReport.DataSources.Clear()
           ReportViewer1.LocalReport.DataSources.Add(New
    ReportDataSource("DataSet1_Actividade_Reservas_Artigo",
    Dataset1.Tables(0)))
           ReportViewer1.LocalReport.Refresh()
           ReportViewer1.RefreshReport()

    After this you can delete the tableadapter automatically created and it's done!!

    Tuesday, January 15, 2008 10:03 AM

All replies

  • I do it all the time.  This is how I do it using the MySQL drivers:

            MySqlConnection sqlConn = new MySqlConnection(sConnStr);
            DataSet ds = new DataSet();
            string sQuery = "Select * from .... ";
            MySqlDataAdapter adReport = new MySqlDataAdapter(sQuery, sqlConn);
            adReport.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            adReport.Fill(ds, "TBLConvertedLeads");
            rptViewer.LocalReport.DataSources.Clear();
            rptViewer.LocalReport.DataSources.Add(new ReportDataSource("dsMain_Overview", ds.Tables[0]));
            rptViewer.LocalReport.AddTrustedCodeModuleInCurrentAppDomain("ReportUtils, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null");
            rptViewer.LocalReport.Refresh();


    The key to this is to design the report using a static dataset and table(s).  Once you get the report the way you want it, you can dynamically assign a dataset to the report as shown above, as long as you use the same datasource name ("dsMain_Overview"), and the table has the same columns/names in it as the table in the static dataset does.

    I don't know if you need the "AddTrutedCodeModuleInCurrentAppDomain" call that I use above with drivers other than MySQL.
    Wednesday, January 9, 2008 4:27 PM
  •  

    the table adapaters aren't needed...in fact all the reports I use don't use table adapaters, since our software has it's own dataportal,I'm required to use this to obtain my data.

     

    So when I design my reports, I do use a dataset designer, but I just drag and drop my stored procedure into the designer, and then delete the tabledapater it automatically creates.

     

    The basic idea is that when you give your report the datasource, the names of the dataset and datatable used with the report have to match what it has listed in it's datasources, otherwise it will say you didn't provide it.  

     

    However, if the column required on the report isn't present in the data you provide to the report, naturally it will appear blank.

     

     

    And if you get really good, of course you could build the entire .RDLC at run time...

    Wednesday, January 9, 2008 4:29 PM
  • Hi, thanks both of you fost your post. It was very useful, but am using vb.net and this is my code:

           

    Dim sqlConn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=IBOLE.Provider.v4;Data Source=c:\praia.fdb;Persist Security Info=True;User ID=sysdba")
            Dim Dataset1 As DataSet
            Dim squery As String = " SELECT * FROM ..."

            Dim TestTableAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(squery, sqlConn)
            TestTableAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            TestTableAdapter.Fill(Dataset1, "Test")
            ReportViewer1.LocalReport.DataSources.Clear()
            ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("DataSet1_Test", DataSet1.Tables(0)))
            ReportViewer1.LocalReport.Refresh()

     

    Information about my dataset on designer:

    Dataset = DataSet1
    TableAdapter = TestTableAdapter
    DataTable = Test
    DataSource = DataSet1_Test

     

    At the end i've deleted my TableAdapter and run my report and i get this error:
    Error: System.ArgumentNullException was unhandled
          Parameter name: dataSet

    anybody can help me?
    thanks

    Wednesday, January 9, 2008 10:59 PM
  • This is a double post, right?  Check the other thread...

     

    I think the problem is here (at least partly):

     

    Code Block


    Dim Dataset1 As DataSet

    ' would have to be:

    Dim Dataset1 As New DataSet()

     


    HTH,

     

    >L<

    Monday, January 14, 2008 2:06 AM
  • Thank U Lisa, that help me , but i've had to change a couple of things and it's now live and running!

           Dim sqlConn As OleDb.OleDbConnection = New
    OleDb.OleDbConnection("Provider=IBOLE.Provider.v4;Data
    Source=c:\praia.fdb;Persist Security Info=True;User ID=sysdba")
           Dim Dataset1 As New DataSet()

           Dim squery As String = " SELECT * FROM ... "

           Dim Actividade_Reservas_ArtigoTabl
    eAdapter As
    OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(squery, sqlConn)
           Actividade_Reservas_ArtigoTableAdapter.MissingSchemaAction =
    MissingSchemaAction.AddWithKey
           Actividade_Reservas_ArtigoTableAdapter.Fill(Dataset1,
    "Actividade_Reservas_Artigo")
           ReportViewer1.LocalReport.DataSources.Clear()
           ReportViewer1.LocalReport.DataSources.Add(New
    ReportDataSource("DataSet1_Actividade_Reservas_Artigo",
    Dataset1.Tables(0)))
           ReportViewer1.LocalReport.Refresh()
           ReportViewer1.RefreshReport()

    After this you can delete the tableadapter automatically created and it's done!!

    Tuesday, January 15, 2008 10:03 AM