Look over my C# SQLite Query, what am I doing wrong?

Posted by CODe on Stack Overflow See other posts from Stack Overflow or by CODe
Published on 2011-01-05T02:23:30Z Indexed on 2011/01/05 2:54 UTC
Read the original article Hit count: 199

Filed under:
|
|
|

I'm writing a WinForms database application using SQLite and C#. I have a sqlite query that is failing, and I'm unsure as to where I'm going wrong, as I've tried everything I could think of.

public DataTable searchSubs(String businessName, String contactName)
    {
        string SQL = null;

        if ((businessName != null && businessName != "") && (contactName != null && contactName != ""))
        {
            // provided business name and contact name for search
            SQL = "SELECT * FROM SUBCONTRACTOR WHERE BusinessName LIKE %@BusinessName% AND Contact LIKE %@ContactName%";
        }
        else if ((businessName != null && businessName != "") && (contactName == null || contactName == ""))
        {
            // provided business name only for search
            SQL = "SELECT * FROM SUBCONTRACTOR WHERE BusinessName LIKE %@BusinessName%";
        }
        else if ((businessName == null || businessName == "") && (contactName != null && contactName != ""))
        {
            // provided contact name only for search
            SQL = "SELECT * FROM SUBCONTRACTOR WHERE Contact LIKE %@ContactName%";
        }
        else if ((businessName == null || businessName == "") && (contactName == null || contactName == ""))
        {
            // provided no search information
            SQL = "SELECT * FROM SUBCONTRACTOR";
        }
        SQLiteCommand cmd = new SQLiteCommand(SQL);
        cmd.Parameters.AddWithValue("@BusinessName", businessName);
        cmd.Parameters.AddWithValue("@ContactName", contactName);
        cmd.Connection = connection;
        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            return dt;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
            return null;
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }

I continually get an error saying that it is failing near the %'s. That's all fine and dandy, but I guess I'm structuring it wrong, but I don't know where! I tried adding apostrophes around the "like" variables, like this:

SQL = "SELECT * FROM SUBCONTRACTOR WHERE Contact LIKE '%@ContactName%'";

and quite honestly, that is all I can think of. Anyone have any ideas?

© Stack Overflow or respective owner

Related posts about c#

Related posts about winforms