SQLite database query with multiple WHERE conditions

Question

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.

What kind of ORM are you using? It seems to be Dapper with Extension but I am not sure. — Steve
– Steve, Commented Jul 6, 2014 at 16:40

chue x · Accepted Answer · 2014-07-06 17:05:28Z

15

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();

answered Jul 6, 2014 at 17:05

chue x

18.9k8 gold badges60 silver badges71 bronze badges

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

2 Comments

wheezer Over a year ago

I am using sqlite-net. I thought I tried every possible way to do a query like your second suggestion and I failed. But yours worked like a charm! Thank you so much! I was not putting the "FirstOrDefault()" part on it. Can I do something to give you some points for this? I just clicked the check mark. I don't have enough hutspa to give your answer a vote up, apparently.

chue x Over a year ago

@user3798080, accepting my answer gives me points. Upvoting also would give me points but as you say, you don't have enough "hutspa" yet.

Ashish Rajput · Accepted Answer · 2014-07-06 18:26:29Z

0

Try this

I hope both Id are int in database

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

edited Jul 6, 2014 at 18:26

answered Jul 6, 2014 at 16:29

Ashish Rajput

1,5291 gold badge11 silver badges20 bronze badges

1 Comment

wheezer Over a year ago

Thanks for your suggestion, Ashish. Chue answer did the trick so I didn't need to try yours.

Collectives™ on Stack Overflow

SQLite database query with multiple WHERE conditions

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related