Look over my C# SQLite Query, what am I doing wrong?
- by CODe
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?
