Updating database from Datagrid

[smitty68503]

I am currently stuck on a project where I am populating a datagrid from a series of labels and textboxes. I have that part down. Now for the part where I actually add it do the database, I am having a problem. It is actually adding everything to the database just like I want it but when it finishes adding the data from the datagridview, I am getting an error saying there are no values. I have tried numerous things and nothing helps.

I have included the code I have been working with. I appreciate any help I can get on this.

Thanks

Private Sub btnsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsubmit.Click


Dim I As Integer
        For I = 0 To DataGridView1.Rows.Count - 1
            Dim itemname As String = DataGridView1.Rows(I).Cells(0).Value
            Dim quantity As String = DataGridView1.Rows(I).Cells(1).Value
            Dim totalcost As String = DataGridView1.Rows(I).Cells(2).Value
            Dim barcode As String = DataGridView1.Rows(I).Cells(3).Value
            Dim ordernumber As String = DataGridView1.Rows(I).Cells(4).Value
            Dim orderdate As String = DataGridView1.Rows(I).Cells(5).Value
            Dim buyinprice As String = DataGridView1.Rows(I).Cells(6).Value
            Dim saleprice As String = DataGridView1.Rows(I).Cells(7).Value
            Dim vendorname As String = DataGridView1.Rows(I).Cells(8).Value
            Dim status As String = DataGridView1.Rows(I).Cells(9).Value
            Dim RepairNumber As String = DataGridView1.Rows(I).Cells(10).Value
            Dim conn As New SqlConnection("*******")
            Dim query As String = "Insert Into orders(Itemname, Quantity, totalcost, barcode, ordernumber, orderdate, buyinprice, saleprice, vendorname, status, RepairNumber) values (@Itemname, @Quantity, @totalcost, @barcode, @ordernumber, @orderdate, @buyinprice, @saleprice, @vendorname, @status, @RepairNumber)"
            Dim cmd As New SqlCommand(query, conn)
            cmd.Parameters.AddWithValue("@itemname", itemname)
            cmd.Parameters.AddWithValue("@Quantity", quantity)
            cmd.Parameters.AddWithValue("@totalcost", totalcost)
            cmd.Parameters.AddWithValue("@barcode", barcode)
            cmd.Parameters.AddWithValue("@ordernumber", ordernumber)
            cmd.Parameters.AddWithValue("@orderdate", orderdate)
            cmd.Parameters.AddWithValue("@buyinprice", buyinprice)
            cmd.Parameters.AddWithValue("@saleprice", saleprice)
            cmd.Parameters.AddWithValue("@vendorname", vendorname)
            cmd.Parameters.AddWithValue("@status", status)
            cmd.Parameters.AddWithValue("@repairnumber", RepairNumber)
            conn.Open()
            cmd.Executenonquery()
            conn.Dispose()
        Next
        If I <= 0 Then
            MessageBox.Show("Items Added Fool")
            DataGridView1.Refresh()

[M.Waqas Aslam]

dim i as integer
Dim conn As New SqlConnection("*******")
conn.open()
for i = 0 to DataGridView1.rows.count - 1
Dim query As String = "Insert Into orders(Itemname, Quantity, totalcost, barcode, ordernumber, orderdate, buyinprice, saleprice, vendorname, status, RepairNumber) values (@Itemname, @Quantity, @totalcost, @barcode, @ordernumber, @orderdate, @buyinprice, @saleprice, @vendorname, @status, @RepairNumber)"
Dim cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@itemname", DataGridView1.item(0,i).value.tostring)
cmd.Parameters.AddWithValue("@Quantity", DataGridView1.item(1,i).value.tostring)
cmd.Parameters.AddWithValue("@totalcost", DataGridView1.item(2,i).value.tostring)
cmd.Parameters.AddWithValue("@barcode", DataGridView1.item(3,i).value.tostring)
cmd.Parameters.AddWithValue("@ordernumber", DataGridView1.item(4,i).value.tostring)
cmd.Parameters.AddWithValue("@orderdate", DataGridView1.item(5,i).value.tostring)
cmd.Parameters.AddWithValue("@buyinprice", DataGridView1.item(6,i).value.tostring)
cmd.Parameters.AddWithValue("@saleprice", DataGridView1.item(7,i).value.tostring)
cmd.Parameters.AddWithValue("@vendorname", DataGridView1.item(8,i).value.tostring)
cmd.Parameters.AddWithValue("@status", DataGridView1.item(9,i).value.tostring)
cmd.Parameters.AddWithValue("@repairnumber", DataGridView1.item(10,i).value.tostring)
cmd.executenonquery()
Next()
connclose()

            If I <= 0 Then
