Conversion from type 'DBNull' to type 'String' is not valid when inserting data from datagridview to sql database

Question

Hello people i'm a newbie programmer of vb.net, so i have this problem so far, i am searching for a related answer but found no luck, so i decided to post my problem.

so here it is.

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    Dim connstr As String = "server=midtelephone\sqlexpress; database=testdb; user= sa; password=sa;"

    cmdconn = New SqlConnection
    cmd = New SqlCommand
    cmdconn.ConnectionString = sqlstr
    cmd.Connection = cmdconn
    cmdconn.Open()

    Dim period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO As String
    For i As Integer = 0 To Me.DataGridView1.Rows.Count - 1

        'cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no) values" &_
        '" (@period,@VOUCH_AMT,@INDIVIDUAL_AMT,@check_no,@D_MAILED,@DIR_NO)"

        period = Me.DataGridView1.Rows(i).Cells(0).Value()
        VOUCH_AMT = Me.DataGridView1.Rows(i).Cells(1).Value()
        INDIVIDUAL_AMT = Me.DataGridView1.Rows(i).Cells(2).Value()
        check_no = Me.DataGridView1.Rows(i).Cells(3).Value()
        D_MAILED = Me.DataGridView1.Rows(i).Cells(4).Value()
        DIR_NO = Me.DataGridView1.Rows(i).Cells(5).Value()

        cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no)values" & _
            "('" & period & "','" & VOUCH_AMT & "','" & INDIVIDUAL_AMT & "','" & check_no & "','" & D_MAILED & "', '" & DIR_NO & "')"
        cmd.ExecuteNonQuery()
        MsgBox("Saved")
    Next
    cmdconn.Close()

End Sub

i'd like to update my sql database through datagridview.

( that datagridview was called from another sql query - cmd.CommandText = " select period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO from tobee.EBD_BILLHISTORY where CLAIM_NO like '" + claimno.ToString + "'"select period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO from tobee.EBD_BILLHISTORY where CLAIM_NO like '" + claimno.ToString + "'")

now the problem is/are, the error/s come up when i click the save button. whenever i click the save button leaving the other columns empty. errors shows up "invalidcastexception was unhandled" (on a certain rows that is empty) -Conversion from type 'DBNull' to type 'String' is not valid.

aaaaaand.. another error comes up when inserting different value in each rows. specially when i want to insert a datetime value. not really familiar with the code.

is there something wrong with my code? all processes are good(like pulling up the data) except clicking the save button. or updating my database. thanks in advance. your replies are much appreciated.

I updated the code again please take a look, it is a bit more lines. I included the checking of Null/Nothing, DBNull, empty value. — Jade
– Jade, Commented Feb 10, 2014 at 6:06
@jade thank you, all lines proceeded with no errors, but 1 thing is my concern.. the changes i made in the datagridview doesn't update in my sql database when i click the save button. anyways i'll post it in a new question, or u can help me find a solution right here and right now? :O thx in advance — Newbie matt
– Newbie matt, Commented Feb 10, 2014 at 6:32
actually there is, the datagridview(EBD_billhistory table) was pulled up from a "private Sub loaddgvfrm3()" cmd.CommandText = "select period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO from tobee.EBD_BILLHISTORY where CLAIM_NO like '" + claimno.ToString + "'" Dim dt As New DataTable da = New SqlDataAdapter da.SelectCommand = cmd da.Fill(dt) frmEb.DataGridView1.DataSource = dt cmdconn.Close() — Newbie matt
– Newbie matt, Commented Feb 11, 2014 at 2:12
actually ,that private sub was pulled up from another form.. did i gave you the appropriate information? or u're looking for extensive info? — Newbie matt
– Newbie matt, Commented Feb 11, 2014 at 3:03

Jade · Accepted Answer · 2014-02-10 06:00:09Z

1

Try the code below, I also updated the code to avoid SQL injections. You must check if the value from cell is DBNull and handle it appropriately

Dim connstr As String = "server=midtelephone\sqlexpress; database=testdb; user= sa; password=sa;"

cmdconn = New SqlConnection
cmd = New SqlCommand
cmdconn.ConnectionString = connstr 'sqlstr
cmd.Connection = cmdconn
cmdconn.Open()

Dim period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO As String
For i As Integer = 0 To Me.DataGridView1.Rows.Count - 1

    'cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no) values" &_
    '" (@period,@VOUCH_AMT,@INDIVIDUAL_AMT,@check_no,@D_MAILED,@DIR_NO)"

    With Me.DataGridView1.Rows(i)

        If IsDBNull(.Cells(0).Value()) OrElse .Cells(0).Value() Is Nothing OrElse .Cells(0).Value().ToString().Trim() = "" Then
            period = ""
        Else
            period = .Cells(0).Value()
        End If
        If IsDBNull(.Cells(1).Value()) OrElse .Cells(1).Value() Is Nothing OrElse .Cells(1).Value().ToString().Trim() = "" Then
            VOUCH_AMT = "0"
        Else
            VOUCH_AMT = .Cells(1).Value()
        End If
        If IsDBNull(.Cells(2).Value()) OrElse .Cells(2).Value() Is Nothing OrElse .Cells(2).Value().ToString().Trim() = "" Then
            INDIVIDUAL_AMT = "0"
        Else
            INDIVIDUAL_AMT = .Cells(2).Value()
        End If
        If IsDBNull(.Cells(3).Value()) OrElse .Cells(3).Value() Is Nothing OrElse .Cells(3).Value().ToString().Trim() = "" Then
            check_no = ""
        Else
            check_no = .Cells(3).Value()
        End If
        If IsDBNull(.Cells(4).Value()) OrElse .Cells(4).Value() Is Nothing OrElse .Cells(4).Value().ToString().Trim() = "" Then
            D_MAILED = ""
        Else
            D_MAILED = .Cells(4).Value()
        End If
        If IsDBNull(.Cells(5).Value()) OrElse .Cells(5).Value() Is Nothing OrElse .Cells(5).Value().ToString().Trim() = "" Then
            DIR_NO = ""
        Else
            DIR_NO = .Cells(5).Value()
        End If

        'period = IIf(IsDBNull(.Cells(0).Value()), "", .Cells(0).Value())
        'VOUCH_AMT = IIf(IsDBNull(.Cells(1).Value()), "0", IIf(.Cells(1).Value().ToString().Trim() = "", "0", .Cells(1).Value()))
        'INDIVIDUAL_AMT = IIf(IsDBNull(.Cells(2).Value()), "0", IIf(.Cells(2).Value().ToString().Trim() = "", "0", .Cells(2).Value()))
        'check_no = IIf(IsDBNull(.Cells(3).Value()), "", .Cells(3).Value())
        'D_MAILED = IIf(IsDBNull(.Cells(4).Value()), "", .Cells(4).Value())
        'DIR_NO = IIf(IsDBNull(.Cells(5).Value()), "", .Cells(5).Value())

    End With

    cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no)values" & _
        "('" & period.Replace("'", "''") & "'," & VOUCH_AMT & "," & INDIVIDUAL_AMT & ",'" & check_no.Replace("'", "''") & "','" & D_MAILED.Replace("'", "''") & "', '" & DIR_NO.Replace("'", "''") & "')"
    cmd.ExecuteNonQuery()
    MsgBox("Saved")
