Error : Parameter name not found, in Postgresql

Question

I am working in Asp.net with postgresql with devart dotconnect connection driver. When i am executing a parmeterized query it is showing error,

Parameter name not found

My code is below

Command = new Devart.Data.PostgreSql.PgSqlCommand();
Command.Parameters.AddWithValue("@TestName", testmaster.TestName);
Command.Parameters.AddWithValue("@TestShortName", testmaster.TestShortName);
DAF.Command.CommandText = @"Do $$
DECLARE newTestID INT;
BEGIN
"+"Insert into \"TestMaster\"(\"TestName\", \"TestShortName\", \"RCUID\", \"RCDate\", \"RCTime\") " + @"
    "+"values (:TestName, :TestShortName, 0, CURRENT_DATE, CURRENT_TIME(0)) " + @"
    "+"RETURNING \"TestID\" INTO newTestID;"+@"
END$$;";

// Rest is execution code

What i am making a mistake here ?

Edit : I got on which code this message is occurring, it is not on insert command it is on returning command.

Are you sure that's the *entire* error message? Please copy and paste the full error/exception. — Craig Ringer, Jul 26 '14 at 07:45
Check my edit, the error was not on insert command it is on returning command — Haider Ali Wajihi, Jul 26 '14 at 08:51
Exactly what line does the exception get thrown from? Is it in the "execution code"? — Craig Ringer, Jul 26 '14 at 09:32
It can't be - for a number of reasons. That's not an error PostgreSQL produces; `RETURNING` isn't a statement, it's just part of the `INSERT`; and most importantly because that's **just a string**. You don't run it. **Show the full exception stack trace please**, and line numbers that match up with the exception. The code you show *cannot* produce that error without the "rest is execution code" bits you've left out. — Craig Ringer, Jul 26 '14 at 09:55

Craig Ringer · Accepted Answer · 2014-07-26T10:24:22.097

Parameter name not found must be an error from the database driver. It's hard to be sure without the full exception trace, but at a guess I think that DevArt must require you to set the SQL text before attempting to set parameters.

e.g.

Command = new Devart.Data.PostgreSql.PgSqlCommand();
Command.CommandText = "SELECT ...."; /* or whatever your SQL is */
Command.Parameters.AddWithValue("@TestName", testmaster.TestName);
Command.Parameters.AddWithValue("@TestShortName", testmaster.TestShortName);

(I haven't used it, I'm just going by the examples in some of the docs, which while they refer to Oracle parameters seem much the same for the basics.)

However, it's also possible that DevArt can't parse the DO block and $$ quoting. To confirm that, try:

Command = new Devart.Data.PostgreSql.PgSqlCommand();
Command.Parameters.AddWithValue("@TestName", testmaster.TestName);
Command.Parameters.AddWithValue("@TestShortName", testmaster.TestShortName);
Command.CommandText = "Insert into \"TestMaster\"(\"TestName\", \"TestShortName\", \"RCUID\", \"RCDate\", \"RCTime\") values (:TestName, :TestShortName, 0, CURRENT_DATE, CURRENT_TIME(0)) RETURNING \"TestID\" INTO newTestID;";

If this works, but the version in the DO block doesn't, then it's probably because DevArt can't parse the DO block or its $$ quoting to find parameters.

You might need to use an SQL function instead (assuming you're trying to do more after the first statement, otherwise there'd be no point using a DO block at all). Then call the function in a separate statement. e.g. first execute this SQL without any parameters:

CREATE OR REPLACE FUNCTION myfunc(testname text, testshortname text) returns void AS $$
DECLARE
    newTestID INT;
BEGIN
    INSERT into "TestMaster"("TestName", "TestShortName", "RCUID", "RCDate", "RCTime")
    VALUES (testname, testshortname, 0, CURRENT_DATE, CURRENT_TIME(0))
    RETURNING TestID INTO newTestID;

    -- whatever you want to do with newTestID here
END$$;

then run another statement to call the function, like:

Command = new Devart.Data.PostgreSql.PgSqlCommand();
Command.CommandText = "SELECT myfunc(:TestName, :TestShortName)";
Command.Parameters.AddWithValue("@TestName", testmaster.TestName);
Command.Parameters.AddWithValue("@TestShortName", testmaster.TestShortName);

to call it.

There's no CREATE TEMPORARY FUNCTION so you'll have to DROP FUNCTION afterwards if you don't want to keep and re-use it.

Yes, i got it the parser, does not pars do block, so that error occurring. Now i am going to try function — Haider Ali Wajihi, Jul 26 '14 at 10:23
can you help me how to use out parameter in function and calling it from C# code — Haider Ali Wajihi, Jul 26 '14 at 10:51
@HaiderAliWajihi If you have something new, *post a new question* please. This time please read http://stackoverflow.com/tour and http://stackoverflow.com/help/how-to-ask first, so you can provide a clearer question that requires less guesswork. — Craig Ringer, Jul 26 '14 at 11:32

Error : Parameter name not found, in Postgresql

1 Answers1