Active TopicsMemberlistSearchHelpRegisterLogin

Data Connectivity
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Data Connectivity
Message Icon Topic: Crystal and Stored Procedures Parameters Post Reply Post New Topic
Author Message
nuno_rogerio
Newbie
Newbie


Joined: 11 Sep 2007
Location: Portugal
Online Status: Offline
Posts: 3
Quote nuno_rogerio Replybullet Topic: Crystal and Stored Procedures Parameters
    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
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
IP IP Logged
nuno_rogerio
Newbie
Newbie


Joined: 11 Sep 2007
Location: Portugal
Online Status: Offline
Posts: 3
Quote nuno_rogerio Replybullet 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
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
 
IP IP Logged
nuno_rogerio
Newbie
Newbie


Joined: 11 Sep 2007
Location: Portugal
Online Status: Offline
Posts: 3
Quote nuno_rogerio Replybullet Posted: 12 Sep 2007 at 9:19am
hi john..
thanks for the support...
keep in touch :)
always learning
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
IP IP Logged
TomN
Newbie
Newbie


Joined: 22 Jan 2008
Location: Australia
Online Status: Offline
Posts: 3
Quote TomN Replybullet Posted: 22 Jan 2008 at 8:49pm
Originally posted by nuno_rogerio

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
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()
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
IP IP Logged
TomN
Newbie
Newbie


Joined: 22 Jan 2008
Location: Australia
Online Status: Offline
Posts: 3
Quote TomN Replybullet 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 
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.048 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.