Reading from Database to placeholder textboxes not working Asp.net

Question

I'm trying to read values from database into placeholder textbox but if one of the columns is null then throws the Unable to cast object of type 'System.DBNull' to type 'System.String'. And if I remove the textboxes that have null then I'm only seeing 1 ID even when it is two ID numbers in the database

SqlDataReader myReader = null;

    SqlCommand command = new SqlCommand("sp_selectUser", objConnection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@userNum", txtuserNum.Text);


    myReader = command.ExecuteReader();
    int i = 0;
    while (myReader.Read())
    {


        TextBox txt = new TextBox();

        txt.Text = (string)myReader["ID1"];
        txt.Text = (string)myReader["ID2"];
        txt.Text = (string)myReader["ID3"];

        ContentPlaceHolder1.Controls.Add(txt);
        ContentPlaceHolder1.Controls.Add(new 

LiteralControl("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"));
            ControlCache.Add(txt);
}

Either in your SQL do a ISNULL check on the values to replace null with blank, or set the values to a variable prior to setting the items to the .Text box and do a null check on the assignment are two options — Brad, Jun 13 '18 at 19:57
A part from the null problem your code replaces the content of the TextBox three times and leave the textbox with the final value. — Steve, Jun 13 '18 at 20:05
Possible duplicate of [SQL Data Reader - handling Null column values](https://stackoverflow.com/questions/1772025/sql-data-reader-handling-null-column-values) — Marco, Jun 13 '18 at 20:05
If you want to have in the same textbox the values of the three fields then you need to write _txt.Text += ...._ for the two last fields. — Steve, Jun 13 '18 at 20:18
@Steve, can you write out what you mean because I want it in separate boxes — , Jun 13 '18 at 20:20

Vandel212 · Answer 1 · 2018-06-13T23:24:18.940

0

You need to check if the value is null first. Try this when you are setting the text box's text value:

txt.Text = myReader["ID1"] == DBNull.Value ? "" : myReader["ID1"].ToString();

That way if it's null, it'll just fill it with a empty string.

As a side note, you are setting the same text box's text value. So the end result will always be whatever ID3 turns up. If you are appending it, use "+=" instead of "=".

edited Jun 13 '18 at 23:24

answered Jun 13 '18 at 20:04

Vandel212

1,074
1
13
28

I edited my answer, try that. The syntax might be off a tiny bit, I'm doing it off the top of my head. Ultimately you need to check for a null value first before doing anything else. Also make sure you do it for each one. – Vandel212 Jun 13 '18 at 23:25

Reading from Database to placeholder textboxes not working Asp.net

1 Answers1