Problem To Edit SQL Commands Of Tableadapters
Problem To Edit SQL Commands Of Tableadapters
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
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
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;
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;
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
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