problem on crystal report

Hi all,

Below is my very simple code of using dataset as datasource for crystal report. I have created and generated a dataset at design time.

Code:

---

        Dim oRpt As rptconsult
        oRpt = New rptconsult()
        Try
            DataSet11.Clear()
            SqlDataAdapter1.Fill(DataSet11)
            oRpt.SetDataSource(DataSet11)
            CrystalReportViewer1.ReportSource = oRpt

        Catch Exp As SqlException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

---

But when I run this, the crystalreportviewer doesn't have any data except for the report headings. What have I done wrong?

Kindly help me.

Thanks a lot.

Are you opening your data connection before you fill the dataset? I don't see SqlConnection1.Open() in your code.

Yeah, you're right, I forgot to the open method.

I slightly modify my program to instead of supplying the dataset, I would like to manually populate a dataset based on some criteria and make it the reportsource for my existing crystal report. Here is my code:

Code:

---

Dim MyConnection As New SqlConnection()
        MyConnection.ConnectionString = ConfigurationSettings.AppSettings("VendorConnect")
        Dim oRpt As rptconsult
        oRpt = New rptconsult()
        Try
            MyConnection.Open()
            Dim mysql As String
            Dim grp, dept, from, todate As String
            grp = "00001"
            dept = "021"
            mysql = "select consultfinal.vendorcode, consultfinal.deptname," & _
            "consultfinal.purpose, consultfinal.consultdate," & _
            "vendormst.vendorname, grpdept.deptdesc, grpdept.deptcode, consultfinal.mercode, grpdept.grpcode from consultfinal, grpdept, vendormst " & _
            "where consultfinal.vendorcode = vendormst.vendorcode and " & _
            "grpdept.deptcode=consultfinal.deptcode and " & _
            "consultfinal.consultdate >=" & obj.PrepareStr(dtpfrom.Value) & " and " & _
            "consultfinal.consultdate <=" & obj.PrepareStr(dtpto.Value) & " and " & _
            "grpdept.grpcode=" & obj.PrepareStr(grp) & " and consultfinal.deptcode=" & obj.PrepareStr(dept)
            MessageBox.Show(mysql)

            Dim mydataadapter As New SqlDataAdapter(mysql, MyConnection)
            Dim ds As New DataSet()
            ds.Clear()
            mydataadapter.Fill(ds)
            oRpt.SetDataSource(ds)
            CrystalReportViewer1.ReportSource = oRpt

        Catch Exp As SqlException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

---

But when I run this, I got a query engine error. What could be the cause? Where do I have to look into?

Thanks a lot.

Yeah, you're right, I forgot to the open method.

I slightly modify my program to instead of supplying the dataset, I would like to manually populate a dataset based on some criteria and make it the reportsource for my existing crystal report. Here is my code:

Code:

---

Dim MyConnection As New SqlConnection()
        MyConnection.ConnectionString = ConfigurationSettings.AppSettings("VendorConnect")
        Dim oRpt As rptconsult
        oRpt = New rptconsult()
        Try
            MyConnection.Open()
            Dim mysql As String
            Dim grp, dept, from, todate As String
            grp = "00001"
            dept = "021"
            mysql = "select consultfinal.vendorcode, consultfinal.deptname," & _
            "consultfinal.purpose, consultfinal.consultdate," & _
            "vendormst.vendorname, grpdept.deptdesc, grpdept.deptcode, consultfinal.mercode, grpdept.grpcode from consultfinal, grpdept, vendormst " & _
            "where consultfinal.vendorcode = vendormst.vendorcode and " & _
            "grpdept.deptcode=consultfinal.deptcode and " & _
            "consultfinal.consultdate >=" & obj.PrepareStr(dtpfrom.Value) & " and " & _
            "consultfinal.consultdate <=" & obj.PrepareStr(dtpto.Value) & " and " & _
            "grpdept.grpcode=" & obj.PrepareStr(grp) & " and consultfinal.deptcode=" & obj.PrepareStr(dept)
            MessageBox.Show(mysql)

            Dim mydataadapter As New SqlDataAdapter(mysql, MyConnection)
            Dim ds As New DataSet()
            ds.Clear()
            mydataadapter.Fill(ds)
            oRpt.SetDataSource(ds)
            CrystalReportViewer1.ReportSource = oRpt

        Catch Exp As SqlException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

---

But when I run this, I got a query engine error. What could be the cause? Where do I have to look into?

Thanks a lot.

I've only done this with data from one table and then you write

VB Code:

mydataadapter.Fill(ds, "TableName")

instead of

VB Code:

mydataadapter.Fill(ds)

where the TableName should be the same as in the report. If you don't specify the table name you will get a query engine error.

I'm not sure how to do when you have data from several tables in one dataadapter, but I've seen an example where they used several dataadapters to get the data from one table at the time and then filling the dataset.

VB Code:

mydataadapter1.Fill(ds, "TableName1")
mydataadapter2.Fill(ds, "TableName2")

/Sara