Check if datagrid contain null value

Question

sushilsth 0 Light Poster

11 Years Ago

Private Sub one_btninsert_Click(sender As Object, e As EventArgs) Handles one_btninsert.Click
        For i = 0 To DataGridView1.Rows.Count - 1
            Dim row1 As Integer = Val(DataGridView1.Rows(i).Cells(1).Value.ToString)
            Dim row2 As String = DataGridView2.Rows(i).Cells(2).Value.ToString
            Dim row3 As String = DataGridView2.Rows(i).Cells(3).Value.ToString
            Dim row4 As String = DataGridView2.Rows(i).Cells(4).Value.ToString
            Dim row5 As String = DataGridView2.Rows(i).Cells(5).Value.ToString
            Dim row6 As String = DataGridView2.Rows(i).Cells(6).Value.ToString
            Dim row7 As String = DataGridView2.Rows(i).Cells(7).Value.ToString
            Dim row8 As String = DataGridView2.Rows(i).Cells(8).Value.ToString
            Dim row9 As String = DataGridView2.Rows(i).Cells(9).Value.ToString
            Dim row10 As String = DataGridView2.Rows(i).Cells(10).Value.ToString
            Dim row11 As String = DataGridView2.Rows(i).Cells(11).Value.ToString
            Dim row12 As String = DataGridView2.Rows(i).Cells(12).Value.ToString
            Dim row13 As String = DataGridView2.Rows(i).Cells(13).Value.ToString
            Dim row14 As String = DataGridView2.Rows(i).Cells(14).Value.ToString
            Dim row15 As String = DataGridView2.Rows(i).Cells(15).Value.ToString
            Dim row16 As String = DataGridView2.Rows(i).Cells(16).Value.ToString
            Dim row17 As String = DataGridView2.Rows(i).Cells(17).Value.ToString

            Dim sqlquery As String = "INSERT INTO nursery(regno,rollno,nepali,com_english_w,com_english_o,com_math,social_w,social_o,science_w,science_o,hpe_w,hpe_o,creative,health,extra_math,extra_eng_w,extra_eng_o) values('" & row1 & "','" & row2 & "','" & row3 & "','" & row4 & "','" & row5 & "','" & row6 & "','" & row7 & "','" & row8 & "','" & row9 & "','" & row10 & "','" & row11 & "','" & row12 & "','" & row13 & "','" & row14 & "','" & row15 & "','" & row16 & "','" & row17 & "')"
            Dim sqlcommand As New OleDbCommand
            With sqlcommand
                .CommandText = sqlquery
                .Connection = conn
                .ExecuteNonQuery()

            End With

        Next
    End Sub

    Private Sub DataGridView2_CellValidating(ByVal sender As Object, ByVal e As DataGridViewCellValidatingEventArgs) Handles DataGridView2.CellValidating
        If DataGridView2.Item(e.ColumnIndex, e.RowIndex).Value Is Nothing Then
            ' Show the user a message
            MessageBox.Show("You have left the cell empty")

            ' Fail validation (prevent them from leaving the cell)
            e.Cancel = True
        End If
    End Sub

hi, its code for inserting data from datagrid view to ms access. while inserting i need to valid null values in each cells. i wrote above code but it doesnot let me to move from one cells to another cells after inserting data in one cells. MessageBox.Show("You have left the cell empty").. always this message appears.. plz help..

vb.net

3 Contributors
12 Replies
3K Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by Begginnerdev

Luc001 77 Posting Whiz

11 Years Ago

Hi,

Remove this part:

 ' Fail validation (prevent them from leaving the cell)
e.Cancel = True

Begginnerdev 256 Junior Poster

11 Years Ago

You can try wrapping the columns in IIFs.

For example:

IFF(IsNothing(DataGridView2.Rows(i).Cells(2).Value.ToString) Or DataGridView2.Rows(i).Cells(2).Value.ToString = String.Empty, String.Empty, DataGridView2.Rows(i).Cells(2).Value.ToString)

The IIF qualifies the statement then pulls the value given.
In this example would be the equivilent of:

IF IsNothing(DataGridView2.Rows(i).Cells(2).Value.ToString)) Or DataGridView2.Rows(i).Cells(2).Value.ToString = String.Empty Then
    row2 = String.Empty
Else
    row2 = DataGridView2.Rows(i).Cells(2).Value.ToString
End If

A true value should be failsafe like String.Empty or 0.
A false value should be the actual column.

Just a note though, if you WANT null values passed to the database use:

row2 = DBNull.Value

