0

I am using the following code that was provided in the npgsql docs. I can retrieve data when my query string is Select * from public.accounts but this function will not return data when conditions are in the where clause.

I have seen other answers on SO that say to pass the command parameters in the AddWithValue function, but this doesn't return anything for me.

The query Select * from public.accounts where ext_auth0_user_id = 'github|42357689' DOES return data when I run it directly in pgAdmin, so I am assuming I have some formatting wrong.

        var userId = "github|42357689";
        using (var conn = new NpgsqlConnection(connString))
        {
            conn.Open();
            NpgsqlCommand cmd = new NpgsqlCommand("Select * from public.accounts where ext_auth0_user_id = @userId", conn);

            // Retrieve all rows
            using (cmd)
            {
                cmd.Parameters.AddWithValue("@userId", userId);
                using (NpgsqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        dataTable.Load(reader);
                    }
                }
            }
        }
3
  • You're comparing ext_auth0_user_id against the string '@userId', not the @userId parameter's value. Remove the quotes. Commented May 13, 2019 at 15:59
  • It may be the single quotes ('@userId'). The single quote indicate TEXT and database may have integers. Remove single quotes. Commented May 13, 2019 at 16:01
  • @madreflection unfortunately I still get no data after removing the single quotes around the param. Updated the OP Commented May 13, 2019 at 16:02

1 Answer 1

1

NpgsqlParameter paramUserId = cmd.Parameters.Add("userId", NpgsqlTypes.NpgsqlDbType.Varchar, 20);

paramUserId.Direction = ParameterDirection.Input; paramUserId.value = userId;

NpgsqlParameter paramAnotherId = cmd.Parameters.Add("anotherId", NpgsqlTypes.NpgsqlDbType.Integer);

paramAnotherId.Direction = ParameterDirection.Input; paramAnotherId.value = anotherId;

Sign up to request clarification or add additional context in comments.

4 Comments

this results in CS1503 Argument 2: cannot convert from 'string' to 'NpgsqlTypes.NpgsqlDbType'. Any way to parse the string as a NpgsqlDbType?
PgSqlParameter paramUserId = cmd.Parameters.Add("userId", PgSqlType.VarChar, 20); paramUserId.value = userId; sorry long time past im not using postgresql
@Shapka Update your answer so the correction isn't hidden among the comments. Also, OP is using Npgsql, not dotConnect.
This solution worked for a text column. However it doesn't work for character varying column, which is what I am after...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.