Click here to Skip to main content
13,446,268 members (34,277 online)
Rate this:
Please Sign up or sign in to vote.
I'm trying to make my program go into the table in my database, called "tblEventsSigned" and then write data to a new row. However when the program gets to my Do Until loop to find an empty row is simply says there is no row at position 0 and I can't seem to fix it. Please help!

SQL = "SELECT * FROM tblEventsSigned"
       DataAdapter = New OleDb.OleDbDataAdapter(SQL, Connection)
       DataAdapter.Fill(DataSet, "tblEventsSigned")
       Dim CurrentRow As Integer = 1     'sets the current row to the first one in the database before it begins to search for an empty row

       Do Until DataSet.Tables("tblEventsSigned").Rows(CurrentRow).Item(1).ToString.Length = 0 'finds empty place
           CurrentRow += 1

       Do Until lst_selectedEvents.Items.Count = 0
           DataSet.Tables("tblEventsSigned").Rows(CurrentRow).Item(1) = Username
           DataSet.Tables("tblEventsSigned").Rows(CurrentRow).Item(2) = lst_selectedEvents.SelectedItem.ToString
           DataAdapter.Update(DataSet, "tblEventsSigned")

This is the code I use to start the connection.

DatabaseProv = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"   'sets up provider for db
        Database = "\House Events.accdb"
        Folder = "P:\6th Form Computing\09OlsenH\COMP4 Form\COMP4\bin\Debug" 'where the db is (my documents)
        DatabasePath = Folder & Database
        DatabaseSource = "Data Source = " & DatabasePath
        Connection.ConnectionString = DatabaseProv & DatabaseSource

im Connection As New OleDbConnection      'object used for connection
    Dim DatabaseProv As String
    Dim DatabaseSource As String
    Dim Folder As String
    Dim Database As String
    Dim DatabasePath As String
    Dim DataSet As New DataSet
    Dim DataAdapter As New OleDbDataAdapter
    Dim SQL As String

What I have tried:

Changing the code so that the variables are in the actual subroutine and then opening the connection there.
Posted 8-Mar-16 5:31am
Updated 8-Mar-16 5:47am
CHill60 8-Mar-16 11:48am
Just add a new row, there is no need to find an "empty space"

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Array indexes start at zero, not one for starters, so this:
Dim CurrentRow As Integer = 1
Do Until DataSet.Tables("tblEventsSigned").Rows(CurrentRow).Item(1).ToString.Length = 0 'finds
Will always start at the second row, not the first. If there are no rows, it will fail.

Other than that, you need to start by looking at your data: we don't have it so we can't run your code in the same way you can.
So use the debugger: put a breakpoint at the top of the method that shows the problem and run your app. When it enters the method, it will stop and pass control to you. Single step through your code line by line, looking at the variables in the debugger and working out what should happen before you execute each row. Did it happen exactly as you expected? If so, more on. If not, why not? It should become fairly obvious where the mistake is , but as I said - we can't run your code so you will have to start looking!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01-2016 | 2.8.180314.2 | Last Updated 8 Mar 2016
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100