Data Connectivity: Crystal and Stored Procedures Parameters

Print Page | Close Window

Crystal and Stored Procedures Parameters

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1299
Printed Date: 18 May 2024 at 12:06pm

Topic: Crystal and Stored Procedures Parameters

Posted By: nuno_rogerio
Subject: Crystal and Stored Procedures Parameters
Date Posted: 11 Sep 2007 at 4:22am

hello crystal experts :)

im trying to work wth this tool for 3 days now..when im in need, i do check the forum for some help..

so..

im using SqlServer2005.. stored procedures wth parameters..

the report that im creating in VisualStudio/CrystalReport.. request some objects like dropdownlists and text.boxs ..etc..

how can i connect this objects wth the stored procedures parameters??

exemple:

select de name of a room..

select a date..

push the ok button

and the report gives me the resolt of the stored procedure "schedule_room" wth parameters "@room","@date"

i hope sb could help me on this..

oh by the way.. im using VB.net... not c#..lol

-------------
always learning

Replies:

Posted By: jkwrpc
Date Posted: 11 Sep 2007 at 10:37am

You might consider designing your report against a dataset and using the stored proc as the select command for the data adapter used to the fill the deataset.

If you do that, you can use .Net to create the parameters based on the combo box, drop down lists, etc. making them the basis for the parameter values. The stored procedure will get the parameters but they will come from the .Net objects without having to be passed through the Crystal Report objects. Then the data returned from the VB.Net parameter objects will fill the dataset that will be the basis for the report.

If you need to use the report to gather the parameter values you still should be able to retrieve those values and pass them to the VB.Net parameter objects. Then the rest of the process will be the same.

All in all should be a pretty straight forward process.

Hope this helps.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: nuno_rogerio
Date Posted: 12 Sep 2007 at 4:10am

thanks John..

im gonna try what u wrote down:)

i was looking for some info about this tool.. and couldnt find alot.. just some tutorials..wth basic stuff.. do u know any book or video.. something that could help me.. cause in this business we need exemples.. looool

Regards

Nuno

-------------
always learning

Posted By: jkwrpc
Date Posted: 12 Sep 2007 at 7:06am

I wont have a chance until later today but I will look back through some of my projects and see I can send you something as a sample to get you started.

I would need you to PM with your email address (or contact me through my website) so we could better exchange the code samples. Since I also code in VB.Net you should be able to easily understand my process.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: nuno_rogerio
Date Posted: 12 Sep 2007 at 9:19am

hi john..

thanks for the support...

my email.. mailto:nuno_rogerio@portugalmail.pt - nuno_rogerio@portugalmail.pt

keep in touch :)

-------------
always learning

Posted By: jkwrpc
Date Posted: 12 Sep 2007 at 4:14pm

I sent a response to your email address. If what I sent gives you trouble let me know.

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: TomN
Date Posted: 22 Jan 2008 at 8:49pm

Originally posted by nuno_rogerio

thanks John..

im gonna try what u wrote down:)

Regards

Nuno

Hi John,

I set setDataSource for a report but the reportviewer shows empty report. I tested DS elsewhere and found that it does have records. I could not work out where I went wrong. For some reports works well when I setDataSource to new dataset.

Any hint is great appreciated!

Tom N

Dim reportDate As DateTime = "7/11/2007"

Dim da As SqlDataAdapter = New SqlDataAdapter

Dim ds As New DataSet

Dim sqlcmd As New SqlCommand

Dim sqlcon As New SqlConnection(My.Settings.SlamConnectionString)

sqlcon.Open()

Dim para As SqlParameter = New SqlParameter

sqlcmd.CommandType = CommandType.StoredProcedure

sqlcmd.CommandText = "proc_Last3MonthsIncdents"

para.ParameterName = "@reportMonth"

para.SqlDbType = SqlDbType.DateTime

para.Direction = ParameterDirection.Input

para.Value = reportDate ' date

sqlcmd.Parameters.Add(para)

sqlcmd.Connection = sqlcon

da.SelectCommand = sqlcmd

da.Fill(ds)

Me.crLast3Months2.SetDataSource(ds)

sqlcon.Close()

Posted By: jkwrpc
Date Posted: 23 Jan 2008 at 7:05am

SQL Server doesnt like a simple date for a search parameter. So I would suggest you use the BETWEEN logic in your stored proc.

If you are looking for 7/1/2007 use BETWEEN 6/30/2007 and 7/2/2007. The reason for this is the in the SQL Server datetime data type there is in addition to the data a time stamp part. Usually it defaults to something like 00:00:00, but it can be an exact time depending on the way the date is entered. A simple date does not match the complete datetime in the database so no records are returned. Therefore the dataset is empty.

If this is not the problem let us know.

Hope it helps.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: TomN
Date Posted: 23 Jan 2008 at 3:20pm

Hi John,

Thanks for your prompt reply. I agree with you about using BETWEEN instead of single datetime parameter in SQL server. ( In my store procedure the datetime para is used to work out first day and last date for SELECT statement)

I have problem that Crystalreportviewer does show any records regardless Select statements or store procedures. After research non-stop I found out that on the form hosts CrystalreportViewer control needed to have this statement:

Imports CrystalDecisions.CrystalReports.Engine

I plan to use 1 windows form to run different reports depending on users pick from a list/combo box. Could I have your sample projects you have sent to help starters before?

Best regards

Tom N

Posted By: jkwrpc
Date Posted: 24 Jan 2008 at 9:29am

I will be happy to share. Just send me a Private Message with your email address so I can send the code.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Print Page | Close Window