Problem To Edit SQL Commands Of Tableadapters

T.Winkler · Post by **T.Winkler** » Mon 13 Nov 2006 20:14

Hi there,

I need some help to edit a SQL command of a tableadapter.
Every time I edit one, I get a lot of errors about missing parameters.

I use VS 2005 and PGSQL 2.50.21

Alexey · Post by **Alexey** » Tue 14 Nov 2006 07:33

Please describe the problem in detail.

T.Winkler · Post by **T.Winkler** » Wed 15 Nov 2006 08:49

Hello Alexey

if I edit an querry, the used parameterts of the querry are not listed in the list if the parameter.
The name of the parameters are wrong.

But pleasy be so kind and simply use VS2005 and have a try to create, edit and use a tableadapter.

regards.

Thomas

Alexey · Post by **Alexey** » Wed 15 Nov 2006 12:02

Please specify the query which you want me to use in the TableAdapter.
(And provide me with table definition as well.)

T.Winkler · Post by **T.Winkler** » Thu 16 Nov 2006 11:58

Hello Alexey,

I use an existing dataset.
Then I say add tableadapter, create a new connection, add the table and finished the wizzard.

The SQL command to update the data will be now:

UPDATE "peri_ww"."peri"."anreden" SET "lfdnr_anrede" = :lfdnr_anrede, "anrede" = :anrede, "code2" = :code2, "code3" = :code3, "code4" = :code4, "code5" = :code5, "code6" = :code6, "code7" = :code7 WHERE (("lfdnr_anrede" = :Original_lfdnr_anrede) AND ((:IsNull_anrede = 1 AND "anrede" IS NULL) OR ("anrede" = :Original_anrede)) AND ((:IsNull_code2 = 1 AND "code2" IS NULL) OR ("code2" = :Original_code2)) AND ((:IsNull_code3 = 1 AND "code3" IS NULL) OR ("code3" = :Original_code3)) AND ((:IsNull_code4 = 1 AND "code4" IS NULL) OR ("code4" = :Original_code4)) AND ((:IsNull_code5 = 1 AND "code5" IS NULL) OR ("code5" = :Original_code5)) AND ((:IsNull_code6 = 1 AND "code6" IS NULL) OR ("code6" = :Original_code6)) AND ((:IsNull_code7 = 1 AND "code7" IS NULL) OR ("code7" = :Original_code7)))

The first error will be the name of the database in front of the command.
It will be work, but, if you change the connection to an other database you will get an error.

the generated list of parameter will be ok.

Now I have a need to change the querry and open the updatecommand to edit. With no changes do to I close the window. After that the updatecommand will be:

UPDATE peri.anreden
SET lfdnr_anrede = :lfdnr_anrede, anrede = :anrede, code2 = :code2, code3 = :code3, code4 = :code4, code5 = :code5, code6 = :code6,
code7 = :code7
WHERE (lfdnr_anrede = :Original_lfdnr_anrede) AND (:IsNull_anrede = 1 AND anrede IS NULL OR
anrede = :Original_anrede) AND (:IsNull_code2 = 1 AND code2 IS NULL OR
code2 = :Original_code2) AND (:IsNull_code3 = 1 AND code3 IS NULL OR
code3 = :Original_code3) AND (:IsNull_code4 = 1 AND code4 IS NULL OR
code4 = :Original_code4) AND (:IsNull_code5 = 1 AND code5 IS NULL OR
code5 = :Original_code5) AND (:IsNull_code6 = 1 AND code6 IS NULL OR
code6 = :Original_code6) AND (:IsNull_code7 = 1 AND code7 IS NULL OR
code7 = :Original_code7)

Please note now the diffrence in front of the command and please note also that I have set no changes!
But the realy problem will be the generated parameters.

anrede As String, _
code2 As String, _
code3 As String, _
code4 As String, _
code5 As String, _
code6 As String, _
code7 As String, _
Original_lfdnr_anrede As Integer, _
Original_Param10 As String, _
Original_anrede As String, _
Original_Param12 As String, _
Original_code2 As String, _
Original_Param14 As String, _
Original_code3 As String, _
Original_Param16 As String, _
Original_code4 As String, _
Original_Param18 As String, _
Original_code5 As String, _
Original_Param20 As String, _
Original_code6 As String, _
Original_Param22 As String, _
Original_code7 As String

The Parameter isnull.... are not generated but used in the command,
and the generated parameter original_param?? are not used in the command.

So I hope this information will be Ok, but if you have a need of more information please do not hesitate to contact me.

The table I use:
CREATE TABLE peri.anreden
(
lfdnr_anrede int2 NOT NULL DEFAULT 0,
anrede varchar,
code2 varchar(30),
code3 varchar(30),
code4 varchar(30),
code5 varchar(20),
code6 varchar(30),
code7 varchar(30),
CONSTRAINT pk_anreden PRIMARY KEY (lfdnr_anrede)
)
WITH OIDS;
ALTER TABLE peri.anreden OWNER TO postgres;
GRANT ALL ON TABLE peri.anreden TO postgres;
GRANT ALL ON TABLE peri.anreden TO public;

Alexey · Post by **Alexey** » Thu 16 Nov 2006 15:52

I can't reproduce any query changes. The update command is correct from the very beginning. Parameters collection is correct as well.

T.Winkler · Post by **T.Winkler** » Sun 19 Nov 2006 11:18

OK, I buy a new notebook

install windows postgres pgsql direkt and visual studio2005.
and have the same problem. Any suggestion?

Alexey · Post by **Alexey** » Mon 20 Nov 2006 07:59

Try to reinstall PostgreSQLDirect .NET.
Make sure that PostgreSQLDirect .NET isn't installed and there are no
CoreLab.PostgreSql.dll, CoreLab.PostgreSql.Addin.dll, CoreLab.PostgreSql.Design.dll in GAC.

T.Winkler · Post by **T.Winkler** » Fri 24 Nov 2006 10:43

Hello Alexey,

nothing will help, but I see, that a lot of people does have the same problem like me.
Will be see here a solution?

Greeting
Thomas

Alexey · Post by **Alexey** » Fri 24 Nov 2006 10:58

a lot of people does have the same problem like me

I haven't seen people complaining about spontaneous query changes. Please provide me with the links.

T.Winkler · Post by **T.Winkler** » Fri 24 Nov 2006 13:02

Hello Alexey,

have a look to:
http://www.crlab.com/forums/viewtopic.php?t=7991
I know everything he says.
Please be so kind and check the insert or update command.
In some of the older versions of PGSQL the parameters are beginning with a "@" but in version 21 it will be created with ":"

But anyway, if I change the parameter description by default ( menu extras / options / database tools / parameter prefix ) from ":" to "@" I have the same problems and I get a lot of errors when I try to edit an command which will be generated by PGSQL, because there are ":" in front of the parameter name, which will be not see as an parameter!

Also I know the problems that the wizard will be not create the update command. If I create a connection and save it to use this connection for all new commands, he does not generate an update command.
But if I say the wizard: Create new connection, the Update command will be creating. Have a look to :
http://www.crlab.com/forums/viewtopic.php?t=6741

So what I do, is to look how to define the connection. And there will be a lot of different things. So I think the connection will be the problem.

Hope to give you some hints.
Regards

Thomas

Alexey · Post by **Alexey** » Tue 28 Nov 2006 14:38

The problem appears to be complicated, and finding a solution may take a lot of time.