Invalid attempt to read when no data is present

[Andy Sutorius]

Hi,

I am getting the error "Invalid attempt to read when no data is present"
when I run the following code. I have checked the sql and it is returning
data. What am I missing?

string sqlGetEmail = "SELECT content, subject FROM tblContent WHERE id =
" + Convert.ToInt16(ddlChooseEmail.SelectedValue.ToString());
SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);
SqlDataReader dtrEmail = cmdGetEmail.ExecuteReader();

string strSubject = Convert.ToString(dtrEmail["subject"]);
string strContent = Convert.ToString(dtrEmail["content"]);

dtrEmail.Close();


Thanks,

Andy

 

[Kevin Spencer]

string strSubject = Convert.ToString(dtrEmail["subject"]);

string strContent = Convert.ToString(dtrEmail["content"]);

Are you checking for NULL values before attempting to convert them to
strings? A NULL cannot be converted to a string. Example:

string strSubject;
int ordinal;
ordinal = dtrEmail.GetOrdinal("subject");
if (!dtrEmail.IsDbNull(ordinal)
strSubject = dtrEmail.GetString(ordinal);
else
strSubject = "";

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

Hi,

I am getting the error "Invalid attempt to read when no data is present"
when I run the following code. I have checked the sql and it is returning
data. What am I missing?

string sqlGetEmail = "SELECT content, subject FROM tblContent WHERE id =
" + Convert.ToInt16(ddlChooseEmail.SelectedValue.ToString());
SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);
SqlDataReader dtrEmail = cmdGetEmail.ExecuteReader();

string strSubject = Convert.ToString(dtrEmail["subject"]);
string strContent = Convert.ToString(dtrEmail["content"]);

dtrEmail.Close();


Thanks,

Andy

 

[Andy Sutorius]

Kevin,

When I placed your snippet in my code I decided to step through in debug. As
soon as I F11 off of the if statement I get the same error. It's failing
when testing for null. Any recommendations?

Thanks,

Andy

string strSubject = Convert.ToString(dtrEmail["subject"]);
string strContent = Convert.ToString(dtrEmail["content"]);

Are you checking for NULL values before attempting to convert them to
strings? A NULL cannot be converted to a string. Example:

string strSubject;
int ordinal;
ordinal = dtrEmail.GetOrdinal("subject");
if (!dtrEmail.IsDbNull(ordinal)
strSubject = dtrEmail.GetString(ordinal);
else
strSubject = "";

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

Hi,

I am getting the error "Invalid attempt to read when no data is present"
when I run the following code. I have checked the sql and it is returning
data. What am I missing?

string sqlGetEmail = "SELECT content, subject FROM tblContent WHERE id =
" + Convert.ToInt16(ddlChooseEmail.SelectedValue.ToString());
SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);
SqlDataReader dtrEmail = cmdGetEmail.ExecuteReader();

string strSubject = Convert.ToString(dtrEmail["subject"]);
string strContent = Convert.ToString(dtrEmail["content"]);

dtrEmail.Close();


Thanks,

Andy

 

[Marina]

You need to call the Read method on the SqlDataReader before trying to get
data from it. And if the Read method returns False, no rows were returned.

 

[Kevin Spencer]

Hi Andy,

Odd that you can debug with the code you posted. After reading your reply I
looked more closely (sorry I didn't in the first place) and noticed an error
that should have thrown an exception:

SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);

This line should throw an exception because there is no constructor for a
SqlDataReader that takes 2 strings as parameters. There are 4 overloads for
the constructor:

public SqlCommand() // parameterless
public SqlCommand(string) // 1 string parameter
(query)
public SqlCommand(string, SqlConnection) // 1 string parameter (query), 1
SqlConnection parameter
public SqlCommand(string, SqlConnection, SqlTransaction) // see above, plus
1 SqlTransaction

In any case, without a Connection, there is no data.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

Kevin,

When I placed your snippet in my code I decided to step through in debug.
As
soon as I F11 off of the if statement I get the same error. It's failing
when testing for null. Any recommendations?

Thanks,

Andy

string strSubject = Convert.ToString(dtrEmail["subject"]);
string strContent = Convert.ToString(dtrEmail["content"]);

Are you checking for NULL values before attempting to convert them to
strings? A NULL cannot be converted to a string. Example:

