SqlDataReader stops reading values if null is present in table

Question

I am using a SqlDataReader to read values to database and then storing the values in appropriate textboxes. The code is working fine if there is no null value in the row but if there is one, it stops reading all the values after it has encountered null and displays blanks in all textboxes.

This is the code I am using :

SqlConnection con = new SqlConnection(ConfigurationManager.
ConnectionStrings["CS"].ConnectionString.ToString());

con.Open();

cmd.CommandText = "SELECT dbo.asp2.CustomerName, dbo.asp2.Email,
dbo.asp2.CP, dbo.asp2.CPN, dbo.asp2.ProductName, dbo.asp2.Warranty,
dbo.asp2.ProductSerial, dbo.asp2.ProductNumber, dbo.asp2.Description,
dbo.asp2.IssueDate, dbo.asp2.Status, dbo.asp2.Remarks, 
dbo.asp2.EngineerName from dbo.asp2 where ID='" + textBox1.Text + "'";

cmd.Connection = con;

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
    textBox3.Text = rdr.GetString(0);
    textBox4.Text = rdr.GetString(1);
    textBox5.Text = rdr.GetString(2);
    textBox6.Text = rdr.GetString(3);
    textBox7.Text = rdr.GetString(4);
    textBox8.Text = rdr.GetString(5);
    textBox9.Text = rdr.GetString(6);
    textBox10.Text = rdr.GetString(7);
    textBox11.Text = rdr.GetString(8);
    textBox2.Text = rdr.GetDateTime(9).ToString();
    textBox12.Text = rdr.GetString(10);
    textBox13.Text = rdr.GetString(11);
    comboBox1.Text = rdr.GetString(12);  
}
con.Close();

I can't seem to figure out the problem here. Could you point it out? What I need it to do is, set the textboxes empty where there is a null value and if there is a value in table then set that value to textbox.

P.S all the values I am retrieving are stored as nvarchar in database except for IssueDate which is in smalldatetime.

Probably your code that is calling the above has a try/catch that is swallowing the exception thrown when the reader encounters a null value. Either test for null (rdr.IsDBNull), or you can also use ToString() to convert null values to an empty string (e.g. rdr[0].ToString()) — to StackOverflow
– to StackOverflow, Commented Mar 12, 2014 at 11:14
Once you've fixed the actual stopping issues, you might also want to read up on SQL injection and then on using parameters. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 12, 2014 at 11:49
@HenkHolterman What do you mean by that? I am reading all the records. My code is working perfectly if there is no null value in the specific row of the table. If there is, it stops reading at that point. — NewbieProgrammer
– NewbieProgrammer, Commented Mar 12, 2014 at 12:04
I am learning C#, so I googled and this is where I got my code from to retrieve multiple values from database into textboxes stackoverflow.com/questions/16565035/… — NewbieProgrammer
– NewbieProgrammer, Commented Mar 12, 2014 at 12:09

Shajee Afzal · Accepted Answer · 2014-03-27 06:29:20Z

1

I was facing the same problem (reader stopped after encountering null), what I did was :

I replaced this :

textBox3.Text = rdr.GetString(0);

with this :

textBox3.Text = rdr["ColumnName"].ToString();

answered Mar 27, 2014 at 6:29

Shajee Afzal

6154 gold badges8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravindra patankar · Accepted Answer · 2014-03-27 06:38:24Z

0

Try this...

while (rdr.Read()) {

if (rdr.IsDBNull(0)) { textBox3.Text = rdr.GetString(0); } else { textBox3.Text = ""; }

---- For Datetime

if (rdr.IsDBNull(9)) { textBox2.Text = rdr.GetDateTime(9).ToString(); } else { textBox2.Text = ""; }

}

answered Mar 27, 2014 at 6:38

Ravindra patankar

1139 bronze badges

Collectives™ on Stack Overflow

SqlDataReader stops reading values if null is present in table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related