CODEWITHSUNDEEP
sql-servervb.netdatagridview
pankaj babbar
pankaj babbar

Reputation: 69

How to insert multiple rows from datagridview to SQL database in VB.net?

I have made a Form named form1.vb.
I have 4 TextBoxes named supid, supname, supmobile, suploc.
I have a DataGridView named datagridview1.
I am transferring these 4 TextBoxes data in the DataGridView but when I try to send a DataGridView row in SQL database, it sends only 1 row.

I want multiple rows inserted in SQL server at once when I click the insert button.
There are many tutorials about C language but no tutorial about VB.Net.
Please help how to do this in VB.Net.

My code:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    If TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Then
        MessageBox.Show("PLEASE FILL ALL FIELDS")
    Else
        connection.Open()
        Dim thequery As String = "select*from supplierdata where supmobile = @supmobile"
        Dim cmd1 As SqlCommand = New SqlCommand(thequery, connection)
        cmd1.Parameters.AddWithValue("@supmobile", TextBox3.Text)

        Dim reader As SqlDataReader = cmd1.ExecuteReader()

        If reader.HasRows Then
            MessageBox.Show("MOBILE NO IS ALREADY REGISTERED")
        Else
            reader.Close()
            command = New SqlCommand("INSERT INTO supplierdata VALUES (@supid,@supname,@supmobile,@suploc)", connection)
            Dim i As Integer = 0
            For Each row As DataGridViewRow In DataGridView1.Rows
                command.Parameters.Add("supid", SqlDbType.Int).Value = DataGridView1.Rows(i).Cells(0).value
                command.Parameters.Add("supname", SqlDbType.NChar).Value = DataGridView1.Rows(i).Cells(1).Value
                command.Parameters.Add("supmobile", SqlDbType.NChar).Value = DataGridView1.Rows(i).Cells(2).Value
                command.Parameters.Add("suploc", SqlDbType.NChar).Value = DataGridView1.Rows(i).Cells(3).Value

                i = command.ExecuteNonQuery()
            Next

            MessageBox.Show("CUSTOMER REGISTERED SUCCESFULLY")
            connection.Close()
            command.Dispose()
        End If
    End If
End Sub

Upvotes: 3

Views: 4393

Answers (1)

Mary
Mary

Reputation: 15091

  1. in your first If statement use OrElse instead of Or. This will stop the evalution as soon as a True is detected.
  2. Keep your connections local. They should be opened at the last minute and closed and disposed as soon as posible. A Using...End Using block will take care of that.
  3. If all you need is the count then ask for just the count. Don't retrieve data that you don't use. No DataReader necessary. Just use ExecuteScalar.
  4. Good job using Parameters!
  5. The names of your parameters that you use in your Sql query must match your names when you add the parameters. (Not true for Access where the order of parameter addition is the only thing that matters.)
  6. Now to the real problem Dim i As Integer = 0 Then you use DataGridView1.Rows(i).Cells(0).Value in the For Each loop. Row is always the first row and it never changes in your loop. That is why "it sends only 1 row"
  7. You don't want to keep adding parameters in your For Each loop.
  8. One more thing - If subid is an auto-increment column do not send any data for this column.

Untested code. I don't have your form or your database.

Private Sub OPCode2()
    If TextBox2.Text = "" OrElse TextBox3.Text = "" OrElse TextBox4.Text = "" Then
        MessageBox.Show("PLEASE FILL ALL FIELDS")
        Return
    End If
    Dim count As Integer
    Try
        Using cn As New SqlConnection("Your connection string")
            Dim thequery As String = "select Count(*) from supplierdata where supmobile = @supmobile;"
            Using cmd1 As SqlCommand = New SqlCommand(thequery, cn)
                cmd1.Parameters.AddWithValue("@supmobile", TextBox3.Text)
                cn.Open()
                count = CInt(cmd1.ExecuteScalar())
            End Using
        End Using
    Catch ex As Exception 'You probably want to catch more specific exceptions
        'and handle them separately
        MessageBox.Show(ex.Message)
    End Try
    If count > 0 Then
        MessageBox.Show("MOBILE NO IS ALREADY REGISTERED")
        Return
    End If
    Try
        Using cn As New SqlConnection("Your connection string")
            Using cmd2 As New SqlCommand("INSERT INTO supplierdata (supid,supname,supmobile,suploc)VALUES (@supid,@supname,@supmobile,@suploc)", cn)
                cmd2.Parameters.Add("@supid", SqlDbType.Int)
                cmd2.Parameters.Add("@supname", SqlDbType.NChar)
                cmd2.Parameters.Add("@supmobile", SqlDbType.NChar)
                cmd2.Parameters.Add("@suploc", SqlDbType.NChar)
                cn.Open()
                For Each row As DataGridViewRow In DataGridView1.Rows
                    With cmd2
                        .Parameters("@supid").Value = row.Cells(0).Value
                        .Parameters("@supname").Value = row.Cells(1).Value
                        .Parameters("@supmobile").Value = row.Cells(2).Value
                        .Parameters("@suploc").Value = row.Cells(3).Value
                    End With
                    cmd2.ExecuteNonQuery()
                Next
            End Using 'You probably want to catch more specific exceptions
            'and handle them separately
        End Using
    Catch ex As Exception 'You probably want to catch more specific exceptions
        'and handle them separately
        MessageBox.Show(ex.Message)
    End Try
    MessageBox.Show("CUSTOMER REGISTERED SUCCESFULLY")
End Sub

EDIT If supid is an auto-increment column remove it form the parameters. Change Insert query to "INSERT INTO supplierdata (supname,supmobile,suploc)VALUES (@supname,@supmobile,@suploc)" Then you need to delete the lines cmd2.Parameters.Add("@supid", SqlDbType.Int) and .Parameters("@supid").Value = row.Cells(0).Value Do this only if supid is and auto-increment (identity column)

Upvotes: 5

Related Questions