You can read more about this function here.

Edited 11 Years Ago by Begginnerdev

Begginnerdev 256 Junior Poster

11 Years Ago

You could pass the values into a DataAdapter instead of using a string based method.

This would safeguard against SQL Injection attacks, and add some cushion for errors.

Private Sub Insert()
    Dim da As New OleDBDataAdapter("SELECT * FROM nursery WHERE 1=2", MyOleDBConnection)
    Dim ds As New DataSet
    Try
        da.Fill(ds, "Nursery") 'This will get table structure without data.

        If Not IsNothing(ds.Tables("Nursery")) Then
            For Each r As DataGridViewRow In DataGridView1.Rows
                Dim dr As DataRow = ds.Tables("Nursery").Rows.Add

                dr("regno") = IIf(Not IsNothing(r.Cells(1).Value.ToString), DBNull.Value, CInt(r.Cells(1).Value.ToString))
                dr("rollno") = IIf(Not IsNothing(r.Cells(2).Value.ToString), DBNull.Value, r.Cells(2).Value.ToString)
                dr("nepali") = IIf(Not IsNothing(r.Cells(3).Value.ToString), DBNull.Value, r.Cells(3).Value.ToString)
                dr("com_english_w") = IIf(Not IsNothing(r.Cells(4).Value.ToString), DBNull.Value, r.Cells(4).Value.ToString)
                dr("com_english_o") = IIf(Not IsNothing(r.Cells(5).Value.ToString), DBNull.Value, r.Cells(5).Value.ToString)
                dr("com_math") = IIf(Not IsNothing(r.Cells(6).Value.ToString), DBNull.Value, r.Cells(6).Value.ToString)
                dr("social_w") = IIf(Not IsNothing(r.Cells(7).Value.ToString), DBNull.Value, r.Cells(7).Value.ToString)
                dr("social_o") = IIf(Not IsNothing(r.Cells(8).Value.ToString), DBNull.Value, r.Cells(8).Value.ToString)
                dr("science_w") = IIf(Not IsNothing(r.Cells(9).Value.ToString), DBNull.Value, r.Cells(9).Value.ToString)
                dr("science_o") = IIf(Not IsNothing(r.Cells(10).Value.ToString), DBNull.Value, r.Cells(10).Value.ToString)
                dr("hpe_w") = IIf(Not IsNothing(r.Cells(11).Value.ToString), DBNull.Value, r.Cells(11).Value.ToString)
                dr("hpe_o") = IIf(Not IsNothing(r.Cells(12).Value.ToString), DBNull.Value, r.Cells(12).Value.ToString)
                dr("creative") = IIf(Not IsNothing(r.Cells(13).Value.ToString), DBNull.Value, r.Cells(13).Value.ToString)
                dr("health") = IIf(Not IsNothing(r.Cells(14).Value.ToString), DBNull.Value, r.Cells(14).Value.ToString)
                dr("extra_math") = IIf(Not IsNothing(r.Cells(15).Value.ToString), DBNull.Value, r.Cells(15).Value.ToString)
                dr("extra_eng_w") = IIf(Not IsNothing(r.Cells(16).Value.ToString), DBNull.Value, r.Cells(16).Value.ToString)
                dr("extra_eng_o") = IIf(Not IsNothing(r.Cells(17).Value.ToString), DBNull.Value, r.Cells(17).Value.ToString)
            Next

            da.InsertCommand = New OleDbCommandBuilder(da).GetInsertCommand
            da.Update(ds)

            MsgBox("Insert successful!")
        Else
            MsgBox("Table not retreived!")
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
        da = Nothing
        ds = Nothing
    End Try
End Sub

Edited 11 Years Ago by Begginnerdev

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sushilsth 0 Light Poster · Answer 1 · 2013-07-25T17:39:13+00:00

thanx luc for response.. removing it leting me to insert into another cell leaving empty cells but it doesnt make empty cells to 0..

sushilsth 0 Light Poster · Answer 2 · 2013-07-25T17:41:47+00:00

begginnnerdev thanx.. but it would make code much long for making each as u said.. isnt there any short code for this.. thanx..

sushilsth 0 Light Poster · Answer 3 · 2013-07-25T18:03:46+00:00

hi begginnerdev..i found error..........iff not declared... how to declare it..

sushilsth 0 Light Poster · Answer 4 · 2013-07-25T18:05:40+00:00

oh sorry... i wrote iff so i found error.. i was iif.. hehe.. thanx

sushilsth 0 Light Poster · Answer 5 · 2013-07-26T11:55:32+00:00

