CODEWITHSUNDEEP
c#asp.netdatabaserazorwebmatrix
Aravin
Aravin

Reputation: 7067

Unable to pass 2 parameter in ASP.NET Razor - WebMatrix Database Query() method

I am unable to use WebMatrix database method Query() with 2 interpolate string.

Actual Code: (Not working)

@{
    var db = Database.Open("WebPagesMovies") ;
    var selectCommand = "SELECT * FROM Movies";        
    var item = "";
    var search = "";

    if(!Request.QueryString["searchTitle"].IsEmpty() ) {
        selectCommand = "SELECT * FROM Movies WHERE @0 LIKE @1";
        item = Request.QueryString["item"];
        search = "%" + Request.QueryString["search"] + "%";
    }

    var selectedData = db.Query(selectCommand, item, search); // **NOT WORKING**
    var grid = new WebGrid(source: selectedData, defaultSort: "Genre", rowsPerPage:3);
}

Working

@{
    var db = Database.Open("WebPagesMovies") ;
    var selectCommand = "SELECT * FROM Movies";   
    var search = "";

    if(!Request.QueryString["searchTitle"].IsEmpty() ) {
        selectCommand = "SELECT * FROM Movies WHERE Title LIKE @0";
        item = Request.QueryString["item"];
        search = "%" + Request.QueryString["search"] + "%";
    }

    var selectedData = db.Query(selectCommand, item, search);
    var grid = new WebGrid(source: selectedData, defaultSort: "Genre", rowsPerPage:3);
}

Why am I unable to interpolate 2 string like item and search in Query() method? Does it not supported in ASP.NET?

On debugging I can see that values are assigned to item and search. But values are not passed to Query() method.

Official Doc:

  1. https://www.asp.net/web-pages/overview/getting-started/introducing-aspnet-web-pages-2/form-basics
  2. https://msdn.microsoft.com/en-us/library/webmatrix.data.database.query(v=vs.111).aspx

Upvotes: 1

Views: 386

Answers (1)

Knox
Knox

Reputation: 2919

That's a very interesting approach. What your code is doing is using parameterized SQL. The parameters are @0, @1 and so. The db.Query statement passes the parameters into SQL. I believe there is a significant restriction on where these parameters can be used. They can only be used where data can go, not where SQL commands or table names or fields can go. So because Title is a field name, it can't be parameterized.

So guessing that sometimes you want to search on the title, sometimes on the release date, and so on, you have several choices.

One is to examine the incoming item variable, maybe in a switch statement, and then have several sql statements each which handles just one item. So you code would look like this psuedo code:

switch (item)
{
   case 'title':
       selectCommand = "SELECT * FROM Movies WHERE Title LIKE @0";
       break;
   case 'releaseDate':
        selectCommand = "SELECT * FROM Movies WHERE ReleaseDate = @0";
        break;
    ...
  }

That leads to one of the reasons that a parameterized field name doesn't make sense. Because 'Title' is string, it can use the LIKE command, but a date couldn't.

Upvotes: 1

Related Questions