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

'System.InvalidCastException: Can't write CLR type System.String with handler type NumericHandler' Very stuck on data type issue in postgresql with C#

I am re-writing some old VB code to C# and converting everything to postgresql from informix and sql server. I am quite stuck on an issue that I believe is a data type issue due to postgresql being stricter with data types. Before I tried casting AS NUMERIC. I also tried not using type casting in the pstrng sql string as well.

I tried changing NpgsqlTypes.NpgsqlDbType‘s to every different data type that would make sense. I feel like I tried everything. Also to be transparent, I am not that well-versed in postgresql so it’s possible it’s some obvious easy syntax thing.

Below is the declaration of variables:

string aString;
int id = 0;
string cmpyid = "";
string tgnn = "";
string inm = "";
string Whs = "";
string ft = "0";
string gh_lt = "0";
string gll = "0";
string ghsd = "1998/01/01";
string gag = "0";
string gsd = "0";
var dList = new List<string>();
string Gge = "";
double Gcv = 0.0d;
double Pgcv = 0.0d;
string ft = "";
int fconv = 0;
double pnum = 0.0d;
decimal anm = 0.0m;
decimal dv = 0.0m;
decimal sdn = 0.0m;
decimal tdvv = 0.0m;
decimal eage = 0.0m;
decimal ie = 0.0m;
decimal tm = 0.0m;

Below is the code where something is going wrong:

string pstrng= "";
pstrng= @"UPDATE gamma_header 
          SET 
              CAST('ft=@ftt' AS DECIMAL), CAST(gh_lt=@ghl AS DECIMAL),
              CAST(gag = @gav AS DECIMAL), CAST(gsd = @gst AS DECIMAL)
          WHERE gmpid = 'IOP' and tg = @gmtg and inm = @ginm";

var cmd = new NpgsqlCommand();
cmd.CommandType = (System.Data.CommandType)Convert.ToInt16(CommandType.Text);
cmd.CommandText = pstrng;

cmd.Parameters.Add("@gma_tg", NpgsqlTypes.NpgsqlDbType.Varchar).Value = tgnn;
cmd.Parameters.Add("@ginm ", NpgsqlTypes.NpgsqlDbType.Varchar).Value = inm;
cmd.Parameters.Add("@ftt", NpgsqlTypes.NpgsqlDbType.Numeric).Value = ft;
cmd.Parameters.Add("@ghl", NpgsqlTypes.NpgsqlDbType.Numeric).Value = gh_lt;
cmd.Parameters.Add("@glll", NpgsqlTypes.NpgsqlDbType.Numeric).Value = gll;
cmd.Parameters.Add("@gsd", NpgsqlTypes.NpgsqlDbType.Date).Value = ghsd;
cmd.Parameters.Add("@gav", NpgsqlTypes.NpgsqlDbType.Numeric).Value = gag;
cmd.Parameters.Add("@gst", NpgsqlTypes.NpgsqlDbType.Numeric).Value = gsd;
using (var con = new NpgsqlConnection(GlobalVariables.consql)) // (strConnString)
{
    cmd.Connection = con;
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

The error I get is System.InvalidCastException: Can't write CLR type System.String with handler type NumericHandler I’ve tried type casting, changing the initial data types of declared variables, and changing the Npgsql data types. None of it is working. Please help.

Tags:

2

Answers


  1. 0

    Shouldn’t this

    CAST('ft=@ftt' AS DECIMAL)

    be something like

    ft = CAST(@ftt AS DECIMAL)

    ?

    Login or Signup to reply.
  2. 0

    I’m hardly a Npgsql expert, but your error looks like the .NET bits can’t convert a string using a numeric converter.

    Looking at your Parameters, it looks like you’re telling the engine @ftt s should be NpgsqlDbType.Numeric, and you pass in a string (ft). Ditto for gh_lt, gll, etc. Similarly, the date gsd should likely be a native .NET DateTime, not a string.

    Beyond that, your SET syntax seems off…

    So, how about letting the adapter deal with data types (and for the love of everything that’s holy, please rename those variables to something that makes sense when you get the chance… 🙂 )

    string tgnn = "";
string inm = "";
var ft = 0;
var gh_lt = 0;
var gll = 0;
var ghsd = DateTime.Parse("1998/01/01");
var gag = 0;
var gsd = 0;
var cmd = new NpgsqlCommand(@"UPDATE gamma_header
SET
ft = @ftt,
gh_lt = @ghl,
gag = @gav,
gsd = @gst
WHERE
gmpid = 'IOP'
and tg = @gmtg
and inm = @ginm");
cmd.Parameters.AddWithValue("gma_tg", tgnn);
cmd.Parameters.AddWithValue("ginm", inm);
cmd.Parameters.AddWithValue("ftt", ft);
cmd.Parameters.AddWithValue("ghl", gh_lt);
cmd.Parameters.AddWithValue("glll", gll);
cmd.Parameters.AddWithValue("gav", gag);
cmd.Parameters.AddWithValue("gst", gsd);
cmd.Parameters.AddWithValue("gsd", ghsd);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search