how can i read procedure from sqlsserver by Select statement in Asp.net

Question

0.00/5 (No votes)

See more:

, +

Hi.
I intend to read number of rows in DB and list them inside DropDownList, so for this issue i have written the following class:

C#

public static DataSet ExecuteDataSet(string CMDText, CommandType CMDType)
       {
           SqlConnection conn = new SqlConnection(ConnectionString);
           SqlCommand cmd = conn.CreateCommand();
           cmd.CommandText = CMDText;
           cmd.CommandType = CMDType;

           DataSet ds = new DataSet();
           SqlDataAdapter sad = new SqlDataAdapter(cmd);

           conn.Open();
           sad.Fill(ds);
           conn.Close();
           conn.Dispose();
           return ds;

       }

and there is a stored procedure in SqlServer 2008r2 as below to read all table's records:

SQL

Create PROCEDURE [dbo].[Select_City_SP](

@CityId int,
@CityName nvarchar(50)
)
as
begin

select CityId,CityName from City

return @@Rowcount
end

and at the end i call them in this code:

C#

if (!IsPostBack)
{
    DataSet Dataset = DataProvider.ExecuteDataSet("[dbo].[Select_City_SP]", CommandType.StoredProcedure);
    {
        CityDrpDown.DataSource=Dataset;
        CityDrpDown.DataTextField="CityName";
        CityDrpDown.DataValueField="CityId";
        CityDrpDown.DataBind();
    }
}

but when i debug the solution this exception appears:
Procedure or function 'Select_City_SP' expects parameter '@CityId', which was not supplied.
what shall i do?

Posted 14-Aug-12 19:19pm

mohammad ehsan

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-14T19:25:00

Solution 1

In your stored proc you mentioned 2 input parameters but its not required ..You are not using it anywhere. so remove it.

SQL

Create PROCEDURE [dbo].[Select_City_SP]
as
begin
 
select CityId,CityName from City

end

Posted 14-Aug-12 19:25pm

Santhosh Kumar Jayaraman

Comments

Santhosh Kumar Jayaraman 15-Aug-12 1:41am

have u compiled stored proc in database and are you pointing to correct database from application?

StianSandberg · Accepted Answer · 2012-08-14T19:27:00

Your command expects two parameters CityId and CityName. You could add this in your SqlCommand like this

C#

cmd.Parameters.AddWithValue("@CityId", 1);
cmd.Parameters.AddWithValue("@CityName", "MyCity");

Bu most likely you could just remove these parameters from your procedure:

SQL

ALTER PROCEDURE [dbo].[Select_City_SP]
as
begin
 
select CityId,CityName from City
 
return @@Rowcount
end

as you want it to return all rows.