locked
Retrieving NULL data from a sql table RRS feed

  • Question

  • User17798125 posted

    Hi forum, 

    I have a little problem, in my data base I have a table with a lot of fields (columns), more or less 50, and when I need to put that data into their corresponding textboxes, some of them do not respond, as I need. The code starts to work and start to populate the textboxes, but if there is a NULL field, the next textboxes are not filled with its data, the process of filing stops. I am using the next code to fill every textbox. In the next example if date11,text has info it is filled, the same with date21.text, but if date31.text is null, the process ends even if date41.text has to be filled and the table has info (dateJ) and the next date51 as well.

    Dim row As Data.DataRow = DT.Rows(0)
    date11.Text = CStr(row("dateL"))
    date21.Text = CStr(row("dateM"))
    date31.Text = CStr(row("dateX"))
    date41.Text = CStr(row("dateJ"))
    date51.Text = CStr(row("dateV"))

    How I can fill all my textboxes even the null ones?

    Thanks!!!

    Tuesday, February 5, 2019 12:30 AM

Answers

  • User283571144 posted

    Hi Jonsey10,

    According to your description and codes, I guess you may get the "Conversion from type 'DBNull' to type 'String' is not valid." error.

    This means if the row("dateJ") is null, you couldn't convert it to string.

    Here is a workaround, you could write a function to check the row data is dbnull or not.

    More details, you could refer to below codes:

    ASPX:

    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="SelectData.aspx.vb" Inherits="VBWebform.SelectData" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" />
                <asp:TextBox ID="date11" runat="server"></asp:TextBox>
                <asp:TextBox ID="date21" runat="server"></asp:TextBox>
    
                <asp:TextBox ID="date31" runat="server"></asp:TextBox>
                <asp:TextBox ID="date41" runat="server"></asp:TextBox>
                <asp:TextBox ID="date51" runat="server"></asp:TextBox>
             </div>
        </form>
    </body>
    </html>
    



    Code-behind:

    Imports System.Data.SqlClient
    
    Public Class SelectData
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
        End Sub
    
    
    
        Protected Sub Button1_Click1(sender As Object, e As EventArgs)
            Dim d1 As DataTable = New DataTable()
    
            Using connection As SqlConnection = New SqlConnection("Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-VbIdentity-20180507035628;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False")
                Dim adapter As SqlDataAdapter = New SqlDataAdapter()
                adapter.SelectCommand = New SqlCommand("select * from [dbo].[TableA]", connection)
                adapter.Fill(d1)
            End Using
            Dim row As Data.DataRow = d1.Rows(0)
    
    
            date11.Text = check(row, "dateL")
            date21.Text = check(row, "dateM")
            date31.Text = check(row, "dateX")
            date41.Text = check(row, "dateJ")
            date51.Text = check(row, "dateV")
        End Sub
    
    
    
        Public Function check(ByVal row As DataRow, ByVal column As String) As String
            If Convert.IsDBNull(row(column)) Then
                Return ""
            Else
                Return CStr(row(column))
            End If
        End Function
    
    
    
    
    End Class

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 5, 2019 4:41 AM

All replies

  • User283571144 posted

    Hi Jonsey10,

    According to your description and codes, I guess you may get the "Conversion from type 'DBNull' to type 'String' is not valid." error.

    This means if the row("dateJ") is null, you couldn't convert it to string.

    Here is a workaround, you could write a function to check the row data is dbnull or not.

    More details, you could refer to below codes:

    ASPX:

    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="SelectData.aspx.vb" Inherits="VBWebform.SelectData" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" />
                <asp:TextBox ID="date11" runat="server"></asp:TextBox>
                <asp:TextBox ID="date21" runat="server"></asp:TextBox>
    
                <asp:TextBox ID="date31" runat="server"></asp:TextBox>
                <asp:TextBox ID="date41" runat="server"></asp:TextBox>
                <asp:TextBox ID="date51" runat="server"></asp:TextBox>
             </div>
        </form>
    </body>
    </html>
    



    Code-behind:

    Imports System.Data.SqlClient
    
    Public Class SelectData
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
        End Sub
    
    
    
        Protected Sub Button1_Click1(sender As Object, e As EventArgs)
            Dim d1 As DataTable = New DataTable()
    
            Using connection As SqlConnection = New SqlConnection("Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-VbIdentity-20180507035628;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False")
                Dim adapter As SqlDataAdapter = New SqlDataAdapter()
                adapter.SelectCommand = New SqlCommand("select * from [dbo].[TableA]", connection)
                adapter.Fill(d1)
            End Using
            Dim row As Data.DataRow = d1.Rows(0)
    
    
            date11.Text = check(row, "dateL")
            date21.Text = check(row, "dateM")
            date31.Text = check(row, "dateX")
            date41.Text = check(row, "dateJ")
            date51.Text = check(row, "dateV")
        End Sub
    
    
    
        Public Function check(ByVal row As DataRow, ByVal column As String) As String
            If Convert.IsDBNull(row(column)) Then
                Return ""
            Else
                Return CStr(row(column))
            End If
        End Function
    
    
    
    
    End Class

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 5, 2019 4:41 AM
  • User-2054057000 posted

    If you want to check the null values then use DBNull.Value

    if (! DBNull.Value.Equals(row("dateL"))) 
    // do something
       else
    // do something

    Tuesday, February 5, 2019 3:39 PM
  • User17798125 posted

    Thanks! My code is a little bit different, but the key is on the function. Greetings!

    Tuesday, February 5, 2019 8:55 PM