Datagridview items to Database

[wilsonchama]

Hi everyone,

I have been strugling wit the "INSERT INTO" statement.

I intend to insert the items that are in a datagridview into An Access Database when a button is clicked. I am using VB.NET 2008 express and Microsoft acces 2007.
At first, I tried to use the listview, but could not get anything done, and so I changed and now I am using a Datagridview.

Below is the code I have so far.

Private Sub btnCpltSale_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCpltSale.Click

    Dim Description As String
    Dim proValue As Decimal
      Dim Datesold As Date
      Dim sql As String

      myConnToAccess.Open()


        For Sale As Integer = 0 To DataGridView1.Rows.Count
            Description = DataGridView1.Rows(Sale).Cells(1).Value
            proValue = DataGridView1.Rows(Sale).Cells(2).Value
            Datesold = DataGridView1.Rows(Sale).Cells(3).Value

            sql = "INSERT INTO tblSales(proDescription, SaleValue, transDate)VALUES (@productname, @productValue, @saleDate)"

            Dim Comm As New OleDbCommand(sql, myConnToAccess)
            Comm.Parameters.AddWithValue("@productname", Description)
            Comm.Parameters.AddWithValue("@productValue", proValue)
            Comm.Parameters.AddWithValue("@saleDate", Datesold)

            Comm.ExecuteNonQuery()
            Comm.Dispose()

        Next
        myConnToAccess.Close()
    End Sub

When the button is clicked, I am getting an error message saying "parameter "@productname" has no default value.However, despite the error, when I check the table, the record is being created successfully. All I do not know is why the error occuring and how to get rid of it.

Any help will greatly be appreciated.

[djjeavons]

Hi

Usually this is caused by not providing a value for a field that is set to not allow Nulls (i.e. a required field). However, I am surprised that data is being added.

To get rid of the error, provide some validation before inserting the data to ensure that all required data is provided.

HTH

[JerrimePatient]

change line 11

from
For Sale As Integer = 0 To DataGridView1.Rows.Count

to
For Sale As Integer = 0 To DataGridView1.Rows.Count - 1

that will solve the problem.

[djjeavons]

While I agree that you should be using .Count - 1 (and something that I missed), I don't believe that this is the root cause of the problem. The reason being is that if the DataGridView had the ability to add new rows then you would get a NullReferenceException. If the ability to add new rows was disabled you would get an ArgumentOutOfRangeException.

However, the OP stated that the exception was regarding the parameter (and therefore an OleDbException), so I am assuming that once that is fixed, then they will also need to implement the above suggestion.

[wilsonchama]

I have tried that Jerrime Patient, but it still is not working. The line now looks as show below.

 For Sale As Integer = 0 To DataGridView1.Rows.Count - 1

[artemix22]

is "Enable Adding" on your DGV properties checked to true?

if yes, then you have to use Count - 2
if no, then you have to use Count -1

[wilsonchama]

Thank you so very much dear. This has really made it work. I can't believe it. Thank you so much once again.