Russ Cummings
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.RegularExpress ions
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;d atabase=ru ssemi2_tes t"
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:\gateac cessdatase t.xml", XmlWriteMode.WriteSchema)
'MessageBox.Show("Connecti on 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.RetryCan cel, 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
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.RegularExpress
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=
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:\gateac
'MessageBox.Show("Connecti
conn.Close()
Catch myerror As MySqlException
MessageBox.Show("Cannot connect To database: " & myerror.Message, "Error Connection", MessageBoxButtons.RetryCan
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
check "Passing a value to a report object" from http://emoreau.com/Entries/Articles/2016/10/Crystal-Reports-for-Net-revisited-10-years-later.aspx
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
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?
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thought of that, but I was hoping for a cleaner solution than writing parameters to a temporary table.
Thank you,
Russ
Thank you,
Russ