Cant Search SQL DB from Front End

Question

I have created a database within Webmatrix, it is one Table called Fish. It stores a number of fish, where they are caught, how they are caught etc. I created a front end within Webmatrix too so that it will be possible to search the database. When I type a fish into the search box and then hit search it throws up an error which I cannot figure out. My Database is called 'Sourcing Matrix' and the table is called 'Fish'. Here is the code I have done for the search function;

@{ var db = Database.Open("Sourcing Matrix"); var selectCommand = "SELECT * FROM Fish"; var searchTerm = "";

    if(!Request.QueryString["searchField"].IsEmpty() ) {
selectCommand = "SELECT * FROM Fish WHERE Fish = @0";
searchTerm = Request.QueryString["searchField"];
}

if(IsPost){
 var selectedData = db.Query(selectCommand, searchTerm);
} } <h1>Search for Fish stored in the 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 is the error which is returned:

The column name is not valid. [ Node name (if any) = ,Column name = Fish ] Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlServerCe.SqlCeException: The column name is 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, they stop the error from happening but they wont return any results either. The only thing which seems logical to me would be to have the whole table to select from, in case the common name, latin name or catch area is searched for, and not just info from one column.

BenMorel · Accepted Answer · 2014-06-27 14:00:00Z

0

Your problem deals with the WHERE clause of the sql SELECT statement: SQL WHERE Clause.

The WHERE clause accepts many comparison operator, but always you must specify the column to search and use the OR operator to chain many search conditions.

Another problem is that the = operator searches for an exact match; if you want to search for a pattern you must use the LIKE operator with the "%" sign to define wildcards (missing letters) both before and after the pattern.

So, if your Fish table has a column named "common name" and a column named "latin name" and you want to search all the rows contain a given pattern in one of the two columns, your code should be:

selectCommand = "SELECT * FROM Fish WHERE [common name] LIKE @0 OR [latin name] LIKE @0";
searchTerm = "%" + Request.QueryString["searchField"] + "%";

Edited

Your error could be generated by the final OR. Your query should end with ".... OR [Reason] LIKE @0". I suggest to try with a search with a limited number of fields and test if it works, adding the other fields gradually.

Anyway, generally a relational database isn't searched in this way. There are functionalities, like SQL Server Full Text Search, or products, like Apache Solr, or database categories, like non-relational databases, that accomplish better this goal.

For a "standard way" of filtering a Sql Ce table with WebMatrix look at this good article: Displaying Search Results In A WebGrid.

edited Jun 27, 2014 at 14:00

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Jun 19, 2014 at 13:09

GmG

1,3721 gold badge9 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cozak Over a year ago

Thanks for the help - I understand where I have gone wrong. I switched out the lines and added the extra columns, i still get an error though but it now shows as : There was an error parsing the query. [ Token line number = 1,Token line offset = 516,Token in error = OR ] Description: An unhandled exception occurred during the execution of the current web request.

Cozak Over a year ago

Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 1,Token line offset = 516,Token in error = OR ] Line 25: Line 26: @if(!Request.QueryString["searchField"].IsEmpty() ){ Line 27: foreach(var row in db.Query(selectCommand, searchTerm)) { Line 28: <div class="col_12 box"> Line 29: <form method="post">

GmG Over a year ago

@Cozak Please post your sql query (the content of your selectCommand variable)

Cozak Over a year ago

Sorry for the slow reply - selectCommand = "SELECT * FROM Fish WHERE [Common Name] LIKE @0 OR [Latin Name] LIKE @0 OR [Method of Production] LIKE @0 OR [Catch Area] LIKE @0 OR [Country of Production (Farmed)] LIKE @0 OR [Area Detail] LIKE @0 OR [Stock Detail] LIKE @0 OR [Method of Capture / Production] LIKE @0 OR [Accreditation] LIKE @0 OR [Certifying Body] LIKE @0 OR [MSC Rating] LIKE @0 OR [Other Rating] LIKE @0 OR [Do SHL Source?] LIKE @0 OR [Reason] LIKE @0 OR";

Collectives™ on Stack Overflow

Cant Search SQL DB from Front End

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related