I have added form1 and on it 2 text boxes.
text box1 to accept fromdate and textbox2 to accept todate.
After this,I added Crystal report from Add new Item.
Then in Crystal report I added table and its columns.
After that I wrote code behind form1.
The code is not correct but I have tried it.
Its as follows:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class REPORT
    Private dataadapter As SqlDataAdapter
    Dim str_com As String = "Data Source=COMPAQ\SQLEXPRESS;Initial Catalog=LibTry;Integrated Security=True"
    Dim objcon As SqlConnection
    Dim objcmd As SqlCommand
    Dim frmDate As DateTime
    Dim toDate As DateTime

    Private Sub call1(ByVal frmDate As Date, ByVal toDate As Date)
        Dim objcon As New SqlConnection("Data Source=COMPAQ\SQLEXPRESS;Initial Catalog=LibTry;Integrated 

Security=True")
        objcon.Open()
        Dim str_con As String = "select * from IssueMBA where dtaccess between '" + frmDate + "' and ' " + toDate + "'"
        objcmd = New SqlCommand(str_con, objcon)
        Dim ds As New DataSet
        Dim dataadapter As New SqlDataAdapter(objcmd)
        dataadapter.Fill(ds)

        objcon.Close()
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        frmDate = Date.Parse(TextBox1.Text)
        toDate = Date.Parse(TextBox2.Text)
        call1(frmDate, toDate)
        CrystalReport1()
        Me.Close()

    End Sub


    Private Sub REPORT_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        objcon = New SqlConnection(str_com)
    End Sub
End Class

You might have come across this report before also but I am trying to do it in this way and not able to do.

u can use a datetimepicker to show the from date and to date....

I will give my example to u...

I have one form in which I have two datepickers one for from date and the other one to date...one button to close the form and the second one to show the report....

u also need a crystal report viewer to display the report which will be under the reporting tab....

now check my code behind form....

NOTE: JUST for reference

Imports System.Data.SqlClient
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmInpatientmasterreport

    Private Sub btnShow_Click(sender As System.Object, e As System.EventArgs) Handles btnShow.Click
        'connection string goes here
        If dtpFromDate.Value > dtpToDate.Value Then
            MsgBox("From Date cannot be greater than To Date")
        Else
            Try
                Dim myCommand As SqlCommand
                myCommand = New SqlCommand("select * from HMS.dbo.inpatientdetails where createddate between '" + dtpFromDate.Value.ToString("yyyy-MM-dd") + "' and  '" + dtpToDate.Value.ToString("yyyy-MM-dd") + "'", Connection)
                Dim reader As SqlDataReader = myCommand.ExecuteReader
                If reader.Read = True Then
                    Dim crtableLogoninfos As New TableLogOnInfos
                    Dim crtableLogoninfo As New TableLogOnInfo
                    Dim crConnectionInfo As New ConnectionInfo
                    Dim CrTables As Tables
                    Dim CrTable As Table

                    Dim cryRpt As New ReportDocument
                    cryRpt.Load("crystal report complete path")

                    With crConnectionInfo
                        .ServerName = "servername"
                        .DatabaseName = "Database"
                        .UserID = ""
                        .Password = ""
                        .IntegratedSecurity = True
                    End With
                    CrTables = cryRpt.Database.Tables
                    For Each CrTable In CrTables
                        crtableLogoninfo = CrTable.LogOnInfo
                        crtableLogoninfo.ConnectionInfo = crConnectionInfo
                        CrTable.ApplyLogOnInfo(crtableLogoninfo)
                    Next
                    Dim crParameterFieldDefinitions As ParameterFieldDefinitions
                    Dim crParameterFieldDefinition As ParameterFieldDefinition
                    Dim crParameterValues As New ParameterValues
                    Dim crParameterDiscreteValue As New ParameterDiscreteValue

                    crParameterDiscreteValue.Value = **dtpFromDate.Value.ToString("yyyy-MM-dd")**
                    crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields
                    crParameterFieldDefinition = crParameterFieldDefinitions.Item("fromdate")
                    crParameterValues = crParameterFieldDefinition.CurrentValues

                    crParameterValues.Clear()
                    crParameterValues.Add(crParameterDiscreteValue)
                    crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

                    crParameterDiscreteValue.Value = **dtpToDate.Value.ToString("yyyy-MM-dd")**
                    crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields
                    crParameterFieldDefinition = crParameterFieldDefinitions.Item("todate")
                    crParameterValues = crParameterFieldDefinition.CurrentValues

                    crParameterValues.Add(crParameterDiscreteValue)
                    crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

                    crvInpatientMaster.ReportSource = cryRpt
                    crvInpatientMaster.Refresh()
                Else
                    MsgBox("No records exists")
                End If
                reader.Close()
            Catch ex As Exception
                MsgBox("Error in select query: " + ex.Message)
            End Try
        End If

        'close connection
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
End Class

Edited 4 Years Ago by poojavb

PoojaVB as per your given code I got few doubts.
1]Should I add crystal report on the same form wherein datetimepickers are added?
2]crtableLogoninfos <- I din't get this line. Why did u create new table as logoninfo?
In my case it is in this way :

 myCommand = New SqlCommand("select * from IssueMBA where ida between '" + DateTimePicker1.Value.ToString("MM-dd-yyyy") + "' and  '" + DateTimePicker2.Value.ToString("MM-dd-yyyy") + "'", con)

