npgsql executescalar() allways returns nothing

Question

I'm using npgsql as a nuget package in visual studio 2017 with visual basic. Various commands do work very well but an ExecuteScalar allways returns 'nothing' although it should give a result.

The command looks like this:

   Dim ser As Integer
   Dim find = New NpgsqlCommand("SELECT serial from dbo.foreigncode WHERE code = '@code';", conn)
   Dim fcode = New NpgsqlParameter("code", NpgsqlTypes.NpgsqlDbType.Varchar)

   find.Parameters.Add(fcode)
   find.Prepare()

   fcode.Value = "XYZ"

   ser = find.ExecuteScalar()     ==>  nothing

When the command string is copied as a value during debugging and pasted into the query tool of PGADMIN it delivers the correct result. The row is definitely there.

Different Commands executed with ExecuteNonQuery() work well, including ones performing UPDATE statements on the row in question.

When I look into the properties of the parameter fcode immediately before the ExecuteScalar it shows 'fcode.DataTypeName' caused an exception 'System.NotImplementedException'.

If I change my prepared statement to "SELECT @code" and set the value of the parameter to an arbitrary value just this value is returned. There is no access to the table taking place because the table name is not part of the SELECT in this case. If I remove the WHERE CLAUSE in the SELECT and just select one column, I would also expect that something has to be returned. But again it is nothing.

Yes there is a column named serial. It is of type bigint and can not contain NULL. A Query shows that there is no single row that contains NULL in any column.

Latest findings: I queried a different table where the search column and the result column happen to have the same datatype. It works, so syntax, passing of parameter, prepare etc. seems to work in principal. The System.NotImplementedException in the DataTypeName property of the parameter occurs as well but it works anyway. I rebuilt the index of the table in question. No change. Still: when I copy/paste the CommandText and execute it in PGAdmin it shows the correct result. Modifying the Command and using plain text there without parameter and without prepare still does yield nothing. The plain text CommandText was copy/pasted from PGAdmin where it was successfully executed before. Very strange.

Reverting search column and result column also gives nothing as a result.

Answer 1

Please try these two alternatives and post back your results:

' Alternative 1: fetch the entire row, see what's returned
Dim dr  = find.ExecuteReader()
While (dr.Read())
  Console.Write("{0}\t{1} \n", dr[0], dr[1])
End While

' Alternative 2: Check if "ExecuteScalar()" returns something other than an int
Dim result = find.ExecuteScalar()

... and (I just noticed Honeyboy Wilson's response!) ...

Fix your syntax:

' Try this first: remove the single quotes around "@code"!
Dim find = New NpgsqlCommand("SELECT serial from dbo.foreigncode WHERE code = @code;", conn)

---

Update 1

Please try this:

 Dim find = New NpgsqlCommand("SELECT * from dbo.foreigncode;", conn)

Q: Does this return anything?

Dim dr  = find.ExecuteReader()
While (dr.Read())
  Console.Write("{0}\t{1} \n", dr[0], dr[1])
End While

Q: Does this?

Dim result = find.ExecuteScalar()

Q: Do you happen to have a column named "serial"? What is it's data type? Is it non-null for the row(s) with 'XYZ'?

Please update your original post with this information.

---

Update 2

You seem to be doing ":everything right":

• You've confirmed that you can connect,
• You've confirmed that non-query updates to the same table work (with npgsql),
• You've confirmed that the SQL queries themselves are valid (by copying/pasting the same SQL into PGAdmin and getting valid results).

As Shay Rojansky said, "System.NotImplementedException in the DataTypeName property" is a known issue stepping through the debugger. It has nothing to do with your problem: https://github.com/npgsql/npgsql/issues/2520

SUGGESTIONS (I'm grasping at straws)::

1. Double-check "permissions" on your database and your table.

2. Consider installing a different version of npgsql.

3. Be sure your code is detecting any/all error returns and exceptions (it sounds like you're probably already doing this, but it never hurts to ask)

... and ...

4. Enable verbose logging, both client- and server-side:

   • https://www.npgsql.org/doc/logging.html
   • https://www.postgresql.org/docs/9.0/runtime-config-logging.html

... Finally ...

Q: Can you make ANY query, from ANY table, using ANY query method (ExecuteReader(), ExecuteScalar(), ... ANYTHING) from your npgsql/.Net client AT ALL?

Answer 2

I finally found it. It's often the small things that can have a big impact. When the value was assigned to the parameter a substring index was incorect. Now it works perfectly. Thanks to everybody who spent his time on this.