Next
cmdconn.Close()

edited Feb 10, 2014 at 6:00

answered Feb 10, 2014 at 2:33

Jade

2,9941 gold badge14 silver badges9 bronze badges

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

11 Comments

Newbie matt Over a year ago

Thank you for your code, it is working! but where should i put the value of my data type? is it like period = IIf(IsDBNull(.Cells(0).Value()), "", .Cells(0).Value.decimal())

Jade Over a year ago

can you enumerate the data datatype of each columns (period, vouch_amt, individual_amt, check_no, d_mailed, dir_no) or which columns are expecting number instead of string? Then put either Null value or 0 value instead of ""

Newbie matt Over a year ago

period = char(25) vouch_amt = decimal(18,2) individual_Amt = decimal(18,2) check_no = char(15) d_mailed = datetime dir_no = char(15)

Jade Over a year ago

@Newbiematt, There, I've updated the code. Hope this gives you the right answer.

Newbie matt Over a year ago

thank you! but a new error occured after clicking the save button -missingmemberexception was unhandled "Public member 'Trim' on type 'DBNull' not found".. :(. how does the trim works anyway? the error was highlighted in this line - VOUCH_AMT = IIf(IsDBNull(.Cells(1).Value()), "0", IIf(.Cells(1).Value().Trim() = "", "0", .Cells(1).Value()))- when i tried to put a value on its cell

|

GiorgiTBS · Accepted Answer · 2014-02-10 06:39:39Z

0

It's better to pass values to command as parameters. Check small portion of code below:

cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no) values" &_
    " (@period,@VOUCH_AMT,@INDIVIDUAL_AMT,@check_no,@D_MAILED,@DIR_NO)"
cmd.parameters.addwithvalue("@Period", iif(period is dbnull.value, 0, period))

answered Feb 10, 2014 at 6:39

GiorgiTBS

3692 silver badges4 bronze badges

1 Comment

Newbie matt Over a year ago

thank you for posting up an answer, the '@ syntax' doesn't work on me though. the answer posted above was more likely functional in my forms, now the problem is, it seems that those values that i inserted in the dgv was not updated in my database.

Collectives™ on Stack Overflow

Conversion from type 'DBNull' to type 'String' is not valid when inserting data from datagridview to sql database

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related