1,105,310 Community Members

C#.net report viewer get input parameter

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

hi,
i have a question in C#.net report viewer. i want to create a report which will take a user input and generate a report. what i want is get the month from the user input and get it as a parameter for the sql query go get the result.
say for example the user selects the date 1/1/2011 so it should get the month as january and get all the people born in january and display. i have a feild in the database which tracks the employees born date so from that i can generate the query, but how to i get the user input to the query ?

can some one give me a help?

thanks

Member Avatar
MartinPlatt
Light Poster
29 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 5 [?]
Skill Endorsements: 0 [?]
 
0
 

Is this an SSRS report?

If it was you would add a parameter in pretty much the same way as you would for a stored procedure call, using a Parameters.Add( ... )

If it isn't, I'd still expect it to work in the same way, you need to pick up the command part of your database call and add your parameter to that.

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Is this an SSRS report?

If it was you would add a parameter in pretty much the same way as you would for a stored procedure call, using a Parameters.Add( ... )

If it isn't, I'd still expect it to work in the same way, you need to pick up the command part of your database call and add your parameter to that.

i know that i have to add a prameter to the sql query, but how to i send the parameter to the sql query, when the user puts in a value

Member Avatar
nick.crane
Veteran Poster
1,195 posts since Feb 2010
Reputation Points: 342 [?]
Q&As Helped to Solve: 190 [?]
Skill Endorsements: 5 [?]
 
0
 

How are you thinking to get the value from the user?

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

How are you thinking to get the value from the user?

in a combo box i have the 12 months, saying jan feb and so on and them i have to convert it to the relevent number say for example Nov i have to get number 11 and then send it to the sql query

that is what i am trying to do

Member Avatar
nick.crane
Veteran Poster
1,195 posts since Feb 2010
Reputation Points: 342 [?]
Q&As Helped to Solve: 190 [?]
Skill Endorsements: 5 [?]
 
0
 

OK. So you have a number that represents the month.
Then your query should use DATEPART to get the month number of the birth date stored in your DB.
Something like this SELECT [Name], [Date] FROM [Birthdays] WHERE DATEPART(month, [Date]) = @Month .
[Edit] Check out DATEPART here.

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

OK. So you have a number that represents the month.
Then your query should use DATEPART to get the month number of the birth date stored in your DB.
Something like this SELECT [Name], [Date] FROM [Birthdays] WHERE DATEPART(month, [Date]) = @Month .
[Edit] Check out DATEPART here.

hey

i wrote the SQl query,
but how can i get the user value as a input parameter to the query
the query is as below

ALTER PROCEDURE dbo.MonthlyBirthday @BirthMonth varchar(50)
	
AS
	Select FirstName,LastName 
	From EmpDetails 
	Where month( [DOB]) = @BirthMonth 
	Order by FirstName

the form code is as below

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace HR_Application.Presentation_Layer.Reports
{
    public partial class frmEmpBirthday : Form
    {
        public frmEmpBirthday()
        {
            InitializeComponent();
        }

        private void EmpBirthday_Load(object sender, EventArgs e)
        {

            this.reportViewer1.RefreshReport();
        }

        private void btnMonth_Click(object sender, EventArgs e)
        {

        }
    }
}

how do i get the combo box value to the sql query

appreciate a reply

Member Avatar
nick.crane
Veteran Poster
1,195 posts since Feb 2010
Reputation Points: 342 [?]
Q&As Helped to Solve: 190 [?]
Skill Endorsements: 5 [?]
 
0
 

Your query uses the string name for the month. I generally try to avoid using strings for dates as there are always regional differences (i.e. language, ordering of each part, etc.).

However, your query is in a procedure so you need to set the SQLCommand object to call the procedure with your parameters.
Something like this.

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.MonthlyBirthday";
cmd.Parameters.AddWithValue("@BirthMonth", "Janvier"); // assuming that your DB is in French!

