none
Visual Studio 2017 RRS feed

  • Question

  • I want to get a record, from a sql server database table, into text boxes. some fields are empty. when i use the sqldatareader to get the record it says cannot get null data using sqldatareader
    SqlConnection con;
    SqlCommand cmd;
    SqlDataReader dr;
    
    search_btn_click()
    {
    
    con.open();
    cmd = new SqlCommand("select * from Student",con);
    dr = cmd.ExcecuteReader();
    
    while(dr.Read())
    {
    txt1.Text = dr.GetString(0);
    txt2.Text = dr.GetString(1);    //This column is empty
    txt3.Text = dr.GetString(2);
    txt4.Text = dr.GetString(3);
    }
    dr.Close();
    con.Close();
    
    }

    Thursday, May 23, 2019 1:34 AM

All replies

  • Try guarding it with .IsDBNull() like this as recommanded in .GetString() documentation.

    txt2.Text = dr.IsDBNull(1)? String.Empty: dr.GetString(1);



    Thursday, May 23, 2019 1:49 AM
    Answerer
  • Hi Thushan,

    Thank you for posting here.

    Based on your description, you want to solve the problem that 'cannot get null data'

    You could try the following code.

                    textBox2.Text = dr.GetString(1)??string.Empty;    //This column is empty

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 23, 2019 5:14 AM
    Moderator
  • No. This will not work as there will be exception throwing at dr.GetString(1) already when the field content is null.
    Thursday, May 23, 2019 5:21 AM
    Answerer
  • the value in the table can be either null or not null.

    if the value is not null, i want to fill the text box with the value.


    Thursday, May 23, 2019 9:15 AM
  • I want to get a record, from a sql server database table, into text boxes. some fields are empty. when i use the sqldatareader to get the record it says cannot get null data usingsqldatareader

    the value in the column

    SqlConnection con;
    SqlCommand cmd;
    SqlDataReader dr;
    
    search_btn_click()
    {
    
    con.open();
    cmd = new SqlCommand("select * from Student",con);
    dr = cmd.ExcecuteReader();
    
    while(dr.Read())
    {
    txt1.Text = dr.GetString(0);
    txt2.Text = dr.GetString(1);  //This column can be either 
                                  //null or not null
    txt3.Text = dr.GetString(2);
    txt4.Text = dr.GetString(3);
    }
    dr.Close();
    con.Close();
    
    }

    can be either null or not null.

    if the value is not null, i want to fill the text box with the value.

    Thursday, May 23, 2019 9:18 AM
  • Hi 

    Thanks for the feedback.

    I used the following code, it works.

      private void Button1_Click(object sender, EventArgs e)
            {
                string str = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                SqlConnection sqlConnection = new SqlConnection(str);
                sqlConnection.Open();
                string sql = String.Format("select * from Student where Name='test1' ");
                SqlCommand cmd = new SqlCommand(sql, sqlConnection);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    textBox1.Text = dr.GetString(0)??string.Empty;
                    textBox2.Text = dr.GetString(1)??string.Empty;    //This column is empty
                    textBox3.Text = dr.GetString(2)??string.Empty;
                }
                dr.Close();
    
                sqlConnection.Close();
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 23, 2019 9:30 AM
    Moderator
  • You can check if the value is DB Null

    while(dr.Read())
    {
    txt1.Text = dr.GetString(0);
    if(!dr.IsDBNull(1))
    {
    txt2.Text = dr.GetString(1);  //This column can be either 
                                  //null or not null
    }
    else
    {
    txt2.Text = "";
    }
    txt3.Text = dr.GetString(2);
    txt4.Text = dr.GetString(3);
    }
    dr.Close();
    con.Close();
    
    }

    Thursday, May 23, 2019 9:37 AM
  • Try set the second field of the record to be null instead of '' to see what is the problem.

    Of course you need to set that field to be nullable first.

    Thursday, May 23, 2019 10:04 AM
    Answerer
  • Hello,

    I would recommend returning from your SELECT statement an empty string rather than checking for a null value in code. To do this you need to specify the fields rather than use *.

    Example, let's say you have SELECT FirstName, MiddleName, LastName FROM Students and MiddleName can be null, use

    SELECT FirstName, COALESCE(MiddleName, '') AS MI, LastName FROM Students

    Then when retrieving data that would had been null as you are now will be an empty string.

    Reference COALESCE.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, May 23, 2019 10:08 AM
    Moderator
  • Hello,

    This is the same reply I posted for your other question asking the same thing. Any time you can handle the issue at the database/query level which is the case here that is preferred and also requires less code. 

    I would recommend returning from your SELECT statement an empty string rather than checking for a null value in code. To do this you need to specify the fields rather than use *.

    Example, let's say you have SELECT FirstName, MiddleName, LastName FROM Students and MiddleName can be null, use

    SELECT FirstName, COALESCE(MiddleName, '') AS MI, LastName FROM Students

    Then when retrieving data that would had been null as you are now will be an empty string.

    Reference COALESCE.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, May 23, 2019 10:10 AM
    Moderator