Dynamic Listview Items to Crystal Report in VB 2008

[Former Member]

Hi Everybody

I have a Visual Basic 2008 Project and I'm developing this solution for my company. This project have an access 2007 database and I have built database connection with Ole.DB connection. So I have a listbox (lstsearchad) to display results from database.

This listbox generate results with SELECT queries. Actually there are few Select Queries to generate this listbox (lstsearchad). According to user's choice he get various results from this listbox (lstsearchad) and after that listbox (lstsearchad) have sometimes 3 columns, Sometimes 4 columns and sometimes 6 columns.

Now I have the problem. I want to get these results to CR Viewer and I need get a Printed version of this Listbox (lstsearchad) results. I'm trying to get this result to Cr Viewer by Dataset. But It doesn't success. This listbox (lstsearchad) generate columns differently from database. because I can't get an idea how to design a crystal report or something like that. If anyone can help me with this issue I'm very appreciate it very much and more. (I had search many codes in google but that codes doesn't success with this issue.) Following are the SQL Queries to generate this listbox (lstserachad). Don't forget I'm a beginner for Crystal Report and I didn't know much more things about Crystal Reports. 

            If frmadvancef.lblvarvalue.Text = 15 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%' and Certificate_Level Like '%%" & frmadvancef.cmbcertficatead.Text.Trim.ToString() & "%%'"

                'Certification Only

            ElseIf frmadvancef.lblvarvalue.Text = 45 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%' and Job_Check Like '%%" & 1 & "%%'"

                'Job Only

            ElseIf frmadvancef.lblvarvalue.Text = 25 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%' and OJT_Check Like '%%" & 1 & "%%'"

                'OJT

            ElseIf frmadvancef.lblvarvalue.Text = 40 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%'and Certificate_Level Like '%%" & frmadvancef.cmbcertficatead.Text.Trim.ToString() & "%%' and OJT_Check Like '%%" & 1 & "%%'"

                'Certification + OJT

            ElseIf frmadvancef.lblvarvalue.Text = 60 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%'and Certificate_Level Like '%%" & frmadvancef.cmbcertficatead.Text.Trim.ToString() & "%%' and Job_Check Like '%%" & 1 & "%%'"

                'Certification + Job

            ElseIf frmadvancef.lblvarvalue.Text = 70 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%'and Job_Check Like '%%" & 1 & "%%' and OJT_Check Like '%%" & 1 & "%%'"

                'Job + OJT

            ElseIf frmadvancef.lblvarvalue.Text = 85 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%'and Certificate_Level Like '%%" & frmadvancef.cmbcertficatead.Text.Trim.ToString() & "%%' and Job_Check Like '%%" & 1 & "%%' and OJT_Check Like '%%" & 1 & "%%'"

                'Certification + OJT + Job

            ElseIf frmadvancef.lblvarvalue.Text = 100 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%' and Status Like '%% " & frmadvancef.chkdropoutad.Text.Trim.ToString() & "%%'"

                'Dropouts Only

            ElseIf frmadvancef.lblvarvalue.Text = 200 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%'"

                'All Options

            ElseIf frmadvancef.lblvarvalue.Text = 0 Then

                OleDa.SelectCommand.CommandText = "SELECT * FROM " & table_name & " WHERE Batch_Details Like '%%" & frmadvancef.cmbbatchnoad.Text.Trim.ToString() & "%%'"

                'All Options

            End If

Thank for Everyone

[former_member183750 · Accepted solution]

Hello Roshan

You have an easy way and a hard way. Of course, the easy way has it's limits.

The easy way

Create a blank report and add 6 blank formulas to it; @formula1, @formula2, ... @formulaN

At runtime populate the formulas with the correct fields from your DS. See KBA - 1214187 - Modifying report formula fields at runtime using the Crystal Reports .NET SDK for code details.

Also see: http://www.facebook.com/note.php?note_id=444515136872

The hard way

Use the InProc RAS Report Creation SDK. For more details see:

How to Use The RAS SDK .NET With In-Process RAS Server

https://forums.sdn.sap.com/thread.jspa?threadID=1028548&tstart=0

And don't forget to consult the Developer Help files:

SAP Crystal Reports .NET API Guide

SAP Crystal Reports .NET SDK Developer Guide

Report Application Server .NET SDK Developer Guide

Report Application Server .NET API Guide

Finally, search box is in the top right corner. Simple search strings such as 'crystal net formula' are best.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces