Parameterized query returns one row with null values.

I am hoping someone could help me understand why this is happening and perhaps a solution.

I am using ASP.NET 2.0 with a SQL 2005 database.

In code behind, I am performing a query using a parameter as below:

sql = "SELECT field_name FROM myTable WHERE (field_name = @.P1)"

objCommand.Parameters.Add(New SqlParameter("@.P1", TextBox1.Text))

The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.

The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:

sql =String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)

I also get NO records retuned using the same criteria.

What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?

Thanks!

I am not sure but see if adding the datatype helps:

objCommand.Parameters.Add(New SqlParameter("@.P1", SqlDbType.Varchar,30)).value = TextBox1.Text

|||

Thanks for the suggestion. I tried adding the data type as you suggested. It did not change the results.

I have found that if I change to a data reader, the null value is not being returned. So, now it looks to be related to the ExecuteScalar method.

|||

I also just realized that it is not a null value being returned but instead an empty value, ie "".

I can get around this easily enough in multiple ways, I am just wanting to understand why this is happening.

So far I have this narrowed down to the following:

A parameterized query, with a user account having select, insert, update permission, and using the ExecuteScalar method. This combination returns a record with an empty result when the criteria is not found in the table instead of returning no records at all.

|||

Eh?

ExecuteScalar is used to return the first column of the first row of the query. If there is no rows, the value comes back as null.

I think perhaps you are misunderstanding what ExecuteScalar is supposed to do. It doesn't return records, or recordsets, it returns a singular scalar value (One column of one row - the first of each).

For further help, please post the whole code block in question. How you initialize your connection, command objects, how you are actually executing the query, where you are storing the result of the query (And how it is defined), and what you expected the result to be, and what you actually got.

If the results are varying depending on what user is executing the query, please make sure that either you explicitly define the schema you want to use, or that there doesn't exist multiple tables with the same name under different schemas (Refer to the table as dbo.Table not just Table).

|||

Ok, my bad, stupid mistake(s) with both user permissions and also with the string.format method.

I at least have it consistenly returning the empty record.

One last question, why return null/empty instead of just nothing like a data reader?

Thank you very much for the response.

|||

Hi,

ExecuteScalar is designed to return a single value from a database command and the proper representation of a single non-existant value is returning null. The ExecuteScalar is a non-void method and should return something!

Enjoy C#,

Mehrdad

|||

Thank you to everyone for the help and clairification on ExecuteScalar.