thanx begginnerdev.. i found error on
Dim da As New OleDbDataAdapter("SELECT * FROM nursery WHERE 1=2", myOleDbConnection)
muoledbconnection is not declared

Begginnerdev 256 Junior Poster · Answer 6 · 2013-07-26T12:00:07+00:00

You should replace MyOleDBConnection with the name of your connection.

For instance:

Dim MyOleDBConnection As New OleDBConnection("ConnectionStringHere")

sushilsth 0 Light Poster · Answer 7 · 2013-07-26T12:08:59+00:00

    Private Sub one_btninsert_Click(sender As Object, e As EventArgs) Handles one_btninsert.Click

        ' Dim i1 As Integer
        'Dim i2 As Integer
        'For i1 = 0 To Me.DataGridView2.Columns.Count - 1
        ' For i2 = 0 To Me.DataGridView2.Rows.Count - 1
        'If String.IsNullOrEmpty(Me.DataGridView2.Item(i1, i2).Value) Then
        'Me.DataGridView2.Rows(i1).Cells(i2).Value = 0

        'End If
        'Next
        'Next

        For i = 0 To DataGridView1.Rows.Count - 1
            '  Dim row1 As Integer = DataGridView2.Rows(i).Cells(1).Value.ToString
            Dim row1 As Integer = Nothing
            Dim row2 As String = Nothing
            Dim row3 As String = Nothing
            Dim row4 As String = Nothing
            Dim row5 As String = Nothing
            Dim row6 As String = Nothing
            Dim row7 As String = Nothing
            Dim row8 As String = Nothing
            Dim row9 As String = Nothing

            Dim row10 As String = Nothing

            Dim row12 As String = Nothing
            Dim row11 As String = Nothing
            Dim row13 As String = Nothing
            Dim row14 As String = Nothing
            Dim row15 As String = Nothing
            Dim row16 As String = Nothing
            Dim row17 As String = Nothing
            IIf(IsNothing(DataGridView2.Rows(i).Cells(1).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(1).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(2).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(2).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(3).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(3).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(4).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(4).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(5).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(5).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(6).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(6).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(7).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(7).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(8).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(8).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(9).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(9).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(10).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(10).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(11).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(11).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(12).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(12).Value.ToString)


            IIf(IsNothing(DataGridView2.Rows(i).Cells(13).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(13).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(14).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(14).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(15).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(15).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(16).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(16).Value.ToString)

            IIf(IsNothing(DataGridView2.Rows(i).Cells(17).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(17).Value.ToString)



            Dim sqlquery As String = "INSERT INTO one(regno,rollno,nepali,com_english_w,com_english_o,com_math,social_w,social_o,science_w,science_o,hpe_w,hpe_o,creative,health,extra_math,extra_eng_w,extra_eng_o) values('" & row1 & "','" & row2 & "','" & row3 & "','" & row4 & "','" & row5 & "','" & row6 & "','" & row7 & "','" & row8 & "','" & row9 & "','" & row10 & "','" & row11 & "','" & row12 & "','" & row13 & "','" & row14 & "','" & row15 & "','" & row16 & "','" & row17 & "')"
            Dim sqlcommand As New OleDbCommand
            With sqlcommand
                .CommandText = sqlquery
                .Connection = conn
                .ExecuteNonQuery()

            End With

        Next
    End Sub

hi begginnerdev.. i did it as u said but when i leave empty cell i find error --object reference not set to instance of object. if i leave empty cell in column 3 i find this error in
IIf(IsNothing(DataGridView2.Rows(i).Cells(3).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(3).Value.ToString)

sushilsth 0 Light Poster · Answer 8 · 2013-07-26T12:24:50+00:00

Dim MyOleDBConnection As New OleDbConnection("ConnString")
        Dim da As New OleDbDataAdapter("SELECT * FROM nursery WHERE 1=2", conn)



    Format of the initialization string does not conform to specification starting at index 0.
    i found this error..

Begginnerdev 256 Junior Poster · Answer 9 · 2013-07-26T15:35:45+00:00

If the cell is empty the Value property will be null;

Change

IIf(IsNothing(DataGridView2.Rows(i).Cells(3).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(3).Value.ToString)

To

IIf(IsNothing(DataGridView2.Rows(i).Cells(3)), String.Empty, DataGridView2.Rows(i).Cells(3).Value.ToString)

As for the connection string, you have to place the string inside the constructor of the connection.

As for the snippet you posted, the connection being passed in to the DataAdapter is not the same connection you are declaring.