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.

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

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.

Edited 1 Year Ago by JerrimePatient

Comments
Perfectly pointing out the cause of the exception.

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.

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

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

Edited 1 Year Ago by artemix22

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

This question has already been answered. Start a new discussion instead.