Function or procedure expects @searchKey which was not in SQL server 2005 and ASP.net

[shanboy]

Hi guys
i'm using ASP.net ans SQL server 2005 with stored procedure to do simple search from a table in database.Plz help me out

The error i get Function or procedure expects @searchKey which was not supplied.

protected void btnSearch_Click(object sender, EventArgs e)
{
string constring = ConfigurationManager.AppSettings.Get("con").ToString();
SqlConnection conn = new SqlConnection(constring);
conn.Open();


SqlCommand check = new SqlCommand("spSearch", conn);
check.CommandType = CommandType.StoredProcedure;


check.Parameters.Add(new SqlParameter("@searchKey", SqlDbType.NVarChar,50));
check.Parameters["@searchKey"].Value = txtSearch.Text.Trim();



SqlDataAdapter da = new SqlDataAdapter("spSearch", conn);


DataSet ds = new DataSet();
da.Fill(ds);
dgv1.DataSource = ds;
dgv1.DataBind();



}

Stored Procedure

ALTER proc [dbo].[spSearch](@searchKey nvarchar(50))  as
select * from CD_details where CD_NAME like '%@searchKey%'

[pritesh2010]

you have done mistak over this statment
SqlDataAdapter da = new SqlDataAdapter("spSearch", conn);
we cannot pass the Sp to adptaer. see asapter's parameter insted of passing Sp pass your command. i.e.

SqlCommand check = new SqlCommand("spSearch", conn);
check.CommandType = CommandType.StoredProcedure;
check.Parameters.Add(new SqlParameter("@searchKey", SqlDbType.NVarChar,50));
check.Parameters["@searchKey"].Value = txtSearch.Text.Trim();
SqlDataAdapter da = new SqlDataAdapter(check , conn);

if you want more detail see this Link

[rohand]

Yes Pritesh2010 We can't pass the Stored Procedure to SqlDataAdapter but we can pass the SqlCommand object to SelectCommand property of SqlDataAdapter and then Fill the dataset or datatable and retrieve the data from dataase table.

Shanboy - in your code, don't pass the name of your Stored procedure in dataadapter but just create the instance of dataadapter and then use

da.SelectCommand = check

and rest of the things are fine..
hope it will help you..

[shanboy]

Hi pritesh2010
As u told me i tried but I get the error

Could not find server 'System' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Am i doing correctly.Plz guide

protected void btnSearch_Click(object sender, EventArgs e)
{
    string constring = ConfigurationManager.AppSettings.Get("con").ToString();
    SqlConnection conn = new SqlConnection(constring);
    conn.Open();

    SqlCommand check = new SqlCommand("spSearch", conn);
    check.CommandType = CommandType.StoredProcedure;
    check.Parameters.Add(new SqlParameter("@searchKey", SqlDbType.NVarChar, 50));
    check.Parameters["@searchKey"].Value = txtSearch.Text.Trim();
    //SqlDataAdapter da = new SqlDataAdapter();
    //da.SelectCommand = check;



    SqlDataAdapter da = new SqlDataAdapter(check.ToString(), conn); 

    DataSet ds = new DataSet();
    da.Fill(ds);
    dgv1.DataSource = ds;
    dgv1.DataBind();
}

I get the error

Could not find server 'System' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

[shanboy]

Hi Rohand
When i went abt ur suggestion i dont get any error but no result is displayed....
Whether the following code is right

protected void btnSearch_Click(object sender, EventArgs e)
{
    string constring = ConfigurationManager.AppSettings.Get("con").ToString();
    SqlConnection conn = new SqlConnection(constring);
    conn.Open();

    SqlCommand check = new SqlCommand("spSearch", conn);
    check.CommandType = CommandType.StoredProcedure;
    check.Parameters.Add(new SqlParameter("@searchKey", SqlDbType.NVarChar, 50));
    check.Parameters["@searchKey"].Value = txtSearch.Text.Trim();
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = check;




    DataSet ds = new DataSet();
    da.Fill(ds);
    dgv1.DataSource = ds;
    dgv1.DataBind();
}

[pritesh2010]

Hi pritesh2010
As u told me i tried but I get the error

Could not find server 'System' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Am i doing correctly.Plz guide

protected void btnSearch_Click(object sender, EventArgs e)
{
string constring = ConfigurationManager.AppSettings.Get("con").ToString();
SqlConnection conn = new SqlConnection(constring);
conn.Open();

SqlCommand check = new SqlCommand("spSearch", conn);
check.CommandType = CommandType.StoredProcedure;
check.Parameters.Add(new SqlParameter("@searchKey", SqlDbType.NVarChar, 50));
check.Parameters["@searchKey"].Value = txtSearch.Text.Trim();
//SqlDataAdapter da = new SqlDataAdapter();
//da.SelectCommand = check;
SqlDataAdapter da = new SqlDataAdapter(check.ToString(), conn);
DataSet ds = new DataSet();
da.Fill(ds);
dgv1.DataSource = ds;
dgv1.DataBind();
}

I get the error

Could not find server 'System' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

as per i told you to see that link where it gives the compleate solution of your question.
in your code there is some mistack then try this code it will work as i tested in my computer it works.

protected void btnSearch_Click(object sender, EventArgs e)
    {
       string constring = ConfigurationManager.AppSettings.Get("con").ToString();
        SqlConnection conn = new SqlConnection(constring);
        conn.Open();
        SqlCommand check = new SqlCommand("spSearch", conn);
        check.CommandType = CommandType.StoredProcedure;
       check.Parameters.Add(“@searchkey”,SqlDbType.Nvarchar,50).Values=txtSearch.Text;
        SqlDataAdapter da = new SqlDataAdapter(check);
        DataSet ds = new DataSet();
        da.Fill(ds,” CD_details”);
       this. dgv1.DataSource = ds;
      this.dgv1.DataMember=”CD_details”;
        dgv1.DataBind();
}

[shanboy]

Hi pritesh
Thanks for the effort u had put in to help me out
hats off

In aspx.cs ur help was bang on.
Even then i got a blank result page

problem was with stored procedure
once i gave input parameter as '%' +@searchKey+ '%' in select statement i got the desired result

Thank bye