Searching for a specific DateTime in a SQL Database(WebMatrix)

Question

I'm trying to search a SQL database for a specific set of DateTime items. My intention is to be able to search for a specific date, and then getting log data of that data onto the screen in a WebGrid.

I would appreciate any tips and help i can get since i'm fairly new to programming

My Initial Error:

System.Data.SqlServerCe.SqlCeException: The data type is not valid for the boolean operation. [ Data type (if known) = datetime,Data type (if known) = nvarchar

The full set of code is below here:

 @{
var db = Database.Open("Cafeen");

var grid = new WebGrid();
if (IsPost) {
    var SearchTerm = Request["Data"];
    var selectcommand = "SELECT * FROM logistics WHERE Date > SearchTerm";
    var dw = Convert.ToDateTime(SearchTerm);
    var SelectedData = db.Query(selectcommand, dw);
    grid = new WebGrid(source: SelectedData, defaultSort: "Stock", rowsPerPage:50);
}
}
</form>
<h1>Search for Log files</h1>
<form method="post">
    <div id="grid">
            Date: <input type="text" id="datepicker" size="30" value="@Request["Data"]" />
            <input type="submit" />
            <hr/>
            @if (IsPost) {
                    @grid.GetHtml(
                        tableStyle: "grid",
                        headerStyle: "head",
                        alternatingRowStyle: "alt",
                        columns: grid.Columns(
                            grid.Column("ProductName"),
                            grid.Column("Category"),
                            grid.Column("Price"),
                            grid.Column("PurchasePrice"),
                            grid.Column("Stock"),
                            grid.Column("Date")
        )
    )
            }
    </div>
</form>

<title>jQuery UI Datepicker - Format date</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<link rel="stylesheet" href="/resources/demos/style.css">
<script>
$(function() {
$( "#datepicker" ).datepicker();
$( "#format" ).change(function() {
  $( "#datepicker" ).datepicker( "option", "dateFormat", $( this ).val() );
 });
});
</script>

Answer 1

You're on the right track. But the way that you pass parameters into a SQL string is through something called parameterization. Your first parameter in the SQL string will be @0, the second @1, and so on. Those will correspond to the first parameter passed in the db.Query(). So you can think of it as db.Query( string, @0, @1,....). So to your specific case, i would change the sql a bit to look at just the date and then use the parameter:

var selectcommand = "SELECT * FROM logistics WHERE Date > @0";

and then your query line will look like:

var SelectedData = db.Query(selectcommand, dw);

and your dw variable is placed into the @0 part of the string. By the way, parameterization protects you from a nasty security vulnerability called SQL injection attack.