Question 114787 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Convert method won't work when I'm trying to convert string variables to numeric & DateTime types (Npgsql Parameters)

So, I am using C#/.NET and am using Npgsql. Basically, I keep running into the error System.InvalidCastException: 'Can't write CLR type System.String with handler type NumericHandler' and I can’t seem to resolve it.

The error seems pretty self-explanatory. Basically, I’m trying to use a string but Postgresql wants a Numeric data type. (Would that be a decimal or double data type?)

For some context, I am re-writing old VB code to C# and in the VB version the initial declarations of the numeric variables are strings. And in the code there is some string manipulation methods that are used.

My thought process was it’d be easier and faster to just let the string manipulation stuff happen and then convert it to a decimal or a double later on. (Decimal’s and Double’s are Numeric postgresql equivalents, I think?)

So here’s an example of the initial declartions:

string tno = "";
string iw = "";
string ws = "";
string ftg = "0";
string hl = "0";
string ll = "0";
string date = "2012/02/04";
string va = "0";
string dev = "0";

Later on in the code there are some string manipulation methods that are used. As I said earlier I thought it’d be faster to just use some Conversion methods to convert the strings to their proper data types before feeding it to postgresql.

For example:

Convert.ToDecimal(tno);

Here’s an example of the SQL string:

string sqlString = @"UPDATE header
SET ft = @gftg, high_limit=@ghl, low_limit=@gll, start_date = @gsdd, avg_ga = @gvgga, std_dev = @gstd
WHERE cmpy_id = 'ABC' and tag_no = @gto and ip_num = @gim";

Here’s the Npgsql parameter code:

cmd.Parameters.Add("@gim", NpgsqlTypes.NpgsqlDbType.Varchar).Value = iw;                              
cmd.Parameters.Add("@gftg", NpgsqlTypes.NpgsqlDbType.Numeric).Value = ftg;                            
cmd.Parameters.Add("@ghl", NpgsqlTypes.NpgsqlDbType.Numeric).Value = hl;                              
cmd.Parameters.Add("@gll", NpgsqlTypes.NpgsqlDbType.Numeric).Value = ll;                              
cmd.Parameters.Add("@gsdd", NpgsqlTypes.NpgsqlDbType.Date).Value = date;                             
cmd.Parameters.Add("@gvgga", NpgsqlTypes.NpgsqlDbType.Numeric).Value = va;                              
cmd.Parameters.Add("@gstd", NpgsqlTypes.NpgsqlDbType.Numeric).Value = dev;

This isn’t really working though and it’s probably for an obvious reason that I’m hoping you guys can help me with.😅

Is there no easy way to do this and will I just have to change the initial variable declarations to the correct data types right away? So for example perhaps It should be more like this? Then I would have to mess with the code below it and do some string manipulation equivalents or something like that..

decimal tno = "";
string iw = "";
string ws = "";
decimal ftg = "0";
decimal hl = "0";
decimal ll = "0";
string date = "2012/02/04";
decimal va = "0";
decimal dev = "0";

Any ideas why the Convert.ToDecimal(tno) before inputting into the postgres npgsql parameters isn’t working?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to @Jeroen Mostert I finally fixed this.. It was simply that Convert.ToDecimal() didn't work. So I just used Decimal.TryParse() instead with a new variable and it works now.. (Now onto my other errors😅)

    Here's an example:

    decimal ftgAsDecimal;
Decimal.TryParse(ftg, out ftgAsDecimal);

    I fed it into my Npgsql parameters like this:

    cmd.Parameters.Add("@gftg", NpgsqlTypes.NpgsqlDbType.Numeric).Value = ftgAsDecimal;

    For DateTime variables I had to use DateTime.ParseExact() like Jeroen recommended above. Here's an example:

    DateTime dateAsDateTime = DateTime.ParseExact(date, "MM-dd-yyyy", null);

    (Keep in mind date above is a DateTime data typed variable)

    Just for absolute clarity purposes this is what it looked like being fed to Npgsql parameters:

    cmd.Parameters.Add("@gsdd", NpgsqlTypes.NpgsqlDbType.Date).Value = dateAsDateTime;

  2. 0

    Check https://www.npgsql.org/doc/api/NpgsqlTypes.NpgsqlDbType.html

    It looks like there’s a type for Double: NpgsqlTypes.NpgsqlDbType.Double

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search