C#.net report viewer get input parameter

[judithSampathwa]

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

[MartinPlatt]

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.

[judithSampathwa]

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

[nick.crane]

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

[judithSampathwa]

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

[nick.crane]

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.

[judithSampathwa]

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

[nick.crane]

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.

[judithSampathwa]

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

[nick.crane]

"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.

[judithSampathwa]

"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

[nick.crane]

@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.

[judithSampathwa]

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

[nick.crane]

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

[judithSampathwa]

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

[nick.crane]

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

[judithSampathwa]

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!

[nick.crane]

Try this.

[judithSampathwa]

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