1

Help with my problem whenever i try to populate the textboxes the problem always appear "conversion DBNull to string is not valid" and do not know what to do,

the database i used is microsoft access. I'm new to vb.net and just trying to understand the table but am unable to understand the DBNull thing

here is my code for the populate and retrieve. i think the problem is in here

'POPULATE LISTVIEW
Private Sub Populate(LRN As String, FullName As String, Address As String, Parent As String, DateofBirth As String, Gender As String, studguardian As String, studaddress As String,
                     sy1 As String, sy2 As String, sy3 As String, sy4 As String, sy5 As String, sy6 As String, sy7 As String, sy8 As String, sy9 As String, sy10 As String, sy11 As String, sy12 As String, sy13 As String, sy14 As String, sy15 As String, sy16 As String, sy17 As String, sy18 As String, sy19 As String, sy20 As String, sy21 As String, sy22 As String, sy23 As String, sy24 As String,
                     s1 As String, s2 As String, s3 As String, s4 As String, s5 As String, s6 As String, s7 As String, s8 As String, s9 As String, s10 As String, s11 As String, s12 As String, s13 As String, s14 As String, s15 As String, s16 As String, s17 As String, s18 As String, s19 As String, s20 As String, s21 As String, s22 As String, s23 As String, s24 As String,
                     g1 As String, g2 As String, g3 As String, g4 As String, g5 As String, g6 As String, g7 As String, g8 As String, g9 As String, g10 As String, g11 As String, g12 As String, g13 As String, g14 As String, g15 As String, g16 As String, g17 As String, g18 As String, g19 As String, g20 As String, g21 As String, g22 As String, g23 As String, g24 As String,
                     n1 As String, n2 As String, n3 As String, n4 As String, n5 As String, n6 As String, n7 As String, n8 As String, n9 As String, n10 As String, n11 As String, n12 As String, n13 As String, n14 As String, n15 As String, n16 As String, n17 As String, n18 As String, n19 As String, n20 As String, n21 As String, n22 As String, n23 As String, n24 As String,
                     ga1 As String, ga2 As String, ga3 As String, ga4 As String, ga5 As String, ga6 As String, ga7 As String, ga8 As String, ga9 As String, ga10 As String, ga11 As String, ga12 As String, ga13 As String, ga14 As String, ga15 As String, ga16 As String, ga17 As String, ga18 As String, ga19 As String, ga20 As String, ga21 As String, ga22 As String, ga23 As String, ga24 As String,
                     r1 As String, r2 As String, r3 As String, r4 As String, r5 As String, r6 As String, r7 As String, r8 As String, r9 As String, r10 As String, r11 As String, r12 As String, r13 As String, r14 As String, r15 As String, r16 As String, r17 As String, r18 As String, r19 As String, r20 As String, r21 As String, r22 As String, r23 As String, r24 As String)
    'ROW ARRAY
    Dim row As String()  = New String() {LRN, FullName, Address, Parent, DateofBirth, Gender, studguardian, studaddress,
                                        sy1, sy2, sy3, sy4, sy5, sy6, sy7, sy8, sy9, sy10, sy11, sy12, sy13, sy14, sy15, sy16, sy17, sy18, sy19, sy20, sy21, sy22, sy23, sy24,
                                        s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, s21, s22, s23, s24,
                                        g1, g2, g3, g4, g5, g6, g7, g8, g9, g10, g11, g12, g13, g14, g15, g16, g17, g18, g19, g20, g21, g22, g23, g24,
                                        n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, n13, n14, n15, n16, n17, n18, n19, n20, n21, n22, n23, n24,
                                        ga1, ga2, ga3, ga4, ga5, ga6, ga7, ga8, ga9, ga10, ga11, ga12, ga13, ga14, ga15, ga16, ga17, ga18, ga19, ga20, ga21, ga22, ga23, ga24,
                                        r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12, r13, r14, r15, r16, r17, r18, r19, r20, r21, r22, r23, r24
                                        }

    Dim item As ListViewItem = New ListViewItem(row)

    'ADD TO ROWS COLLECTION
    ListView1.Items.Add(item)