Also, given that your ComboBox shows the text value for the month then you just need to get the SelectedValue instead of the SelectedIndex.

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Your query uses the string name for the month. I generally try to avoid using strings for dates as there are always regional differences (i.e. language, ordering of each part, etc.).

However, your query is in a procedure so you need to set the SQLCommand object to call the procedure with your parameters.
Something like this.

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.MonthlyBirthday";
cmd.Parameters.AddWithValue("@BirthMonth", "Janvier"); // assuming that your DB is in French!

Also, given that your ComboBox shows the text value for the month then you just need to get the SelectedValue instead of the SelectedIndex.

where have i used string for the date?
and what is cmd? where do i have to write the code

Member Avatar
nick.crane
Veteran Poster
1,195 posts since Feb 2010
Reputation Points: 342 [?]
Q&As Helped to Solve: 190 [?]
Skill Endorsements: 5 [?]
 
0
 

"where have i used string for the date?"
@BirthMonth varchar(50)

"what is cmd?"
cmd is an SqlCommand object which should be obvious from the preceding text.

"where do i have to write the code"
Where ever it is that you need to call the Sql procedure.

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

"where have i used string for the date?"
@BirthMonth varchar(50)

"what is cmd?"
cmd is an SqlCommand object which should be obvious from the preceding text.

"where do i have to write the code"
Where ever it is that you need to call the Sql procedure.

@BirthMonth is the user input value as 1, 2 3, ... 12

Member Avatar
nick.crane
Veteran Poster
1,195 posts since Feb 2010
Reputation Points: 342 [?]
Q&As Helped to Solve: 190 [?]
Skill Endorsements: 5 [?]
 
0
 

@BirthMonth is the user input value as 1, 2 3, ... 12

Yes, but the procedure you posted expects @BirthMonth to be of type varchar(50)!
Last time I checked that is a text type.

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Yes, but the procedure you posted expects @BirthMonth to be of type varchar(50)!
Last time I checked that is a text type.

I didn't get it, is it correct to be varchar(50)
please correct me if i am writing it wrong

Member Avatar
nick.crane
Veteran Poster
1,195 posts since Feb 2010
Reputation Points: 342 [?]
Q&As Helped to Solve: 190 [?]
Skill Endorsements: 5 [?]
 
0
 

Do you not think that int would be more appropriate?
Then the comparison month( [DOB]) = @BirthMonth would not need to do any type conversions.

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Do you not think that int would be more appropriate?
Then the comparison month( [DOB]) = @BirthMonth would not need to do any type conversions.

yes
int will be the correct data type
and also
how do i write the query

SqlCommand cmd = new SqlCommand("dbo.MonthlyBirthday", conn);
cmd.CommandType = CommandType.StoredProcedure;

how do i write the rest of the code

Member Avatar
nick.crane
Veteran Poster
1,195 posts since Feb 2010
Reputation Points: 342 [?]
Q&As Helped to Solve: 190 [?]
Skill Endorsements: 5 [?]
 
0
 

how do i write the rest of the code

Add a parameter like I did in my previous post (only use the int value from your ComboBox).
Then execute the query as you would any other Table query.
[Edit]
Don't forget to modify the SQL procedure to ... @BirthMonth int

Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Add a parameter like I did in my previous post (only use the int value from your ComboBox).
Then execute the query as you would any other Table query.
[Edit]
Don't forget to modify the SQL procedure to ... @BirthMonth int

how do i write the rest of the query for this

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.MonthlyBirthday";
cmd.Parameters.AddWithValue("@BirthMonth", "Janvier"); // assuming that your DB is in French!

Member Avatar
nick.crane
Veteran Poster
1,195 posts since Feb 2010
Reputation Points: 342 [?]
Q&As Helped to Solve: 190 [?]
Skill Endorsements: 5 [?]
 
0
 
Member Avatar
judithSampathwa
Posting Pro in Training
453 posts since May 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

hi could some one help me out with this, coudn't figure it out

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: