System.Data.SQLite

Hi there, I hope someone can help because this makes no sense to me!

I am working in C# (VS2005 sp1), with SQLite build 62.

I create a table adapter based on the following SQL:

SELECT    
    products.products_barcode,
    products.products_name,
    products.products_salePrice * (1 + @stdMarkup / 100) AS salePrice,
    manufacturers.manufacturer_name,
    categories.category_name,
    categories.category_id,
    manufacturers.manufacturer_id,
    products.products_id
FROM
    products
    INNER JOIN    manufacturers ON products.products_manufacturer_id = manufacturers.manufacturer_id
    INNER JOIN    categories ON products.products_category_id = categories.category_id
WHERE   
    (products.products_name LIKE '%' + @prod_filter + '%')
OR
            (manufacturers.manufacturer_name LIKE '%' + @man_filter + '%')
OR
            (categories.category_name LIKE '%' + @cat_filter + '%')
OR
    (products.products_barcode = @bar_filter)

The method that calls this SQL is called GetFilteredData (see below).

In code, I use the following to retrieve filtered product data:

localDataSetTableAdapters.productFilterTableAdapter productsAdapter = new localDataSetTableAdapters.productFilterTableAdapter();
productsAdapter.Connection.ConnectionString = "Data Source = " + generic.programFolder() + "data.s3db" + "; Version=3";

string filter = tbSearchString.Text;
localDataSet.productFilterDataTable productsTable = productsAdapter.GetFilteredData(this.stdMarkUp, filter, filter, filter, filter);

The productsTable never gets populated with data. Always 0 rows. I have checked and the data is there, the query runs fine against the SQL server with the same data.

Am I missing something?

Thanks, in anticpation of a solution.

Chew

 Hi Knch

 Thanks for your reply.

I tried using || in the SQL for my table adapter but Visual Studio says it's illegal syntax. Are we saying that SQLite is incompatible in this way with VS?

Chew

VS doesn't recognize the syntax, but SQLite does.  Just hit OK to continue and VS will send the statement onto SQLite as-is.

 In VS 2010, this non-standard (SQL) concat operater wreaks all sorts of havoc with the data source designers.You can't edit data sources with either the designer or the data source wizard. In the sqlite db, you have to create a SQL view that does the concat for you and bind to that.