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

            End With

    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..

Recommended Answers

All 12 Replies


Remove this part:

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

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
    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.

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

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

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

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

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
        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)

            da.InsertCommand = New OleDbCommandBuilder(da).GetInsertCommand

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

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

You should replace MyOleDBConnection with the name of your connection.

For instance:

Dim MyOleDBConnection As New OleDBConnection("ConnectionStringHere")
    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

        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

            End With

    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)

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..

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


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


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.

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.