none
Passing parameters to a stored procedure in C#

    Question

  • Has anyone encountered this before?

    Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.

    It seems that my code is not passing the parameter to the stored procedure.

    When I click this hyperlink:

    <asp:HyperLink

    ID="HyperLink1"

    Runat="server"

    NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'

    Text='<%# Eval("Name") %>'

    ToolTip='<%# Eval("Description") %>'

    CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>

    </asp:HyperLink>

    it is suppose to get the country name and description, based on the country id.

    I am passing the country id like this.

    protected void Page_Load(object sender, EventArgs e)

    {

    PopulateControls();

    }

    private void PopulateControls()

    {

    string countryId = Request.QueryString["CountryID"];

    if (countryId != null)

    {

    CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);

    divisionNameLabel.Text = cd.Name;

    divisionDescriptionLabel.Text = cd.Description;

    }

    }

    To my app code like this:

    public struct CountryDetails

    {

    public string Name;

    public string Description;

    }

    public static class DivisionAccess

    {

    static DivisionAccess()

    public static DataTable GetCountry()

    {

    DbCommand comm = GenericDataAccess.CreateCommand();

    comm.CommandText = "GetCountry";

    return GenericDataAccess.ExecuteSelectCommand(comm);

    }

    public static CountryDetails GetCountryDetails(string cId)

    {

    DbCommand comm = GenericDataAccess.CreateCommand();

    comm.CommandText = "GetCountryDetails";

    DbParameter param = comm.CreateParameter();

    param.ParameterName = "@CountryID";

    param.Value = 2;

    param.DbType = DbType.Int32;

    comm.Parameters.Add(param);

    DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);

    CountryDetails details = new CountryDetails();

    if (table.Rows.Count > 0)

    {

    details.Name = table.Rows[0]["Name"].ToString();

    details.Description = table.Rows[0]["Description"].ToString();

    }

    return details;

    }

     

    As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get

    Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied

    Someone please help!

     

    Thanks Nickdel68

    Monday, March 26, 2007 6:37 PM

All replies

  • What is the click event code on the menu(country name)?
    Monday, March 26, 2007 6:45 PM
  • I am moving this to the .NET Framework Data Access and Storage forums, which is dedicated to managed code.
    Monday, March 26, 2007 6:46 PM
  • Not sure what you mean, sorry, this is sort of new to me. I guessing you are referring to the code behind page.

    This is what I have on my code behind page.

    {

    protected void Page_Load(object sender, EventArgs e)

    {

    list.DataSource = DivisionAccess.GetCountry();

    list.DataBind();

    }

    Monday, March 26, 2007 7:39 PM
  • Debug your code..

    First Check are you getting Country Id from Query string or not..?

     

     

    Tuesday, March 27, 2007 9:48 AM
  • In this part of the code I am:

    <asp:HyperLink

    ID="HyperLink1"

    Runat="server"

    NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'

    Text='<%# Eval("Name") %>'

    ToolTip='<%# Eval("Description") %>'

    CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>

    </asp:HyperLink>

     

    But in this other part, I am not:

     

    private void PopulateControls()

    {

    string countryId = Request.QueryString["CountryID"];

    if (countryId != null)

    {

    CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);

    divisionNameLabel.Text = cd.Name;

    divisionDescriptionLabel.Text = cd.Description;

    }

    }

     

    I tested it by replacing Request.QueryString["CountryID"] with the actual countryid number and the code actually worked. Any idea why it can't request it?

     

    Nick

    Tuesday, March 27, 2007 3:29 PM
  • I'm not completely sure here, but I'm not sure you can do that.  I was trying something similar a while back and couldn't get it to function properly, so I cut my losses and went another route.  Maybe if you build that string in the code and then insert it you'll have better results.
    Tuesday, March 27, 2007 7:57 PM
  • The query string to a page is only available the first time the page is visited. When the page is posted back (such as when you click on the Hyperlink control), the query string is not available. If you need to have access to a query string value for the life of the page, cache it in viewstate:

     



    ViewState["CountryID"] = Request.QueryString["CountryID"];
     

     

    Tuesday, April 03, 2007 5:57 PM
  • if u haven't done with resolving the doubt than u can try this out

    Just Have the Command type = Store Procedure .

    and check whether the Name of Input Parameter in the Store Procedure should be                CountryID

    while the Datatype should also match i.e int


    DEwang


    Wednesday, April 04, 2007 12:15 PM