Wsrtjtyk

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

1. in your first If statement use OrElse instead of Or. This will stop the evalution as soon as a False 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)