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
Post a Comment