End Sub

'RETRIEVE FROM DB
Private Sub Retrieve()
    ListView1.Items.Clear()
    'SQL STM
    Dim sql As String = "SELECT*  FROM stutb  "

    cmd = New OleDbCommand(sql, con)

    'OPEN CON,RETRIEVE,FILL LISTVIEW
    Try
        con.Open()
        adapter = New OleDbDataAdapter(cmd)

        adapter.Fill(dt)

        'LOOP THRU DT
        For Each row In dt.Rows
            Populate(row(1), row(2), row(3), row(4), row(5), row(6), row(7), row(8), row(9), row(10), row(11), row(12), row(13), row(14), row(15), row(16), row(17), row(18), row(19), row(20), row(21), row(22), row(23), row(24), row(25), row(26), row(27), row(28), row(29), row(30), row(31), row(32),
                     row(33), row(34), row(35), row(36), row(37), row(38), row(39), row(40), row(41), row(42), row(43), row(44), row(45), row(46), row(47), row(48), row(49), row(50), row(51), row(52), row(53), row(54), row(55), row(56), row(57), row(58), row(59), row(60), row(61), row(62), row(63), row(64), row(65),
                     row(66), row(67), row(68), row(69), row(70), row(71), row(72), row(73), row(74), row(75), row(76), row(77), row(78), row(79), row(80), row(81), row(82), row(83), row(84), row(85), row(86), row(87), row(88), row(89), row(90), row(91), row(92), row(93), row(94), row(95), row(96), row(97), row(98),
                     row(99), row(100), row(101), row(102), row(103), row(104), row(105), row(106), row(107), row(108), row(109), row(110), row(111), row(112), row(113), row(114), row(115), row(116), row(117), row(118), row(119), row(120), row(121), row(122), row(123), row(124), row(125), row(126),
                row(127), row(128), row(129), row(130), row(131), row(132), row(133), row(134), row(135), row(136), row(137), row(138), row(139), row(140), row(141), row(142), row(143), row(144), row(145), row(146), row(147), row(148), row(149), row(150), row(151), row(152))
        Next

        'CLEAR DATATABLE
        dt.Rows.Clear()
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
        con.Close()
    End Try

End Sub
Improve this question
8
  • A NULL value in a database result set has the a value of DbNull.Value, you must test for this before attempting to treat the field value as any other type (such as assigning it to a TextBox.Text). IsDBNull() is one way to do this. Commented May 30, 2018 at 11:22
  • isee. let me try i will update you ASAP Commented May 30, 2018 at 11:27
  • @Alex K. hm. i don't understand but the thing i just want to retrieve the data from the database to the listview but that occur saying "conversion DBNull to string is not valid" Commented May 30, 2018 at 11:35
  • 2
    That means the field you are attempting to retrieve is empty, hence a null value. So there is nothing to convert, thats why you get the exception. Commented May 30, 2018 at 12:06
  • Depending on how you get the data, you'll have to check if the value is null with reader.IsDbNull() or something similar. Then you decided what to do with the null and if there's data you retrieve it as usual. Also, might I suggest reading up on list, array or class, you have a lot of parameters there. Commented May 30, 2018 at 13:11

2 Answers 2

Reset to default
2

This is a common issue when retrieving data from a database. You need to check for null before or as you assign the value to a control. For Example (I use inline if a lot):

Texbox_SomeTextBox.Text = If(isDBNull(MyDatabaseColomn), "", MyDatabaseColomn)

So when you create each row for your listview you need to check each column returned in the dataset for null. It gets a bit tedious, but necessary.

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

2 Comments

I'd strongly recommend discontinuing your use of the IIf function and instead to use the ternary If operator. IIf is functionally obsolete, I can't think of a situation where it isn't better to use the ternary If operator.
Edited my answer
0

If you know which fields allow nulls and are strings try row(2) & "".

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.