Adding query parameter to NpgsqlCommand results in Exception

[Mark Yorkovich]

I'm binding a DataTable to a DGV with this method:

public DataTable GetDgvData(string selectQuery, string companyFilter, string categoryFilter, string idFilter)
{
using (NpgsqlConnection conn = new NpgsqlConnection(connString))
using (NpgsqlCommand cmd = new NpgsqlCommand(selectQuery, conn))
{
cmd.Parameters.Add(new NpgsqlParameter("company", companyFilter));
if (!string.IsNullOrEmpty(idFilter)) cmd.Parameters.Add(new NpgsqlParameter("idList", idFilter));
if (categoryFilter != "All Categories") cmd.Parameters.Add(new NpgsqlParameter("category", categoryFilter));

DataSet ds = new DataSet();

conn.Open();

using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
da.Fill(ds);
}
conn.Close();

return ds.Tables[0];
}
}

Initially, it didn't have the last parameter, string idFilter or the second cmd.Parameters.Add() line . And all calls to the method have been working fine and populating the DGVs that use the method.

I then added a search function to my WinForm, that searches the DGV's DataTable for a user-entered string, iterates through the DataTable, builds a list of the record IDs for the rows with the search string, and then I need to rebuild the DGV with just the rows that have the search string in a cell in the DataTable.

Here's the the function for that search process:

private void btnSearch_Click(object sender, EventArgs e)
{
string searchText = txtSearchCatsGrid.Text;
string idToAddToList = "";
string tempIdList = "";
string idListForQuery = "";

DataTable dt = ((DataTable)dgvCategories.DataSource);
string cellValue;
int rowIndex;
int columnIndex;

for (rowIndex = 0; rowIndex <= dt.Rows.Count - 1; rowIndex++)
{
for (columnIndex = 0; columnIndex <= dt.Columns.Count - 1; columnIndex++)
{
cellValue = dt.Rows[rowIndex][columnIndex].ToString();

if (searchText == cellValue)
{
// Set corresponding dgvCategories cell's background color to yellow
dgvCategories[columnIndex, rowIndex].Style.BackColor = Color.Yellow;

idToAddToList = dt.Rows[rowIndex][0].ToString();
}
}

if (!string.IsNullOrEmpty(idToAddToList) && !tempIdList.Contains(idToAddToList)) tempIdList += idToAddToList + ",";

}

if (!string.IsNullOrEmpty(tempIdList))
{
idListForQuery = tempIdList.Remove(tempIdList.Length - 1, 1);
}

Console.WriteLine("idListForQuery: " + idListForQuery);

// Refresh dgvCategories with just rows from search, using idListForQuery

string companyFilter = cboSelectCompany.Text;
string categoryFilter = cboSelectCategory.Text;

db categoriesData = new db();

if (categoryFilter == "All Categories")
{
string catsQuery = "SELECT id, category, source_company, old_value, old_desc, new_value, new_desc, reference1, reference2 " +
"FROM masterfiles.xref"+
" WHERE company_name = @company" +
" AND id IN (@idList)" +
" ORDER BY category, old_value, source_company";
this.dtCategories = categoriesData.GetDgvData(catsQuery, companyFilter, categoryFilter, idListForQuery);
}
else
{
string catsQuery = "SELECT id, category, source_company, old_value, old_desc, new_value, new_desc, reference1, reference2 " +
"FROM masterfiles.xref" +
" WHERE company_name = @company" +
" AND category = @category" +
" AND id IN (@idList)" +
" ORDER BY old_value, source_company";
this.dtCategories = categoriesData.GetDgvData(catsQuery, companyFilter, categoryFilter, idListForQuery);
}

// TODO: Need to check this.dtCategories.Rows.Count

dgvCategories.DataSource = this.dtCategories;

if (dtCategories.Rows.Count == 0)
{
return;
}
else
{
dgvCategories.Columns[0].Visible = false;
dgvCategories.Rows[0].Cells[0].Selected = false;
}
}

The list of IDs passed to the GetDgvData() function is correct, and as far as I can tell is being implemented exactly the same as the company and category filters. The program runs fine when the form loads and the DGV is initially populated (I'm passing an empty string to GetDgvData() for all other calls to it), but when I use that search function, I get an error at da.Fill(ds), which says, "Npgsql.PostgresException: '42883: operator does not exist: integer = text'".

'selectQuery' looks like this:

SELECT id, category, source_company, old_value, old_desc, new_value, new_desc, reference1, reference2 FROM masterfiles.xref WHERE company_name = @company AND id IN (@idList) ORDER BY category, old_value, source_company

and idList looks like this:

1475,1476,1477,1331,1332

I'm interpreting the exception to mean that the IDs need to be integers, but I can't figure out how to pass that list of IDs to the GetDgvData() method as integers - if that is even the issue here at all. Do I need to build the list of IDs as an array and pass the list to GetDgvData() that way? Or, in GetDgvData() cast each of those IDs as an int?

Continue reading...