asp.net - Must declare the scalar variable @param problem -


newbie alert!

error:

must declare scalar variable "@param2".

must declare scalar variable "@param2" (twice 2 param2's)

 protected void button1_click(object sender, eventargs e) {    sqldatasource ds1 = new sqldatasource(getconnectionstring(), getselectionstring());    gridview1.datasource = ds1;    gridview1.databind(); }

and

  protected string getselectionstring()     {         string searchstring = textbox1.text.tostring();         if (radiobuttonlist1.selectedvalue == "all")         {             sqlparameter @param2 = new sqlparameter();             sqlcommand searchall = new sqlcommand("select document_name, document_summary document_details (document_id in (select document_id search_index (tag_id in (select distinct tag_id tags (tag_name '%'+@param2+'%'))))) union select document_name, document_summary document_details document_details_1 (document_name '%'+@param2+'%')");            searchall.parameters.addwithvalue("@param2", searchstring.tostring());             return (string)searchall.commandtext.tostring();         } 

textbox1 value passed user. have searched solutions around 6 hours... , still stuck problem. solutions please?

using vs2008 ms sql server 2008 r2 connection.

edit1: giving complete code.::

 

protected string getselectionstring() { string searchstring = "%"; searchstring = searchstring+ textbox1.text.trim().tostring(); searchstring =searchstring+ "%";

    if (radiobuttonlist1.selectedvalue == "all")     {         sqlparameter @param2 = new sqlparameter();         sqlcommand searchall = new sqlcommand("select document_name, document_summary document_details (document_id in (select document_id search_index (tag_id in (select distinct tag_id tags (tag_name @param2))))) union select document_name, document_summary document_details document_details_1 (document_name @param2)");         searchall.parameters.addwithvalue("@param2", searchstring.tostring());         return (string)searchall.commandtext.tostring();     }     if (radiobuttonlist1.selectedvalue == "filenames")     {         sqlparameter param2 = new sqlparameter();          sqlcommand searchfilename = new sqlcommand("select document_name, document_summary document_details (document_name @param2)");         searchfilename.parameters.addwithvalue("@param2", searchstring.tostring());         return (string)searchfilename.commandtext.tostring();     } 

protected void button1_click(object sender, eventargs e) { sqldatasource ds1 = new sqldatasource(getconnectionstring(), getselectionstring()); gridview1.datasource = ds1; gridview1.databind(); }

please note: binding gridview control. works if hardcode value of @param2 in query.

edit2: different approach different error:

 tried way,  sqlcommand temp1 = getselectionstring(); string temp2 = temp1.commandtext.tostring(); sqldatasource ds1 = new sqldatasource(getconnectionstring(), temp1.tostring()); gridview1.datasource = ds1; gridview1.databind(); 
....getting new error

could not find server 'system' in sys.servers. verify correct server name specified. if necessary, execute stored procedure sp_addlinkedserver add server sys.servers

system.data.sqlclient.sqlexception: not find server 'system' in sys.servers. verify correct server name specified. if necessary, execute stored procedure sp_addlinkedserver add server sys.servers.

you need use @param2 "stand-alone" parameter - not pack string!

sqlcommand searchall = new sqlcommand(    "select document_name, document_summary document_details      (document_id in         (select document_id search_index          (tag_id in (select distinct tag_id                            tags                            tag_name @param2))))      union      select document_name, document_summary document_details document_details_1      document_name @param2"); 

if want search string % @ beginning , end, need supply value of @param2

also: query might work lot better if break subselects , use single sql statement using join's join tables...

update: approach has basic flaw: seem expect if use parametrized query in sqlcommand you'll out full sql statement parameter value filled in when accessing searchall.commandtext - not case - parameter @param2 not substituted value!

so basically, cannot way you're doing right - need pass sqlcommand instance - not string! never work

update #2: need this:

protected void button1_click(object sender, eventargs e) {    // grab search string web ui    string searchstring = "%" + textbox1.text.trim() + "%";     // connection string    string connectionstring = getconnectionstring();     sqldatasource ds1 = new sqldatasource(connectionstring);     // sqlcommand select    ds1.selectcommand = getselectcommand(connectionstring, searchstring);     gridview1.datasource = ds1;    gridview1.databind(); } 

and

protected sqlcommand getselectcommand(string connectionstring, string searchvalue) {    // define query string - simplified!    string querystmt = "select document_name, document_summary document_details (document_id in (select document_id search_index (tag_id in (select distinct tag_id tags tag_name @param2)))) union select document_name, document_summary document_details document_details_1 document_name @param2";     // set sqlcommand based on query string , connection string passed in           sqlcommand cmd = new sqlcommand(querystmt, connectionstring);     // define parameter    cmd.parameters.add("@param2", sqldbtype.varchar, 100);     // set value parameter    cmd.parameters["@param2"].value = searchvalue;     // pass sqlcommand fill data source    return cmd; } 

Comments