"could not determine data type of parameter"

thomas840 · Post by **thomas840** » 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!

Alexey · Post by **Alexey** » Tue 16 May 2006 06:11

Please provide us with the definition of your database objects (including small set of data as well).

thomas840 · Post by **thomas840** » Tue 16 May 2006 11:06

Worked (unsuccessfully) on this problem all day. I did discover the very important .SourceColumn property of Command.Property, but, alas, setting that correctly was not the solution.

For testing purposes, I did some simplifying. The following data was used to generate this exception:

Message "could not determine data type of parameter $1" String
Source "System.Data" String
StackTrace " at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at CoreLab.Common.DbTable.Update()
at CoreLab.PostgreSql.PgSqlDataTable.Update()
at HHIS.HHIS.DataReview.SaveTable(PgSqlDataTable MyTable) in C:\Documents and Settings\tom\My Documents\SharpDevelop Projects\HHIS\DataReview.vb:line 1573"

thomas840 · Post by **thomas840** » Tue 16 May 2006 11:08

Continuing ...

--
-- PostgreSQL database dump
--

-- Started on 2006-05-16 17:09:50 SE Asia Standard Time

SET client_encoding = 'UNICODE';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

--
-- TOC entry 1971 (class 0 OID 0)
-- Dependencies: 1584
-- Name: batch_cuts_cuts_cuts_key_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('batch_cuts_cuts', 'cuts_key'), 1, true);

--
-- TOC entry 1969 (class 0 OID 28598)
-- Dependencies: 1585
-- Data for Name: batch_cuts_cuts; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY batch_cuts_cuts (cuts_key, cuts_injm_key, cuts_injd_key, cuts_pick_key_object, cuts_pick_key_activity, cuts_pick_key_objectstorage, cuts_object_other, cuts_objectstorage_other) FROM stdin;
1 36 \N 946 947 958 razor my hump
\.

-- Completed on 2006-05-16 17:09:51 SE Asia Standard Time

--
-- PostgreSQL database dump complete
--

That should do it...

Alexey · Post by **Alexey** » Tue 16 May 2006 12:29

Executing your script raises the
ERROR: relation "inmi_inmigration" does not exist
Please advice. Have you missed something?
Send your test VS project as well.

thomas840 · Post by **thomas840** » Thu 18 May 2006 08:08

Alexy and Corelab,

I have resolved my error. The problem is that I had defined more parametrs than I was using. Apparently every parameter attached to an Update Statement must be referenced in the UpdateCommand.CommandText. I only tested this with pgsqlDataTable, so I don't know if it is more widespread (i.e. the regular datatable and datatables for other providers); whether this is a bug or by design.

Secondly, what I think may be a bug is that when your column names in the pgsqldatatable includes a ".", i.e. "tablename.columnname", the code that generates the update sql automatically appears to be unable to parse past the ".", leading to errors. Again, since I haven't done further testing I can't say if this is particular to the pgsqldatatable or is endemic to other commandbuilders.

Anyway, thanks for your help and your great product.