Invalid attempt to read when no data is present

A

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
 
K

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.

Andy Sutorius said:
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
 
A

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


Kevin Spencer said:
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.

Andy Sutorius said:
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
 
M

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.
 
K

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.


Andy Sutorius said:
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


Kevin Spencer said:
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.

Andy Sutorius said:
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
 
A

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();
}


}
 
K

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.

Andy Sutorius said:
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 said:
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.
 
A

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();
}
 
M

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.

Andy Sutorius said:
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();
}



Kevin Spencer said:
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.
 
A

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();

}
 
K

Kevin Spencer

The DataBinding does the reading in those cases.

--
HTH,

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

Andy Sutorius said:
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();
}



Kevin Spencer said:
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.
 
A

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();

}
 
K

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.

Andy Sutorius said:
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();

}



Marina said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top