CODEWITHSUNDEEP
c#asp.netpostgresql
DebPepDevuan
DebPepDevuan

Reputation: 489

PostgreSql ExecuteScalar ASP.NET

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.

Upvotes: 1

Views: 6060

Answers (1)

Hambone
Hambone

Reputation: 16397

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.

Upvotes: 2

Related Questions