Working with CRViewer trying to use push method

[WalterHeisenberg]

Hello,

I'm not very familiar with .net so bare with me. I've been following some different guides online with various results and finally think I am close.

Basically, I created a new web site project, I created a dataset (dataset1) and associated objects via wizard. I created a CrystalReport that uses this dataset as its datasource and has just the customer line in the details section.

Now, I'm hoping to dynamically be able to change this dataset so I can use this same template for various different reports. I added the following into my page load event:

     Dim myConnection As New Data.SqlClient.SqlConnection()
        myConnection.ConnectionString = "server= PYRAMID;database=CUSMNG;Trusted_Connection=yes"

        Dim MyCommand As New Data.SqlClient.SqlCommand()
        MyCommand.Connection = myConnection
        MyCommand.CommandText = "Select * from Customers where customer like 'ABC INC'"
        MyCommand.CommandType = Data.CommandType.Text

        Dim MyDA As New Data.SqlClient.SqlDataAdapter()
        Dim ConnInfo As New ConnectionInfo
        MyDA.SelectCommand = MyCommand

        Dim myDS As New DataSet1()
        MyDA.Fill(myDS, "DataSet1")

        Dim oRpt As New ReportDocument()
        oRpt.Load(MapPath("CrystalReport.rpt"))
        oRpt.SetDataSource(myDS)
        CrystalReportViewer1.ReportSource = oRpt

When I run this it opens the crystal report but only shows the customer header not any of the details that should show up. Can anyone get me pointed in the right direction? Thanks!

Ariel

 

[jbenson001]

To find the problem you should step through the code and debug. Check the dataset and check the table count after the fill. Also check the table in the dataset that should have data and check the rowcount.

I suspect the problem is you are getting now rows back because of the LIKE clause you have. To use LIKE in sql server you need to use '%' signs as wild cards:

LIKE '%ABC INC' will return any row with customers that end in "ABC INC'

LIKE 'ABC INC%' will return any row with customers that begin with ABC INC

LIKE '%ABC INC%' will return any row with custers that have ABC INC somewhere within the column.

LIKE 'ABC INC' will return nothing.

Also, you should be writing can calling stored procedures(with parameters) for any database manipulation you may be doing. This will prevent any possiblity of sql injection attacks.

 

[WalterHeisenberg]

I'll take your advice of stepping through and seeing what happens. I know the like isn't the culprit as I had tested this with select * and got the same results. I'll report back if I find anything.

 

[jbenson001]

The like is still incorrect. See my post. If you get it working with select * then go back to your like, it won't return any rows.

 

[jmeckley]

you need to include wildcards for the like to work

select [columns] from [table] where [column] like @this

where [tt]@this[/tt] would have a value like "%abc", "%abc%" or "abc%".

you can change the data that gets pushed to the dataset, but you cannot change the structure of the dataset at runtime. the tables/relations/columns remain the same, but how you populate the dataset is entirely up to you.

I have used this technique in the past. I have three models.
The view model (how the report sees the data)
the domain model (how the system behaves)
the database model (how the data is stored)
using various frameworks and simple mapping objects i translate my database structure into a view model structure for the report to consume.

now, if you currently have a report that shows a list of customers and now you want it to show order with line detail. That is best handled by creating two distinct reports. One for customers another for order details. you can then reference the reports dynamically and load them into the CR Viewer.

personally I can't stand the CR View. since the app is web based consider streaming the report as a PDF to the client's browser and bypass webforms altogether using a generic handler (ashx). There are examples on the web of how to do this.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[WalterHeisenberg]

Hi guys,

Sorry I didn't phrase my last post very well. I removed the like clause all together and still was unable to get data so I'm sure it's not the SQL. I added a grid view and sure enough the data shows there.

Basically, all we're trying to do is use the CR View for graphs. My boss wants to be able to build the columns/rows by themselves and then just send it to the CR Viewer to display. If I am understanding your post correctly Jason, we would need to have a crystal report already setup for each different variation of what we want to show the user is that correct? If so it seems like it is just easier to write the report in crystal and be done with it. Am I missing something?