Link to home
Start Free TrialLog in
Avatar of Russ Cummings
Russ CummingsFlag for United States of America

asked on

How do I pass a string variable from a form to crystal report?

All,
  I'm using VS 2015 Enterprise, specifically vb.net along with crystal reports.
I have a form1.vb that opens mysql database, asks user for start date & end date & lastly creates xml schema of requested data.

Imports MySql.Data.MySqlClient
Imports System.Text.RegularExpressions

Public Class Form1
    Dim myData As New DataSet
    Dim conn As New MySqlConnection
    Dim cmd As New MySqlCommand
    Dim myAdapter As New MySqlDataAdapter

    Public MySQLConnectionString As String = "server= www.automatedanswers.com;user id=russemi2_test;password=Passw0rd;database=russemi2_test"
    Dim connStr As String = MySQLConnectionString
    Dim sql As String
    Dim id As String
    Dim datetime, datetime2 As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim pattern As String = "^(20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$"
        Dim regex As New Regex(pattern)
        datetime = ""
        datetime = InputBox("Start Date - Use Format 'yyyy-mm-dd'")
        Do While Not regex.IsMatch(datetime)
            datetime = ""
            datetime = InputBox("Start Date - Use Format 'yyyy-mm-dd'")
        Loop


        datetime2 = InputBox("End Date - Use Format 'yyyy-mm-dd'",, datetime)
        Do While Not regex.IsMatch(datetime2)
            datetime2 = ""
            datetime2 = InputBox("End Date - Use Format 'yyyy-mm-dd'")
        Loop

        datetime2 = datetime2 & " 23:59:59"
        datetime = datetime & " 00:00:01"

        DBConn()
    End Sub



    Sub DBConn()

        Dim conn As MySqlConnection
        conn = New MySqlConnection()
        conn.ConnectionString = MySQLConnectionString

        Try
            conn.Open()

            '            cmd.CommandText = "SELECT * FROM gateaccess where gadate BETWEEEN '" & datetime & "' AND '" & datetime2 & "' order by id,gadate DESC;"
            cmd.CommandText = "SELECT * FROM gateaccess where gadate > '" & datetime & "' and gadate < '" & datetime2 & "' order by gadate,id ASC;"

            cmd.CommandText = "select gate.fullname, gateaccess.id, gateaccess.gadate from gateaccess join gate on gate.id = gateaccess.id where gadate > '" & datetime & "' and gadate < '" & datetime2 & "' order by gateaccess.gadate, gateaccess.id asc;"
            cmd.Connection = conn

            myAdapter.SelectCommand = cmd
            myAdapter.Fill(myData)

            myData.WriteXml("C:\gateaccessdataset.xml", XmlWriteMode.WriteSchema)



            'MessageBox.Show("Connection To Database has been opened.", "Connection Established", MessageBoxButtons.OK, MessageBoxIcon.Information)
            conn.Close()
        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect To database: " & myerror.Message, "Error Connection", MessageBoxButtons.RetryCancel, MessageBoxIcon.Warning)
        Finally
            conn.Dispose()
        End Try

    End Sub
End Class



Then the crystal report application is run using the appropriate data.
All works well.
What I'd like to do is display on the header of the report    datetime - datetime2    (i.e.: Start Date - End Date).
How do I do that?  
When I look in the Solutions Explorer under Form1.vb I can see the 2 variables as string, but I can't simply drop them into the report.
Thank you,

Russ
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Avatar of Russ Cummings

ASKER

Thank you, I have entered "StartDate" as a new parameter. Please guide me how I put that on the header section of the report.
I don't wish to use it in a formula, as the blog demonstrates, just display it.

Russ
By following the instructions in "Passing a value to a report object", you can set directly the value of a TextObject in your report. Isn't it what you want?
Please review my code from original post - I don't have code Passing anything to crystal reports engine. All I'm doing is creating the xml file & using it from within the crystal application.

Does this mean I must rewrite my program to mimic what is in the blog, in order to simply pass the strings?

Russ
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thought of that, but I was hoping for a cleaner solution than writing parameters to a temporary table.
Thank you,

Russ