So,I guess I don't need to create Logoninfos table right? I am confused kindly explain it .
3]I am getting error at this line :
`

   crParameterDiscreteValue.Value = **DateTimePicker1.Value.ToString("MM-dd-yyyy")**

The error is at the symbols "** "
**And the error is Expression expected.
4] Getting the **** same error as above mentioned in "3" for the below sentence too.

crParameterDiscreteValue.Value = **DateTimePicker2.Value.ToString("MM-dd-yyyy")**

5]Getting error at the below lines too :

crvIsuueMBA.ReportSource = cryRpt
crvIssueMBA.Refresh()

The error is crvIsuueMBA is not declared.

`

Find ur ansers below

  1. Yes u can add ur cystal report in the same form where u have added the crystal report viewer and the datetimepickers...
  2. crtableLogoninfos - this is because when u move ur database to anyother client machine then the server name may change according to the client and also the database...to get the correct database info I have added the above code

Yes according to ur form it may be the same as below but check that the date format is the same as u have ment0ined in the query.....it changes according to the database

myCommand = New SqlCommand("select * from IssueMBA where ida between '" + DateTimePicker1.Value.ToString("MM-dd-yyyy") + "' and '" + DateTimePicker2.Value.ToString("MM-dd-yyyy") + "'", con)

  1. Check if the datetimepicker name is the same as u are giving in ur query

crParameterDiscreteValue.Value = DateTimePicker1.Value.ToString("MM-dd-yyyy")

  1. Same as 3.

Note:
u have the below line also in ur code....
crParameterFieldDefinition = crParameterFieldDefinitions.Item("todate")

the todate and the fromdate are the paramaters that has to be created while creating ur crystal reports....

if u have any doubt feel free to post...

Please check my attachment...

Attachments report.png 23.45 KB

poojavb

Thanku for the patience to reply my all doubts .
I am sorry In previuos post I said :
1]Should I add crystal report on the same form wherein datetimepickers are added?
Actually I meant to say Crystal report Viewer.
Ok.So, thought to tell you what I did is:
I added 2 datetimepickers ,2 buttons, 1 crystal report viewer on form1.
Then I used your given code.
Now I am still getting errors at this below code:

 crvIsuueMBA.ReportSource = cryRpt
 crvIssueMBA.Refresh()

The error is crvIssueMBA is not declared.
One more thing U have mentioned to add crystal report.
So, am I supposed to add new item as crystal report and then connect the IssueMBA on crystal report .
And give the name to that Crystal Report form as crvIssueMBA?

no crvIssueMBA should be ur crystal report viewer name....see my example....cryRpt is my crystalreportviewer name.....

have u added this below code??

Dim cryRpt As New ReportDocument
cryRpt.Load("crystal report complete path")

cryRpt the place where u have ur actual crystal report....complete path

Edited 4 Years Ago by poojavb