string strSubject;
int ordinal;
ordinal = dtrEmail.GetOrdinal("subject");
if (!dtrEmail.IsDbNull(ordinal)
strSubject = dtrEmail.GetString(ordinal);
else
strSubject = "";

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

Hi,

I am getting the error "Invalid attempt to read when no data is
present"
when I run the following code. I have checked the sql and it is returning
data. What am I missing?

string sqlGetEmail = "SELECT content, subject FROM tblContent WHERE
id =
" + Convert.ToInt16(ddlChooseEmail.SelectedValue.ToString());
SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);
SqlDataReader dtrEmail = cmdGetEmail.ExecuteReader();

string strSubject = Convert.ToString(dtrEmail["subject"]);
string strContent = Convert.ToString(dtrEmail["content"]);

dtrEmail.Close();


Thanks,

Andy

 

[Andy Sutorius]

Kevin,

I have the connection string in the Public Class and the open in the
Page_Load. I also have 2 functions that populate drop downs with data and
they do not error out. So it is something specific to the data reader in my
third function.


SqlConnection strConnString = new
SqlConnection(ConfigurationSettings.AppSettings["conString"]);

private void Page_Load(object sender, System.EventArgs e)
{
strConnString.Open();

if (!IsPostBack)
{
BindEmailDropDown();
BindGroupDropDown();
}


}

 

[Kevin Spencer]

Ah, well, that's bad use of Hungarian notation in that case. "str" as a
prefix almost always means "string."

Marina is correct. You need to call Read() before there will be any data in
the SqlDataReader. The SqlDataReader starts out with no records. Each call
to Read() loads the next record in it (It only contains one record at a
time). The Read() method returns false if there is no next record, true if
it fetched one.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

Kevin,

I have the connection string in the Public Class and the open in the
Page_Load. I also have 2 functions that populate drop downs with data and
they do not error out. So it is something specific to the data reader in
my
third function.


SqlConnection strConnString = new
SqlConnection(ConfigurationSettings.AppSettings["conString"]);

private void Page_Load(object sender, System.EventArgs e)
{
strConnString.Open();

if (!IsPostBack)
{
BindEmailDropDown();
BindGroupDropDown();
}


}

Hi Andy,

Odd that you can debug with the code you posted. After reading your reply I
looked more closely (sorry I didn't in the first place) and noticed an error
that should have thrown an exception:

SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);

This line should throw an exception because there is no constructor for a
SqlDataReader that takes 2 strings as parameters. There are 4 overloads for
the constructor:

public SqlCommand() // parameterless
public SqlCommand(string) // 1 string
parameter
(query)
public SqlCommand(string, SqlConnection) // 1 string parameter
(query), 1
SqlConnection parameter
public SqlCommand(string, SqlConnection, SqlTransaction) // see above, plus
1 SqlTransaction

In any case, without a Connection, there is no data.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

 

[Andy Sutorius]

Can you explain something to me. I have 2 functions with datareaders that
populate dropdowns on page load. I don't have a Read() function on them. Why
don't I receive an error?



private void Page_Load(object sender, System.EventArgs e)
{
strConnString.Open();

if (!IsPostBack)
{
BindEmailDropDown();
BindGroupDropDown();
}


}

private void BindGroupDropDown()
{
string sqlSelectGroup = "select id, [group] from tblGroups";
SqlCommand cmdSelect = new SqlCommand( sqlSelectGroup, strConnString );
SqlDataReader dtrGroup = cmdSelect.ExecuteReader();

ddlChooseGroup.DataSource = dtrGroup;
ddlChooseGroup.DataValueField = "id";
ddlChooseGroup.DataTextField = "group";
ddlChooseGroup.DataBind();

dtrGroup.Close();
}

private void BindEmailDropDown()
{
string sqlSelectEmail = "select id, content, subject from tblContent";
SqlCommand cmdSelect = new SqlCommand( sqlSelectEmail, strConnString );
SqlDataReader dtrEmail = cmdSelect.ExecuteReader();

ddlChooseEmail.DataSource = dtrEmail;
ddlChooseEmail.DataValueField = "id";
ddlChooseEmail.DataTextField = "subject";
ddlChooseEmail.DataBind();

dtrEmail.Close();
}

 

[Marina]

Because you are using the reader as a datasource, and the object using the
datasource deals with getting data out of it. This is very different then
trying to access a specific pieces of data in the result set.

Can you explain something to me. I have 2 functions with datareaders that
populate dropdowns on page load. I don't have a Read() function on them.
Why
don't I receive an error?



