By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,289 Members | 3,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,289 IT Pros & Developers. It's quick & easy.

Format of local variable as input parameter in stored procedure in sql server

P: 20
Stored procedure

ALTER proc [dbo].[spSearchCombo](@searchBy nvarchar(50),@searchKey nvarchar(50)) as
select * from CD_DETAILS where @searchBy like '%' +@searchKey+ '%'


I'm using ASP.net2.0 with c# to extract rows basedon search key from a text box and searchBy for the column to be searched.

When i use column name instead of @searchBy which comes from value selected from a ddropdownlist i get the desired result . There seems to be a problem with format of @searchBy and i get a blank page.Plz help me out.

Expand|Select|Wrap|Line Numbers
  1. protected void btnSearch_Click(object sender, EventArgs e)
  2.     {
  3.                 string constring = ConfigurationManager.AppSettings.Get("con").ToString();
  4.         SqlConnection conn = new SqlConnection(constring);
  5.         conn.Open();
  6.  
  7.  
  8.         SqlCommand cmdSP = new SqlCommand("spSearchCombo", conn);
  9.         cmdSP.CommandType=CommandType.StoredProcedure;
  10.  
  11.         cmdSP.Parameters.Add(new SqlParameter("@searchBy",SqlDbType.NVarChar,50));
  12.         cmdSP.Parameters["@searchBy"].Value=ddlSearchBy.SelectedValue.ToString();
  13.  
  14.         cmdSP.Parameters.Add(new SqlParameter("@searchKey", SqlDbType.NVarChar, 50));
  15.         cmdSP.Parameters["@searchKey"].Value=txtSearch.Text.Trim();
  16.  
  17.  
  18.         SqlDataAdapter da=new SqlDataAdapter(cmdSP);
  19.         DataSet ds=new DataSet();
  20.         da.Fill(ds);
  21.  
  22.         this.dgv1.DataSource=ds.Tables[0].DefaultView;
  23.         dgv1.DataBind();
  24.  
  25.  
  26.  
  27.     }
Jul 28 '10 #1
Share this Question
Share on Google+
3 Replies


Frinavale
Expert Mod 5K+
P: 9,731
Are you sure your stored procedure works?
Have you tried it using Query Analyzer or something?

-Frinny
Jul 29 '10 #2

P: 20
Hi Frinny
Thanx for responding
i have heard of query analyzer
But haven't used it yet,plz guide me if u can

My stored procedure works when I provide column name CD_NAME and win as search string in New query of SQL server 2005 and it gives a blank table with field names and no record

[code]execute spSearchCombo CD_NAME,win
Jul 30 '10 #3

Frinavale
Expert Mod 5K+
P: 9,731
Open Query Analyzer and try to execute the store procedure. Query Analyzer comes with MS SQL Server.

-Frinny
Jul 30 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.