poojavb
Ok ok thanku Ok I added the full path name and also the crystal viewer name.
And in previous comments U had written that :

"the todate and the fromdate are the paramaters that has to be created while creating ur crystal reports"

So I should create a crystal report of IssueMBA and pass the parameter to it?
But what the parameters should be?
As I have given the names as datetimepicker1 and datetimepicker2 in the code .
So should add this 2 names only for parameter?
I am sorry I am new to this..trying to learn.

U must have created a crystal report using the wizard right???

That is ur crystal report....in that now u need to compare the value with crsytal report

means

the datetimepicker1 and two are ur part of vb.net code so there shud be some parameters to compare with the crystal report...

1. Open ur crystalreport1.rpt file 'dont know ur file name...
2. On left had side u will have a Field Explorer window...Right click on Parameter Fields-> New -> create two variables as fromdate and todate with datatype String.....

after this is done u need to assign the parameter values to that of ur table column ida since that contains ur date values....

1. on center of ur crystal report right click -> Report -> Selection Formula - >Record
2. A Record Selection formula Editor window will be opened ...write the below thing according to ur requirements
3. {tablename.ida} in {?fromdate} to {?todate}

this will compare in ur crystal report....

Comments
hey when we close the record selection formula editor the error is occruing......................i applied ur replies also bt was of no use error is popping out..........pls reply me as sun as pos

poojavb

I did like this :
IssueMBA.ida in frmdate to todate
But when I click on save box of Record Selection Formula Editor ,the message box pops up "There is an error in this formula,do you want to save it anyway"

check the datatype values in database and the parameter datatype that u have created.. else try putting quotes for ur parameters in the Record Selection Formula Editor

can u plz explain the connection string
coz error is occuring on yhe word connection
and
hv nt understud the following code

> crvInpatientMaster.ReportSource = cryRpt
>                     crvInpatientMaster.Refresh()

what is crvInpatientMaster???

poojavb
The datatype of ida in IssueMBA is Datetime.
So,am I supposed to make the parameters frmdate and todate also to Datetime?

hey when we close the record selection formula editor the error is occruing......................i applied ur replies also bt was of no use error is popping out..........pls reply me as sun as pos

hey when we close the record selection formula editor the error is occruing......................i applied ur replies also bt was of no use error is popping out..........pls reply me as sun as pos

I had a datatype date in my database that is Microsoft SQL server....and so I had used a string datatype in crystal report....it worked for me....

and even vice versa...when varchar in database then date datatype in crystal report.....

Might be u are missing something.....

poojavb
What I did is 1st selected datetime then tried it din't work out.
Then selected string ,it din't work out.
Again selected datetime and it worked out :)
Thanku dear :)

Ur welcome.... dont forget to vote me up :) and mark the thread as solved if it helped u....

ya same here datetime worked for me also
bt runtime error is occuring
Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

Hey Poojavb,
It dint give any error but when I click on button its not showing the report.

Poojavb:
Ok The problem is :
1]Its not showing any report if I click on Button. The button is where all the above codings are done.
2]So,I connected Crystal Report to Crystal Report Viewer i.e. the connection is done through the small arrow we can see on the crystal report viewer.
3] I run the form and both the form and the Crystal Report pops up.
Crystal Report directly asks to choose the frmdate and todate and after choosing it does'n show any result,it closes that box.
4] On the form,Where the 2 datetimepickers and button and crystal report viewer are shown:
Here after clicking on button there is no result.

