Add record to database

Question

Hi to all,

i keep getting this error after i clicked the update button.

There is no row at position -1.

this is the code 

Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("Database2").Rows(inc).Item(1) = SO_TypeComboBox.Text
        ds.Tables("Database2").Rows(inc).Item(2) = SO_DateTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(3) = SO_CtrlNoTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(4) = SO_SoNoTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(5) = SO_NameTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(6) = SO_LicenseTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(7) = SO_ExpTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(8) = SO_DetachmentComboBox.Text
        ds.Tables("Database2").Rows(inc).Item(9) = SO_AddressTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(10) = SO_EffectivityTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(11) = SO_NoteTextBox.Text
        da.Update(ds, "Database2")
        MsgBox("Data updated")
    End Sub

please help me.the connection is alright and i want to add a record to my database. but this error keep showing. Thank you

TheJocker

---

jocker

Answer 1

Hello,

I cannot see from your code where the inc variable is being initialized. I think that's the problem. I suggest to set a break point in the click event and check the value of inc. You should also check that the Items are selected as you want them to be (usually the indexes start from 0)

---

Bianca

Answer 2

Hello TheJocker,

Hi to all,

i keep getting this error after i clicked the update button.

There is no row at position -1.

this is the code 

Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("Database2").Rows(inc).Item(1) = SO_TypeComboBox.Text
        ds.Tables("Database2").Rows(inc).Item(2) = SO_DateTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(3) = SO_CtrlNoTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(4) = SO_SoNoTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(5) = SO_NameTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(6) = SO_LicenseTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(7) = SO_ExpTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(8) = SO_DetachmentComboBox.Text
        ds.Tables("Database2").Rows(inc).Item(9) = SO_AddressTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(10) = SO_EffectivityTextBox.Text
        ds.Tables("Database2").Rows(inc).Item(11) = SO_NoteTextBox.Text
        da.Update(ds, "Database2")
        MsgBox("Data updated")
    End Sub

please help me.the connection is alright and i want to add a record to my database. but this error keep showing. Thank you

TheJocker

---

jocker

I do not see the enhanced variable inc, enhances like?

Regards.

---

• Carmelo La Monica
• Visual Basic Tips e Tricks Blog
• WordPress.com Blog
• Twitter
• Blogger
• CrystalwebDotNetGroup

Answer 3

hello sir here's the declaration of inc

Public Class Form1

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim inc As Integer
    Dim MaxRows As Integer

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source=C:\Users\PC17\Documents\Database2.accdb"
        con.ConnectionString = dbProvider & dbSource
        con.Open()
        sql = "SELECT * FROM Table1"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Database2")
        'MsgBox("Database is now open")
        con.Close()
        'MsgBox("Database is now Closed")
        MaxRows = ds.Tables("Database2").Rows.Count
        inc = -1
    End Sub
    Private Sub navigaterecords()
        SO_TypeComboBox.Text = ds.Tables("Database2").Rows(inc).Item(1)
        SO_DateTextBox.Text = ds.Tables("Database2").Rows(inc).Item(2)
        SO_CtrlNoTextBox.Text = ds.Tables("Database2").Rows(inc).Item(3)
        SO_SoNoTextBox.Text = ds.Tables("Database2").Rows(inc).Item(4)
        SO_NameTextBox.Text = ds.Tables("Database2").Rows(inc).Item(5)
        SO_LicenseTextBox.Text = ds.Tables("Database2").Rows(inc).Item(6)
        SO_ExpTextBox.Text = ds.Tables("Database2").Rows(inc).Item(7)
        SO_DetachmentComboBox.Text = ds.Tables("Database2").Rows(inc).Item(8)
        SO_AddressTextBox.Text = ds.Tables("Database2").Rows(inc).Item(9)
        SO_EffectivityTextBox.Text = ds.Tables("Database2").Rows(inc).Item(10)
        SO_NoteTextBox.Text = ds.Tables("Database2").Rows(inc).Item(11)
    End Sub

---

jocker

Answer 4

Hi TheJocker,

Welcome to the MSDN forum.

I think you have got what the issue is.

You have declared the inc as global variable, and set the value of inc to -1. When you use inc in navigaterecords method, the value will be not changed.The parameter for datatable.rows property is the index of the row in datatable. The index number begins with 0, and there is no -1 index row provided for you. So you get the exception.

Apart from this, you also have another issue in your code post in the original post.

You want to use command builder to update the data in database. But you forget to use the command builder to “create” the update command after you change the value to datatable. Add the following code before the line( da.Update(ds, "Database2"))  :

cb.GetUpdateCommand()

Hope this helps.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 5

Thanks a lot sir mark liu, it works

---

jocker