html - Cant Search SQL DB from Front End -

html - Cant Search SQL DB from Front End -

i have created database within webmatrix, 1 table called fish. stores number of fish, caught, how caught etc. created front end end within webmatrix possible search database. when type fish search box , nail search throws error cannot figure out. database called 'sourcing matrix' , table called 'fish'. here code have done search function;

@{ var db = database.open("sourcing matrix"); var selectcommand = "select * fish"; var searchterm = "";

if(!request.querystring["searchfield"].isempty() ) { selectcommand = "select * fish fish = @0"; searchterm = request.querystring["searchfield"]; } if(ispost){ var selecteddata = db.query(selectcommand, searchterm); } } <h1>search fish stored in database</h1>

search: search

@if(!request.querystring["searchfield"].isempty() ){ foreach(var row in db.query(selectcommand, searchterm)) { <div class="col_12 box"> <form method="post"> <!--entry in search box--> </form> </div> } } else { <div class="col_12 box"> <form method="post"> <!--no entry in search box--> </form> </div> }

here error returned:

the column name not valid. [ node name (if any) = ,column name = fish ] description: unhandled exception occurred during execution of current web request. please review stack trace more info error , originated in code.

exception details: system.data.sqlserverce.sqlceexception: column name not valid. [ node name (if any) = ,column name = fish ]

source error:

line 25: line 26: @if(!request.querystring["searchfield"].isempty() ){ line 27: foreach(var row in db.query(selectcommand, searchterm)) { line 28: line 29:

i have tried various different column names, stop error happening wont homecoming results either. thing seems logical me have whole table select from, in case mutual name, latin name or grab area searched for, , not info 1 column.

your problem deals clause of sql select statement: sql clause.

the clause accepts many comparing operator, must specify column search , utilize or operator chain many search conditions.

another problem = operator searches exact match; if want search pattern must utilize operator "%" sign define wildcards (missing letters) both before , after pattern.

so, if fish table has column named "common name" , column named "latin name" , want search rows contain given pattern in 1 of 2 columns, code should be:

selectcommand = "select * fish [common name] @0 or [latin name] @0"; searchterm = "%" + request.querystring["searchfield"] + "%";

edited

your error generated final or. query should end ".... or [reason] @0". suggest seek search limited number of fields , test if works, adding other fields gradually.

anyway, relational database isn't searched in way. there functionalities, sql server total text search, or products, apache solr, or database categories, non-relational databases, accomplish improve goal.

for "standard way" of filtering sql ce table webmatrix @ article: displaying search results in webgrid.

html sql webmatrix