MessageBox.Show("Items Added Fool")
DataGridView1.Refresh()

hope this will works fine for you

Regards

;

[smitty68503]

Thanks for your response. I tried the code you provided and it didn't even add the data to the database. It erred out. I think am on the right track with this so far as the code is actually adding the datagridview to the database. But it errs out when it is complete. This is the error it gives:

The parameterized query '(@itemname nvarchar(4000),@Quantity nvarchar(4000),@totalcost nv' expects the parameter '@itemname', which was not supplied.

[smitty68503]

 Try
            Dim I As Integer
            For I = 0 To DataGridView1.Rows.Count - 1

                Dim itemname As String = DataGridView1.Rows(I).Cells(0).Value
                Dim quantity As String = DataGridView1.Rows(I).Cells(1).Value
                Dim totalcost As String = DataGridView1.Rows(I).Cells(2).Value
                Dim barcode As String = DataGridView1.Rows(I).Cells(3).Value
                Dim ordernumber As String = DataGridView1.Rows(I).Cells(4).Value
                Dim orderdate As String = DataGridView1.Rows(I).Cells(5).Value
                Dim buyinprice As String = DataGridView1.Rows(I).Cells(6).Value
                Dim saleprice As String = DataGridView1.Rows(I).Cells(7).Value
                Dim vendorname As String = DataGridView1.Rows(I).Cells(8).Value
                Dim status As String = DataGridView1.Rows(I).Cells(9).Value
                Dim RepairNumber As String = DataGridView1.Rows(I).Cells(10).Value
                Dim conn As New SqlConnection(*****)
                conn.Open()
                Dim query As String = "Insert Into orderstable(Itemname, Quantity, totalcost, barcode, ordernumber, orderdate, buyinprice, saleprice, vendorname, status, RepairNumber) values (@Itemname, @Quantity, @totalcost, @barcode, @ordernumber, @orderdate, @buyinprice, @saleprice, @vendorname, @status, @RepairNumber)"
                Dim cmd As New SqlCommand(query, conn)
                cmd.Parameters.AddWithValue("@itemname", itemname)
                cmd.Parameters.AddWithValue("@Quantity", quantity)
                cmd.Parameters.AddWithValue("@totalcost", totalcost)
                cmd.Parameters.AddWithValue("@barcode", barcode)
                cmd.Parameters.AddWithValue("@ordernumber", ordernumber)
                cmd.Parameters.AddWithValue("@orderdate", orderdate)
                cmd.Parameters.AddWithValue("@buyinprice", buyinprice)
                cmd.Parameters.AddWithValue("@saleprice", saleprice)
                cmd.Parameters.AddWithValue("@vendorname", vendorname)
                cmd.Parameters.AddWithValue("@status", status)
                cmd.Parameters.AddWithValue("@repairnumber", RepairNumber)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
            Next

        Catch ex As Exception
            MessageBox.Show("Items Added")
            DataGridView1.Rows.Clear()
                   End Try


    End Sub

I got this solved finally. Thanks anyway. I am just posting the solution to it in case someone else is having the same problem. Since it is entering multiple values from a datagridview, I need to use a "try" statement. This will enter all the data then go to the next statement. If anyone has an easier way to accomplish this, I would be happy to hear it.