hey plz give me the solution i m tryng to resolve it from last 6 hrs.....plz let me knw why this error is occuring :(

hey plz give me the solution i m tryng to resolve it from last 6 hrs.....plz let me knw why this error is occuring :(

Let me tell u in detail....

  1. Create a crystalreport for ur project...... eg. Crystalreport.rpt
  2. After the report is created create parameters as I told u....fromdate and todate and then avail the condition in the Record Selection Editor Formula as ur database_columnname in {fromdate} to todate
  3. Go to ur Main Report Preview Window and see if u are getting ur output....the report will have two textboxes as from date and to date then enter the value and see if u get ur output....

this deals with ur crystal report...

Now ur win form.....

Add two datetimepickers for fromdate and todate.....one button to show the report and one crystal report viewer....

in ur button click event write the code which I had given u earlier and give the correct database and the correct crystal report path....

Run ur program and then see what output u get.....

to need to connect ur crystal report to crystal report viewer as u said in point 2....

Check and let me know if still u face any error....

poojavb
Yes I had done evrything same as u described above ...After doing it I posted the problems..Hey dear read my previous post..I have given what are those problems...

Now it displays only 5/16-201 on main report and nothing.
I dont know what is 5/16-201..

poojavb

After running the form ,the Crsytal Report asks to enter frmdate and todate and after entering it, the crystal report gets close and the form will be available ,on the form after choosing datetimepicker from date and todate and clicking on Button, then on CrystalReportViewer 5/16-201 gets displayed.

Poojavb
Not able to open the file and its showing the msg-> Unsafe file can damagae the system.

The code on my form and button is :

Imports System.Data.SqlClient
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Public Class dani




    Dim con As New SqlConnection("Data Source=COMPAQ\SQLEXPRESS;Initial Catalog=LibTry;Integrated Security=True")







    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        con.Open()

        If DateTimePicker1.Value > DateTimePicker2.Value Then
            MsgBox("From Date cannot be greater than To Date")
        Else
            Try
                Dim myCommand As SqlCommand
                myCommand = New SqlCommand("select * from IssueMBA where ida between '" + DateTimePicker1.Value.ToString("MM-dd-yyyy") + "' and  '" + DateTimePicker2.Value.ToString("MM-dd-yyyy") + "'", con)
                Dim reader As SqlDataReader = myCommand.ExecuteReader
                If reader.Read = True Then
                    Dim crtableLogoninfos As New TableLogOnInfos
                    Dim crtableLogoninfo As New TableLogOnInfo
                    Dim crConnectionInfo As New ConnectionInfo
                    Dim CrTables As Tables
                    Dim CrTable As Table
                    Dim cryRpt As New ReportDocument
                    cryRpt.Load("C:\Users\Admin\Documents\Visual Studio 2008\Projects\Libsys\Libsys\crvIssueMBA.rpt")

                    With crConnectionInfo
                        .ServerName = "COMPAQ\SQLEXPRESS"
                        .DatabaseName = "LibTry"

                        .IntegratedSecurity = True
                    End With
                    CrTables = cryRpt.Database.Tables
                    For Each CrTable In CrTables
                        crtableLogoninfo = CrTable.LogOnInfo
                        crtableLogoninfo.ConnectionInfo = crConnectionInfo
                        CrTable.ApplyLogOnInfo(crtableLogoninfo)
                    Next
                    Dim crParameterFieldDefinitions As ParameterFieldDefinitions
                    Dim crParameterFieldDefinition As ParameterFieldDefinition
                    Dim crParameterValues As New ParameterValues
                    Dim crParameterDiscreteValue As New ParameterDiscreteValue
                    crParameterDiscreteValue.Value = DateTimePicker1.Value.ToString("MM-dd-yyyy")
                    crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields
                    crParameterFieldDefinition = crParameterFieldDefinitions.Item("fromdate")
                    crParameterValues = crParameterFieldDefinition.CurrentValues
                    crParameterValues.Clear()
                    crParameterValues.Add(crParameterDiscreteValue)
                    crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)
                    crParameterDiscreteValue.Value = DateTimePicker2.Value.ToString("MM-dd-yyyy")
                    crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields
                    crParameterFieldDefinition = crParameterFieldDefinitions.Item("todate")
                    crParameterValues = crParameterFieldDefinition.CurrentValues
                    crParameterValues.Add(crParameterDiscreteValue)
                    crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)
                    CrystalReportViewer1.ReportSource = cryRpt
                    CrystalReportViewer1.Refresh()
                Else
                    MsgBox("No records exists")
                End If
                reader.Close()
            Catch ex As Exception
                MsgBox("Error in select query: " + ex.Message)
            End Try
        End If
        con.Close()

    End Sub


End Class
This article has been dead for over six months. Start a new discussion instead.