Devart Forums

Posted: **Tue 04 Aug 2015 15:58**

I'm trying to change the password of a login role ('test'):

            using (var command = new PgSqlCommand("alter role @user test with password @pw", MyConnection))
            {
                command.Parameters.AddWithValue("@user", user);
                command.Parameters.AddWithValue("@pw", pw);
                result = (command.ExecuteNonQuery() == -1);
            }

But this fails with: syntax error at or near \"$1\"

I tried hardcoding the password (i.e. i.e. alter role @user test with 'test') and this fails too. It works fully hardcoded (i.e. alter role test with 'test').

c#, VS2013, latest dotConnect

Posted: **Wed 05 Aug 2015 14:57**

You can use parameters in static SQL statements only in positions where the SQL syntax allows a parameter. For example, you cannot specify table names with parameters.

Therefore, parameters can send only query values, not the values of its part.

Parameters cannot be used for building query as a query part. The alter role query cannot be used with parameters, since parameters for a given query are not applicable.

As a workaround, you can use the following code:

Code: Select all

using (var command = new PgSqlCommand("alter role "+user+" with password "+pw+";", MyConnection)) {
	bool result = (command.ExecuteNonQuery() == -1);
}

Posted: **Wed 05 Aug 2015 15:26**

Hi, thanks for the reply. The reason I was using parameters for the password is because it may have characters in it that cause a problem in the command string (", /, '). I now understand the role is not a parameter but is the password not a parameter?

Posted: **Thu 06 Aug 2015 10:44**

damon.cognito wrote:I now understand the role is not a parameter but is the password not a parameter?

The password is not a parameter too.

Devart Forums

command parameters

command parameters

Re: command parameters

Re: command parameters

Re: command parameters