PostgreSql ExecuteScalar ASP.NET

Question

I am porting a ASP.NET Webforms application from MSSQL to PostgreSql. I am running into a problem with this code snippet.

    string checkIfExist ="SELECT COUNT(*) FROM tblHairRecord WHERE customerid = @customerID AND typetitle1= @titleParam AND type1_1=@typeValue";
        //Open the SQL Connectionn
        con.Open();
        //Set all the parameters
        NpgsqlCommand cmdChk = new NpgsqlCommand(checkIfExist,con);
        cmdChk.Parameters.Add("@customerID", NpgsqlDbType.Integer);
        cmdChk.Parameters.Add("@titleParam", NpgsqlDbType.Char, 10);
        cmdChk.Parameters.Add("@typeValue", NpgsqlDbType.Char, 10);
        cmdChk.Parameters["@customerID"].Value = lblCustIDed.Text;
        cmdChk.Parameters["@titleParam"].Value = "顔型";
        cmdChk.Parameters["@typeValue"].Value = "卵型";
        //Run the the count query and Close the connection
        int checkIfExistCount = (int)cmdChk.ExecuteScalar();
        con.Close();

The Error that the web page is returning is *

Input string was not in a correct format.

* The stack trace leads me to the line in the snippet

int checkIfExistCount = (int)cmdChk.ExecuteScalar();

I know that this snippet works because it executes with no problems in the MSSQL environment. I am fairly new to PostgreSql, and think basically PostgreSql is not liking the "int" there for it assumes the wrong format is being submitted.

That error message comes from C# not from Postgresql. What if you replace the @titleParam and the @typeValue values with plain ASCII characters? — Clodoaldo Neto
– Clodoaldo Neto, Commented Feb 1, 2017 at 13:24
@Clodoaldo -- I changed the values as you suggested to ASCII the result is the same error. To add to that the reason I am running the "int checkIfExistCount = (int)cmdChk.ExecuteScalar(); is because the customerid is stored in a label that is a text value. The "int" lets me pass that as an integer in MSSQL and it work. Not sure if that is correct approach in PostgreSQL — DebPepDevuan
– DebPepDevuan, Commented Feb 1, 2017 at 13:34
@Vao-- You are correct, I fixed the syntax. Then I tried it again with the same error. Maybe the real question is am I running the ExecuteScalar properly for PostgreSql — DebPepDevuan
– DebPepDevuan, Commented Feb 1, 2017 at 13:37
have you checked the sql script finally executed after the parameters completion? Is it correct? And what if you don't cast the ExecuteScalar() result as integer and leave it as object, what do you get then? — jambonick
– jambonick, Commented Feb 1, 2017 at 13:48

Hambone · Accepted Answer · 2017-02-02 02:36:10Z

2

The count aggregate function in PostgreSQL returns a bigint, or in C# terms an Int64 / long. I think the cast is too brute force. ExecuteScalar does return a typed object, so I think you need to use Convert instead of a cast:

int checkIfExistCount = Convert.ToInt32(cmdChk.ExecuteScalar());

To illustrate this, run this simple command in SQL

select count (*)

And you'll see it returns a 64-bit integer.

-- EDIT : for context within the comments, I'm leaving this in, but disregard everything below --

Off topic, but your code could be abbreviated to the following:

NpgsqlCommand cmdChk = new NpgsqlCommand(checkIfExist, con);
cmdChk.Parameters.AddWithValue("customerId", lblCustIDed.Text);
cmdChk.Parameters.AddWithValue("titleParam", "顔型");
cmdChk.Parameters.AddWithValue("typeValue", "卵型");

int checkIfExistCount = Convert.ToInt32(cmdChk.ExecuteScalar());
con.Close();

Declaring the parameters and assigning the values in two steps is the way to go if you are doing a declare once, execute many (like with inserts/updates). In this case, you are declaring once, executing once. Parameters are still a great idea, but the AddWithValue method keeps the code nice and compact.

-- EDIT - except this line. It's still relevant --

Also of interest, many databases require the "@" prefix when declaring parameters, but they are optional in Postgres.

edited Feb 2, 2017 at 2:36

answered Feb 2, 2017 at 2:10

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joel Coehoorn Over a year ago

There are good reasons to avoid AddWithValue(). This is for Sql Server, but most DB's have similar rules: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…

Hambone Over a year ago

@JoelCoehoorn -- I seriously had no idea. That's a great article -- very succinct and to the point. Thanks for sharing. I knew about the possibility of a datatype mismatch but didn't know about the other factors. I actually thought it interesting in the snippet above that it worked since the .Text property was most assuredly a string, whereas the field in PostgreSQL was an integer. My answer was going to point to that as a culprit until I tested it and discovered it worked. Now I know that came at a cost. Again, thanks for enlightening

Joel Coehoorn Over a year ago

Remember...most of the time it doesn't matter. ADO.Net is pretty good at making correct inferences, and even when it gets it wrong it only matters if results in a narrowing conversion or breaks an index. I wouldn't call AddWithValue() wrong, per se. It's just fine in many situations. I just want people to be aware of what's going on.

Collectives™ on Stack Overflow

PostgreSql ExecuteScalar ASP.NET

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related