How to create crystal report dynamically

See more:

VB

ASP.NET

CrystalReports

Hi, I have a problem to create single crystal report to generate data of multiple SQL queries dynamically. I have googled lot but I could not find the right solution to solve my problem.

My requiement is to create the single crystal report that should load the contents of the SQL Query passed on that time dynamically not to create the report using of drag and drop wizard.

For Example, I have a text field to enter the SQL query and Button field to generate Crysrtal Report. if I hit the generate button after providing the sql query, the CR should be generate the SQL query result inside the crystal report.

This is to avoid to generate multiple Crystal report for multiple queries.

What I have tried:

This is the code I have tried so far, I 'm stuck to find out the right solution.

VB Code:
Private Sub loadlabe2()
Dim cryRpt As New ReportDocument
Dim cnn As SqlConnection
Dim connectionString As String
Dim sql As String

connectionString = "Data source=MYDATA\MYAPP21;initial catalog=XYZ_FrontOffice;user id=XYZ;password=test"
cnn = New SqlConnection(connectionString)
cnn.Open()
sql = procesSQL()
Dim dscmd As New SqlDataAdapter(sql, cnn)
Dim ds As New DataSet4
dscmd.Fill(ds, "DataTable1")
cryRpt.SetDataSource(ds.Tables(1))
CrystLabl.ReportSource = "CrystalReport4.rpt"
CrystLabl.RefreshReport()
End Sub
Public Function procesSQL() As String
Dim sql As String
Dim inSql As String
Dim firstPart As String
Dim lastPart As String
Dim selectStart As Integer
Dim fromStart As Integer
Dim fields As String()
Dim i As Integer
Dim mytext As CrystalDecisions.CrystalReports.Engine.TextObject
Dim discreteVal1 As New CrystalDecisions.Shared.ParameterDiscreteValue()

inSql = TextBox1.Text
inSql = inSql.ToUpper

selectStart = inSql.IndexOf("SELECT")
fromStart = inSql.IndexOf("FROM")
selectStart = selectStart + 6
firstPart = inSql.Substring(selectStart, (fromStart - selectStart))
lastPart = inSql.Substring(fromStart, inSql.Length - fromStart)

fields = firstPart.Split(",")
firstPart = ""
For i = 0 To fields.Length - 1
If i > 0 Then
firstPart = firstPart & " , " & fields(i).ToString() & " AS COLUMN" & i + 1
mytext = CType(CrystLabl.ReportSource.ReportObjects("Text" & i + 1), TextObject)
mytext.Text = fields(i).ToString()
Else
firstPart = firstPart & fields(i).ToString() & " AS COLUMN" & i + 1
mytext = CType(CrystLabl.ReportSource.ReportObjects("Text" & i + 1), TextObject)
mytext.Text = fields(i).ToString()
End If
Next
sql = "SELECT " & firstPart & " " & lastPart
Return sql
End Function