Hi,
Your stored procedure seems to be good but need certain chenges as per your code behide.
Let's take one by one:
1. Bind() function :-
Here you have to change this code
cn.Open();
to -
cn.Open();
SqlCommand cmd = new SqlCommand("gvempdetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action","SELECT");
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cn.Close();
2. ddlAge_SelectedIndexChanged function
As it seems here, you want to filter the employee with Age between your range i.e. from and to
As you want to use these paramters in Stored Procedure(SP) , you have to take them as input parametrs in SP
Like :
@Action VARCHAR (10),
@EMPLOYEEID INT ,
@EMPLOYEENAME VARCHAR(100)= NULL ,
@DEPARTMENT VARCHAR(100)= NULL ,
@AGE VARCHAR(30)= NULL,
@SALARY INT = NULL,
@FrOM INT = NULL,
@TO INT = NULL
and need to add one more Action for this as
IF @Action = 'SELECTAGE'
BEGIN
SELECT EmpId,EmpName,Dep,Age,Sal where age >= @FROM and age <= @TO
FROM gvdetails17
END
Now the codebehid code,
cn.open();
SqlCommand cmd = new SqlCommand("gvempdetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ACTION", "SELECTAGE");
cmd.Parameters.AddWithValue("@FROM", from);
cmd.Parameters.AddWithValue("@TO", to);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
I hope you will understand by above example how to use Stored Procedure from code
just for information,
While creating SqlCommand object we need to pass the commandText and connection.
So in case of SP you have to pass only the name of SP as commandText in our case "gvempdetails"
and rest of the thing you have to pass as parameter like @ACTION,@FROM in our case.
in your stored procedrue you may have observed that some parameters are initialize with NULL like @SALARY INT = NULL
Here the NULL is use to make the parameter optional otherwise you have to pass this parameter on all call
On last suggestion - @Action VARCHAR (10) here, in action type of parametrs you should take atleast 30 char coz there ia a chance
you to take Action name as long string like 'SELECTAGEBYRANGE'
Hope it will help you out!
Thanks,
Ejaz Waquif