SQLite database query with multiple WHERE conditions

c#, database, sqlite, sqlite-net

Solution

Assuming you are using SQLite-Net as your ORM, you can just pass in the parameters after the query. As far as I know there is no support for anonymous classes, as in your example. Try this:

var existingmaintenance = db.Query<maintenance>(
    "select * from maintenance where VehID = ? AND MaintID = ?",
    Maintenance.Vehicleid, Maintenance.Maintid).FirstOrDefault();

You can also use a linq query, like so:

var existingmaintenance = db.Table<maintenance>().Where 
    (c => c.VehID == Maintenance.Vehicleid && 
    c.MaintID == Maintenance.Maintid).FirstOrDefault();

Problem

Trying to develop a Windows 8.1 Store App. Amongst other difficulties, I need to retrieve records from a sqlite database with two parameters on the where clause. I can successfully query with one parameter in the where clause but it crashes everything when I try to use two parameters. Here is my code for this: ``` public string SaveMaint(MaintViewModel Maintenance) { string result = string.Empty; using (var db = new SQLite.SQLiteConnection(App.DBPath)) { string change = string.Empty; try { var existingmaintenance = db.Query<maintenance> ("select * from maintenance where VehID = ? AND MaintID = ?", new String[] {Maintenance.Maintid, Maintenance.Vehicleid}); // var existingmaintenance = (db.Table<maintenance>().Where // (c => c.MaintID == Maintenance.Maintid).SingleOrDefault()); if (existingmaintenance != null) { existingmaintenance.VehID = Maintenance.Vehicleid; existingmaintenance.MaintID = Maintenance.Maintid; existingmaintenance.ServiceDate = Maintenance.Servicedate; existingmaintenance.ServiceCost = Maintenance.Servicecost; existingmaintenance.ServiceLocation = Maintenance.Servicelocation; existingmaintenance.ServiceNote = Maintenance.Servicenote; existingmaintenance.ServiceOdom = Maintenance.Serviceodom; int success = db.Update(existingmaintenance); } else { int success = db.Insert(new maintenance() { VehID = Maintenance.Vehicleid, MaintID = Maintenance.Maintid, ServiceDate = Maintenance.Servicedate, ServiceCost = Maintenance.Servicecost, ServiceLocation = Maintenance.Servicelocation, ServiceNote = Maintenance.Servicenote, ServiceOdom = Maintenance.Serviceodom }); } result = "Success"; } catch { result = "This project was not saved."; } } return result; } ``` Please refer to the line in which `existingmaintenance` variable is defined. The commented out version of this line works fine. When I substitute the variable definition with the two parameter query (obtained using a different method because I couldn't figure out how to add a second parameter to the Table query approach), it crashes. Thanks for any help you can give. Sorry that I only half understand what I'm doing.

Original source