Imports System.Data.SqlClient

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

Imports System.Data

Public Class Form1

    Dim objRpt As New CrystalReport1

    Private Sub Button1_Click(ByVal sender As System.Object, 

	ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As SqlConnection

        Dim connectionString As String

        Dim sql As String



        connectionString = "data source=SERVERNAME; _

		initial catalog=crystaldb;user id=sa;password=PASSWORD;"

        cnn = New SqlConnection(connectionString)

        cnn.Open()

        sql = procesSQL()

        Dim dscmd As New SqlDataAdapter(sql, cnn)

        Dim ds As New DataSet1

        dscmd.Fill(ds, "Product")

        objRpt.SetDataSource(ds.Tables(1))

        CrystalReportViewer1.ReportSource = objRpt

        CrystalReportViewer1.Refresh()

    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 TextObject



        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(objRpt.ReportDefinition.ReportObjects("Text"  _

				& i + 1), TextObject)

                MyText.Text = fields(i).ToString()

            Else

                firstPart = firstPart & fields(i).ToString() & _

				"  AS COLUMN" & i + 1

                MyText = CType(objRpt.ReportDefinition.ReportObjects("Text" & _

				 i + 1), TextObject)

                MyText.Text = fields(i).ToString()

            End If

        Next

        sql = "SELECT " & firstPart & " " & lastPart

        Return sql

    End Function



End Class