SqlDataReader issues

Question

The code performs as needed when done like this.

SqlDataReader sqlRead = selectCommand.ExecuteReader(CommandBehavior.SingleRow);

if (sqlRead.Read())
{
    string text = sqlRead["USERNAME"].ToString();

    MessageBox.Show(text);
    connection.Close();
}

Whenever written without the if and just the variable declaration like this

string text = sqlRead["USERNAME"].ToString();

MessageBox.Show(text);
connection.Close();

I get this error:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: Invalid attempt to read when no data is present.

Why can't I simply assign the value to the variable? Is it because nothing was actually read into the reader?

The Read call is mandatory. Otherwise your reader has not read any record. The call positions the reader on the first record (if any) and return true, otherwise return false and you know that there are no more record to read — Steve
– Steve, Commented Dec 10, 2017 at 9:01
sqlRead.Read() fetches the next record from cursor and return true on success, if you don't have any record fetched (data present) you can't read it — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Dec 10, 2017 at 9:01

Zohar Peled · Accepted Answer · 2017-12-10 09:01:20Z

That's just how DataReader is written. You must first call the Read method before you can get values from it.
See Retrieving Data Using a DataReader Microsoft docs:

You use the Read method of the DataReader object to obtain a row from the results of the query. You can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader.

However, in your case, I would recommend not to use ExecuteReader() in the first place. Since you are only interested in one value, you should use ExecuteScalar instead, and make sure your SQL statement actually selects the value you want (if, for instance, you are selecting more than one column or more than one row, the ExecuteScalar will return the value of the first column in the first row, ignoring all other values that might be returned from the SQL statement).

var text = "";
var userName = selectCommand.ExecuteScalar();
if(userName != null && userName != DbNull.Value)
{
    text = userName.ToString();
}

Collectives™ on Stack Overflow

SqlDataReader issues

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related