Results 1 to 6 of 6

Thread: Database Error There is no row at position 2

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Database Error There is no row at position 2

    Hi, I'm trying to fill some text boxes and a listbox. I have this code:

    VB.NET Code:
    1. DatabaseOperations databaseOperations = new DatabaseOperations();
    2.  
    3. dataTable = new DataTable();
    4. dataTable = databaseOperations.DBNextRecord();
    5.  
    6. FillBoxes("next_record");
    7.  
    8. dataTable = null;
    9. dataTable = new DataTable();
    10. dataTable = databaseOperations.DBAllRecords();
    11.  
    12. // Liste öğesini seç
    13. if (dataTable.Rows.Count >= 1) // Check if the DataTable returns any data from database
    14. {
    15.     if (!DBNull.Value.Equals(dataTable.Rows[currentId]["id"]))
    16.     {
    17.         Lbx_LeftList.SelectedValue = dataTable.Rows[currentId]["id"];
    18.     }
    19. }

    This gives me error: 'There is no row at position 2.' for this line: if (!DBNull.Value.Equals(dataTable.Rows[currentId]["id"])) When I debug, I see that the data table has 2 rows and currentId equals to 2.

    The data table is set like this

    VB.NET Code:
    1. public DataTable DBNextRecord()
    2. {
    3.     connectionString.DataSource = "database.sqlite";
    4.     connectionString.ForeignKeys = true;
    5.     connectionString.JournalMode = SQLiteJournalModeEnum.Wal;
    6.  
    7.     System.Console.WriteLine(connectionString.ToString());
    8.  
    9.     sqlite_conn = new SQLiteConnection(connectionString.ToString());
    10.  
    11.     selectQueryString = @"SELECT
    12.                               S.id,
    13.                               P.txt_press,
    14.                               CA.txt_category,
    15.                               S.txt_repertory_no,
    16.                               T.txt_taken_from,
    17.                               S.txt_revision_date1,
    18.                               S.txt_revision_date2,
    19.                               S.txt_revision_date3,
    20.                               R.txt_region,
    21.                               PR.txt_prepared_by,
    22.                               S.txt_research_date1,
    23.                               S.txt_research_date2,
    24.                               S.txt_research_date3,
    25.                               SP.txt_source_by,
    26.                               M.txt_measure,
    27.                               S.txt_time,
    28.                               C.txt_compiled_by,
    29.                               S.txt_compilation_date,
    30.                               N.txt_notation_by,
    31.                               S.txt_mp3_paths,
    32.                               A.txt_artist,
    33.                               S.txt_song_name,
    34.                               S.txt_lyrics,
    35.                               S.image1,S.image2,S.image3,S.image4,S.image5,S.image6,S.image7,
    36.                               S.image8,S.image9, S.image10,S.image11,S.image12,S.image13,S.image14
    37.                           FROM tbl_sheet S
    38.                           LEFT JOIN tbl_category CA
    39.                               ON CA.id = S.int_category_id
    40.                           LEFT JOIN tbl_press P
    41.                               ON P.id = S.int_press_id
    42.                           LEFT JOIN tbl_taken_from T
    43.                               ON T.id = S.int_taken_from_id
    44.                           LEFT JOIN tbl_prepared_by PR
    45.                               ON PR.id = S.int_prepared_by_id
    46.                           LEFT JOIN tbl_region R
    47.                               ON R.id = S.int_region_id
    48.                           LEFT JOIN tbl_source_by SP
    49.                               ON SP.id = S.int_source_by_id
    50.                           LEFT JOIN tbl_measure M
    51.                               ON M.id = S.int_measure_id
    52.                           LEFT JOIN tbl_compiled_by C
    53.                               ON C.id = S.int_compiled_by_id
    54.                           LEFT JOIN tbl_notation_by N
    55.                               ON N.id = S.int_notation_by_id
    56.                           LEFT JOIN tbl_artist A
    57.                               ON A.id = S.int_artist_id;";
    58.  
    59.     sqlite_conn.Open();
    60.  
    61.     sqliteDataAdapter = new SQLiteDataAdapter(selectQueryString, sqlite_conn.ConnectionString);
    62.     // sqliteCommandBuilder = new SQLiteCommandBuilder(sqliteDataAdapter);
    63.  
    64.     dataTable = new DataTable();
    65.     sqliteDataAdapter.Fill(dataTable);
    66.  
    67.     sqlite_conn.Close();
    68.     sqliteDataAdapter.Dispose();
    69.  
    70.     return dataTable;
    71. }

    How can I fix this?
    Last edited by nikel; Jun 24th, 2018 at 03:26 PM. Reason: I corrected code
    I'm not a man of too many faces
    The mask I wear is one

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Database Error There is no row at position 2

    With arrays/collections/etc, the first element is usually 0, and second element is 1... so by trying to access element '2', you are trying to get the third row.

    You haven't indicated how/why you set currentId, but it sounds like you should either set it one lower, or use [currentId - 1] in the code you showed.

    You should also change the If statement so that it checks against currentId rather than 1, eg:
    Code:
    if (dataTable.Rows.Count >= currentId - 1)
    (I used -1 based on the assumption you are going to change to [currentId - 1] ).

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Database Error There is no row at position 2

    Hi thanks for the reply. If I set them to

    Code:
    dataTable.Rows[currentId - 1]["id"]
    I get error
    Code:
    Exception thrown: 'System.InvalidCastException' in mscorlib.dll
    Object cannot be cast from DBNull to other types.
    I'm not a man of too many faces
    The mask I wear is one

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Database Error There is no row at position 2

    Well that's an improvement, as now you are at least accessing a row... but there is something wrong with how you are doing that.

    Your original code included a check for DBNull, so your amended version should work too if you altered it correctly. Your current code should look like the following:
    Code:
        if (!DBNull.Value.Equals(dataTable.Rows[currentId - 1]["id"]))
        {
            Lbx_LeftList.SelectedValue = dataTable.Rows[currentId - 1]["id"];
        }
    Assuming you are using that, and the error still occurs, which line does it happen on?

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Database Error There is no row at position 2

    I can't tell which line it occurs at, actually it's not an error but timer I use hangs. Here's some more console output

    Code:
    'thm_notalar.exe' (CLR v4.0.30319: thm_notalar.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml.Linq\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.Linq.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    data source=database.sqlite;foreign keys=True;journal mode=Wal
    data source=database.sqlite;foreign keys=True;journal mode=Wal
    data source=database.sqlite;foreign keys=True;journal mode=Wal
    The thread 0x11e4 has exited with code 0 (0x0).
    The thread 0x1678 has exited with code 0 (0x0).
    Exception thrown: 'System.InvalidCastException' in mscorlib.dll
    Object cannot be cast from DBNull to other types.
    Exception thrown: 'System.InvalidCastException' in mscorlib.dll
    Object cannot be cast from DBNull to other types.
    The program '[4800] thm_notalar.exe' has exited with code 0 (0x0).
    I'm not a man of too many faces
    The mask I wear is one

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Database Error There is no row at position 2

    You are obviously accessing nullable data and ignoring the fact that it might be null. You need to take a good look at all the locations that you are getting data from a DataRow and make sure that, if the field you're accessing is nullable, you are allowing for it to be null.

    One of the simplest ways to do that is to use the Field(Of T) extension method. Here's how you can allow for null without that method:
    vb.net Code:
    1. Dim text As String
    2. Dim number As Integer?
    3.  
    4. If Not myDataRow.IsNull("Text") Then
    5.     text = CStr(myDataRow("Text"))
    6. End If
    7.  
    8. If Not myDataRow.IsNull("Number") Then
    9.     number = CInt(myDataRow("Number"))
    10. End If
    Here's how you can do the same with that method:
    vb.net Code:
    1. Dim text = myDataRow.Field(Of String)("Text")
    2. Dim number = myDataRow.Field(Of Integer?)("Number")
    In both cases, the 'text' and 'number' variables will be Nothing if the corresponding field in the DataRow contains DBNull.Value, otherwise they will be set to the appropriate value as the appropriate type. Just note that, when working with value types like Integer, you need to use nullable versions. That's why that code uses 'Integer?', which is shorthand for 'Nullable(Of Integer)', rather than just 'Integer'. If you don't know how to work with nullable value types then you should do some reading on that. As a quick primer, in this case:
    vb.net Code:
    1. Dim num1 As Integer?
    2. Dim num2 As Integer? = Nothing
    3. Dim num3 As Integer? = 100
    the HasValue properties of 'num1', 'num2' and 'num3' would be False, False and True respectively, which means that getting the Value properties of 'num1' and 'num2' would throw exceptions while the Value property of 'num3' would be 100.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width