PetaPoco not returning expected data

Question

I have a BIT data type on one of my columns.

I have written a query that does SELECT * FROM TABLE WHERE BITCOLUMN <> @0

It works fine if I pass in 1 or 0 but if I pass in 3 PetaPoco doesn't return the results I expect.

Executing the SQL in a query window does return all records when I use 3 as the parameter value.

Any ideas?

UPDATE: If I use string SQL = "SELECT * FROM TABLE WHERE BITCOLUMN <> " + MethodParam; This returns data as expected.

What are you expecting when you use @0, 3 and what do you actually get? — Trevor Pilley, Mar 14 '12 at 12:16
I've tested this on both SQL Server and SQLite, and it returns the expected value. You'll have to show the code where you are actually calling this so that we can see if there is anything there. — patmortech, Mar 14 '12 at 18:25

score 4 · Answer 1 · answered Mar 14 '12 at 10:42

4

Could you tell me what result you expecting? According to MSDN, A bit column can either be 1, 0 or null. It does not make senses to me when you pass 3 to it, since it will select all rows.

And my quick test shows that Petapoco behaves as expected.

using (var database = new Database("sql"))
{
    string sql = "SELECT COUNT(*) FROM TBLTESTBIT WHERE BITCOLUMN <> " + "3";
    var test = database.ExecuteScalar<long>("SELECT COUNT(*) FROM TBLTESTBIT WHERE BITCOLUMN <> @0", 3);
    var test2 = database.ExecuteScalar<long>(sql);

    Console.WriteLine(test == test2); // this output true
    Console.Read();
}

answered Mar 14 '12 at 10:42

Yin

301
2
11

You mean something like `database.ExecuteScalar("SELECT COUNT(*) FROM TBLTESTBIT WHERE BITCOLUMN <> @0", "3");`? It won't work, you have to convert it to int first. Because the final sql is `SELECT * FROM tblTestBit where bitColumn<>'3';` – Yin Mar 14 '12 at 11:45
What version of PetaPoco are you running? – Jon Mar 14 '12 at 11:47
Change your sql variable to string sql = "SELECT COUNT(*) FROM TBLTESTBIT WHERE BITCOLUMN <> " + 3; – Jon Mar 14 '12 at 11:48
I am running the latest version from nuget with SQL Server 2008. It still works for me. You may have to post your petaPoco version, Sql server version and method you wrote here for people to have a look. Have you tried to debug it? It should show you what the last execute statement is. – Yin Mar 14 '12 at 21:15
I'm running against SQL CE 4 and PetaPoco 4.0.2 – Jon Mar 15 '12 at 09:16
Maybe try to update PetaPoco if you don't have any customizations. Nuget allows you to easily fallback. – Yin Mar 15 '12 at 11:43

score 0 · Answer 2 · edited Oct 13 '16 at 10:03

0

Use new Sql() not Append.

You should use Query = new PetaPoco.Sql( over Query = PetaPoco.Sql.Append

edited Oct 13 '16 at 10:03

James Monger

10,181
7
62
98

answered Oct 13 '16 at 09:23

Jon Snow

11
1

score 0 · Answer 3 · answered Mar 14 '12 at 11:54

0

I think it will be because when you use a parametrised sql string, you are passing in int with a value of 3 in as one of the args. PetaPoco will then create an IDataParameter for that argument and the DbType will be set to DbType.Int32 by default since PetaPoco has no idea what the underlying table column type is.

answered Mar 14 '12 at 11:54

Trevor Pilley

16,156
5
44
60

I don't see why that would be a problem if it is an int. I assume directly querying the database does the same by converting it to an int – Jon Mar 14 '12 at 11:58
It depends what SQL Server does when it receives a parametrised query with a parameter value of an int which is being compared against a bit in the table. – Trevor Pilley Mar 14 '12 at 12:09
Well it should behave the same way as when I query the database directly – Jon Mar 14 '12 at 12:13

PetaPoco not returning expected data

3 Answers3