private void Page_Load(object sender, System.EventArgs e)
{
strConnString.Open();

if (!IsPostBack)
{
BindEmailDropDown();
BindGroupDropDown();
}


}

private void BindGroupDropDown()
{
string sqlSelectGroup = "select id, [group] from tblGroups";
SqlCommand cmdSelect = new SqlCommand( sqlSelectGroup, strConnString );
SqlDataReader dtrGroup = cmdSelect.ExecuteReader();

ddlChooseGroup.DataSource = dtrGroup;
ddlChooseGroup.DataValueField = "id";
ddlChooseGroup.DataTextField = "group";
ddlChooseGroup.DataBind();

dtrGroup.Close();
}

private void BindEmailDropDown()
{
string sqlSelectEmail = "select id, content, subject from tblContent";
SqlCommand cmdSelect = new SqlCommand( sqlSelectEmail, strConnString );
SqlDataReader dtrEmail = cmdSelect.ExecuteReader();

ddlChooseEmail.DataSource = dtrEmail;
ddlChooseEmail.DataValueField = "id";
ddlChooseEmail.DataTextField = "subject";
ddlChooseEmail.DataBind();

dtrEmail.Close();
}

Ah, well, that's bad use of Hungarian notation in that case. "str" as a
prefix almost always means "string."

Marina is correct. You need to call Read() before there will be any data in
the SqlDataReader. The SqlDataReader starts out with no records. Each
call
to Read() loads the next record in it (It only contains one record at a
time). The Read() method returns false if there is no next record, true
if
it fetched one.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

 

[Andy Sutorius]

Well that makes sense. Something that still does not make sense to me is the
fact that I am still receiving the same error. I have moved some code around
so one of the datareaders is nested inside the other thinking this would get
rid of the error. During debug I receive the error when I step off of this
line: SqlCommand cmdSelect = new SqlCommand( sqlRecipients, strConnString );


private void EmailEm()
{
string sqlGetEmail = "SELECT content, subject FROM tblContent WHERE id =
" + Convert.ToInt16(ddlChooseEmail.SelectedValue.ToString());
SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);
SqlDataReader dtrEmail = cmdGetEmail.ExecuteReader();

while (dtrEmail.Read())
{
string sqlRecipients = "SELECT tblRecipients.Email AS Email FROM tblGR
INNER JOIN tblRecipients ON tblGR.RecipientID = tblRecipients.ID WHERE
tblGR.GroupID=" + Convert.ToInt16(ddlChooseGroup.SelectedValue.ToString());
SqlCommand cmdSelect = new SqlCommand( sqlRecipients, strConnString );
SqlDataReader dtrRecipient = cmdSelect.ExecuteReader();

MailMessage objMailMessage;

while (dtrRecipient.Read())
{
// Create the Mail Message
objMailMessage = new MailMessage();
objMailMessage.From = "";
//objMailMessage.To = Convert.ToString(dtrRecipient["Email"]);
objMailMessage.To = "";
objMailMessage.Subject = Convert.ToString(dtrEmail["subject"]);
objMailMessage.Body = Convert.ToString(dtrEmail["content"]) + " " +
Convert.ToString(dtrRecipient["Email"]);

// Send the Mail Message
SmtpMail.Send( objMailMessage );
}
dtrRecipient.Close();
}
dtrEmail.Close();

}

 

[Kevin Spencer]

The DataBinding does the reading in those cases.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

Can you explain something to me. I have 2 functions with datareaders that
populate dropdowns on page load. I don't have a Read() function on them.
Why
don't I receive an error?



private void Page_Load(object sender, System.EventArgs e)
{
strConnString.Open();

if (!IsPostBack)
{
BindEmailDropDown();
BindGroupDropDown();
}


}

private void BindGroupDropDown()
{
string sqlSelectGroup = "select id, [group] from tblGroups";
SqlCommand cmdSelect = new SqlCommand( sqlSelectGroup, strConnString );
SqlDataReader dtrGroup = cmdSelect.ExecuteReader();

ddlChooseGroup.DataSource = dtrGroup;
ddlChooseGroup.DataValueField = "id";
ddlChooseGroup.DataTextField = "group";
ddlChooseGroup.DataBind();

dtrGroup.Close();
}

private void BindEmailDropDown()
{
string sqlSelectEmail = "select id, content, subject from tblContent";
SqlCommand cmdSelect = new SqlCommand( sqlSelectEmail, strConnString );
SqlDataReader dtrEmail = cmdSelect.ExecuteReader();

ddlChooseEmail.DataSource = dtrEmail;
ddlChooseEmail.DataValueField = "id";
ddlChooseEmail.DataTextField = "subject";
ddlChooseEmail.DataBind();

dtrEmail.Close();
}

