"could not determine data type of parameter"
Posted: Mon 15 May 2006 11:04
Hi
I am running PostGresSQLDirect 2.40.14, PostGresSQL 8.04, and SharpDevelop 1.1.0 (VB).
I have a PgSQLDataTable that is filled from an SQL View. This View contains columns from different tables but with the same column name. To differentiate the columns from the different tables I've included the tablename in the columnname in the View, i.e.:
TableA:
Column: pick_key
TableB:
Column: pick_key
Create MyView As
Select TableA.pick_key as "TableA.pick_key", TableB.pick_key as "TableB.pick_key" from TableA JOIN TableB ON [join clause]
This works fine so far. I can go into pgAdmin and run an Update statement in their SQL utility and the view and underlying tables all update correctly.
In VB I then attempted to let the pgSqlDataTable generate my Update Statement. The initial select statement is just "Select [columnlist] from MyView". But when I attempted to update, I received an error... it's been a few days at this point, but something about not being able to find the parameter "TableA". It seemed be getting hung up on the "." in my column name.
So then I manually created an update command for my pgSqlDataTable, eliminating the "." from the parameter names. The commandtext reads
UPDATE MyView
SET "TableA.pick_key" = CAST(:TableApick_key AS [datatype]),
"TableB.pick_key" = CAST(:TableB(:TableBpick_key AS [datatype])
WHERE "TableA.pick_key" = CAST(:oldTableApick_key AS [datatype])
AND "TableB.pick_key" = CAST(:oldTableApick_key AS [datatype])
I add parameters to the UpdateCommand like so:
pgSqlDataTable.UpdateCommand.parameters.Add(TableApick_key, [pgSqlDataType]).SourceVersion = DataRowVersion.Current
pgSqlDataTable.UpdateCommand.parameters.Add(oldTableApick_key, [pgSqlDataType]).SourceVersion = DataRowVersion.Original
If the pgsqldatatype = varchar, I add a third parameter to the "Add" method for the string length. I always use 100 in this case because that is the maximum size for all my varchar columns; that is, some columns may only be varchar(20), but I nonetheless define the Command.Parameter as Varchar(100).
This is simplified; my actual CommandText has about 30 SET statements and about 10 WHERE clauses; the UPDATE columns and WHERE columns are mutually exclusive. The "CAST" functions included above is my (unsuccessful) attempt at solving this problem: when I make a change to the pgSQLDataTable and run the Update method, I receive the following error:
Corelab.PostgreSql.PgSqlException: could not determine the data type of parameter $2
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at Corelab.PostgreSql.PgSqlDataTable.Update()
etc...
Please help!
I am running PostGresSQLDirect 2.40.14, PostGresSQL 8.04, and SharpDevelop 1.1.0 (VB).
I have a PgSQLDataTable that is filled from an SQL View. This View contains columns from different tables but with the same column name. To differentiate the columns from the different tables I've included the tablename in the columnname in the View, i.e.:
TableA:
Column: pick_key
TableB:
Column: pick_key
Create MyView As
Select TableA.pick_key as "TableA.pick_key", TableB.pick_key as "TableB.pick_key" from TableA JOIN TableB ON [join clause]
This works fine so far. I can go into pgAdmin and run an Update statement in their SQL utility and the view and underlying tables all update correctly.
In VB I then attempted to let the pgSqlDataTable generate my Update Statement. The initial select statement is just "Select [columnlist] from MyView". But when I attempted to update, I received an error... it's been a few days at this point, but something about not being able to find the parameter "TableA". It seemed be getting hung up on the "." in my column name.
So then I manually created an update command for my pgSqlDataTable, eliminating the "." from the parameter names. The commandtext reads
UPDATE MyView
SET "TableA.pick_key" = CAST(:TableApick_key AS [datatype]),
"TableB.pick_key" = CAST(:TableB(:TableBpick_key AS [datatype])
WHERE "TableA.pick_key" = CAST(:oldTableApick_key AS [datatype])
AND "TableB.pick_key" = CAST(:oldTableApick_key AS [datatype])
I add parameters to the UpdateCommand like so:
pgSqlDataTable.UpdateCommand.parameters.Add(TableApick_key, [pgSqlDataType]).SourceVersion = DataRowVersion.Current
pgSqlDataTable.UpdateCommand.parameters.Add(oldTableApick_key, [pgSqlDataType]).SourceVersion = DataRowVersion.Original
If the pgsqldatatype = varchar, I add a third parameter to the "Add" method for the string length. I always use 100 in this case because that is the maximum size for all my varchar columns; that is, some columns may only be varchar(20), but I nonetheless define the Command.Parameter as Varchar(100).
This is simplified; my actual CommandText has about 30 SET statements and about 10 WHERE clauses; the UPDATE columns and WHERE columns are mutually exclusive. The "CAST" functions included above is my (unsuccessful) attempt at solving this problem: when I make a change to the pgSQLDataTable and run the Update method, I receive the following error:
Corelab.PostgreSql.PgSqlException: could not determine the data type of parameter $2
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at Corelab.PostgreSql.PgSqlDataTable.Update()
etc...
Please help!