Ah, well, that's bad use of Hungarian notation in that case. "str" as a
prefix almost always means "string."

Marina is correct. You need to call Read() before there will be any data in
the SqlDataReader. The SqlDataReader starts out with no records. Each
call
to Read() loads the next record in it (It only contains one record at a
time). The Read() method returns false if there is no next record, true
if
it fetched one.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.

 

[Andy Sutorius]

Well that makes sense. Something that still does not make sense to me is the
fact that I am still receiving the same error. I have moved some code around
so one of the datareaders is nested inside the other thinking this would get
rid of the error. During debug I receive the error when I step off of this
line: SqlCommand cmdSelect = new SqlCommand( sqlRecipients, strConnString );


private void EmailEm()
{
string sqlGetEmail = "SELECT content, subject FROM tblContent WHERE id =
" + Convert.ToInt16(ddlChooseEmail.SelectedValue.ToString());
SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);
SqlDataReader dtrEmail = cmdGetEmail.ExecuteReader();

while (dtrEmail.Read())
{
string sqlRecipients = "SELECT tblRecipients.Email AS Email FROM tblGR
INNER JOIN tblRecipients ON tblGR.RecipientID = tblRecipients.ID WHERE
tblGR.GroupID=" + Convert.ToInt16(ddlChooseGroup.SelectedValue.ToString());
SqlCommand cmdSelect = new SqlCommand( sqlRecipients, strConnString );
SqlDataReader dtrRecipient = cmdSelect.ExecuteReader();

MailMessage objMailMessage;

while (dtrRecipient.Read())
{
// Create the Mail Message
objMailMessage = new MailMessage();
objMailMessage.From = "";
//objMailMessage.To = Convert.ToString(dtrRecipient["Email"]);
objMailMessage.To = "";
objMailMessage.Subject = Convert.ToString(dtrEmail["subject"]);
objMailMessage.Body = Convert.ToString(dtrEmail["content"]) + " " +
Convert.ToString(dtrRecipient["Email"]);

// Send the Mail Message
SmtpMail.Send( objMailMessage );
}
dtrRecipient.Close();
}
dtrEmail.Close();

}

 

[Kevin Spencer]

If I recall correctly, you're only using one Connection. You can't open 2
DataReaders using the same Connection.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

Well that makes sense. Something that still does not make sense to me is
the
fact that I am still receiving the same error. I have moved some code
around
so one of the datareaders is nested inside the other thinking this would
get
rid of the error. During debug I receive the error when I step off of this
line: SqlCommand cmdSelect = new SqlCommand( sqlRecipients,
strConnString );


private void EmailEm()
{
string sqlGetEmail = "SELECT content, subject FROM tblContent WHERE id =
" + Convert.ToInt16(ddlChooseEmail.SelectedValue.ToString());
SqlCommand cmdGetEmail = new SqlCommand(sqlGetEmail, strConnString);
SqlDataReader dtrEmail = cmdGetEmail.ExecuteReader();

while (dtrEmail.Read())
{
string sqlRecipients = "SELECT tblRecipients.Email AS Email FROM tblGR
INNER JOIN tblRecipients ON tblGR.RecipientID = tblRecipients.ID WHERE
tblGR.GroupID=" +
Convert.ToInt16(ddlChooseGroup.SelectedValue.ToString());
SqlCommand cmdSelect = new SqlCommand( sqlRecipients, strConnString );
SqlDataReader dtrRecipient = cmdSelect.ExecuteReader();

MailMessage objMailMessage;

while (dtrRecipient.Read())
{
// Create the Mail Message
objMailMessage = new MailMessage();
objMailMessage.From = "";
//objMailMessage.To = Convert.ToString(dtrRecipient["Email"]);
objMailMessage.To = "";
objMailMessage.Subject = Convert.ToString(dtrEmail["subject"]);
objMailMessage.Body = Convert.ToString(dtrEmail["content"]) + " " +
Convert.ToString(dtrRecipient["Email"]);

// Send the Mail Message
SmtpMail.Send( objMailMessage );
}
dtrRecipient.Close();
}
dtrEmail.Close();

}

Because you are using the reader as a datasource, and the object using
the
datasource deals with getting data out of it. This is very different
then
trying to access a specific pieces of data in the result set.