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:
DatabaseOperations databaseOperations = new DatabaseOperations();
dataTable = new DataTable();
dataTable = databaseOperations.DBNextRecord();
FillBoxes("next_record");
dataTable = null;
dataTable = new DataTable();
dataTable = databaseOperations.DBAllRecords();
// Liste öğesini seç
if (dataTable.Rows.Count >= 1) // Check if the DataTable returns any data from database
{
if (!DBNull.Value.Equals(dataTable.Rows[currentId]["id"]))
{
Lbx_LeftList.SelectedValue = dataTable.Rows[currentId]["id"];
}
}
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:
public DataTable DBNextRecord()
{
connectionString.DataSource = "database.sqlite";
connectionString.ForeignKeys = true;
connectionString.JournalMode = SQLiteJournalModeEnum.Wal;
System.Console.WriteLine(connectionString.ToString());
sqlite_conn = new SQLiteConnection(connectionString.ToString());
selectQueryString = @"SELECT
S.id,
P.txt_press,
CA.txt_category,
S.txt_repertory_no,
T.txt_taken_from,
S.txt_revision_date1,
S.txt_revision_date2,
S.txt_revision_date3,
R.txt_region,
PR.txt_prepared_by,
S.txt_research_date1,
S.txt_research_date2,
S.txt_research_date3,
SP.txt_source_by,
M.txt_measure,
S.txt_time,
C.txt_compiled_by,
S.txt_compilation_date,
N.txt_notation_by,
S.txt_mp3_paths,
A.txt_artist,
S.txt_song_name,
S.txt_lyrics,
S.image1,S.image2,S.image3,S.image4,S.image5,S.image6,S.image7,
S.image8,S.image9, S.image10,S.image11,S.image12,S.image13,S.image14
FROM tbl_sheet S
LEFT JOIN tbl_category CA
ON CA.id = S.int_category_id
LEFT JOIN tbl_press P
ON P.id = S.int_press_id
LEFT JOIN tbl_taken_from T
ON T.id = S.int_taken_from_id
LEFT JOIN tbl_prepared_by PR
ON PR.id = S.int_prepared_by_id
LEFT JOIN tbl_region R
ON R.id = S.int_region_id
LEFT JOIN tbl_source_by SP
ON SP.id = S.int_source_by_id
LEFT JOIN tbl_measure M
ON M.id = S.int_measure_id
LEFT JOIN tbl_compiled_by C
ON C.id = S.int_compiled_by_id
LEFT JOIN tbl_notation_by N
ON N.id = S.int_notation_by_id
LEFT JOIN tbl_artist A
ON A.id = S.int_artist_id;";
sqlite_conn.Open();
sqliteDataAdapter = new SQLiteDataAdapter(selectQueryString, sqlite_conn.ConnectionString);
// sqliteCommandBuilder = new SQLiteCommandBuilder(sqliteDataAdapter);
dataTable = new DataTable();
sqliteDataAdapter.Fill(dataTable);
sqlite_conn.Close();
sqliteDataAdapter.Dispose();
return dataTable;
}
How can I fix this?
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] ).
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.
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?
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).
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:
Dim text As String
Dim number As Integer?
If Not myDataRow.IsNull("Text") Then
text = CStr(myDataRow("Text"))
End If
If Not myDataRow.IsNull("Number") Then
number = CInt(myDataRow("Number"))
End If
Here's how you can do the same with that method:
vb.net Code:
Dim text = myDataRow.Field(Of String)("Text")
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:
Dim num1 As Integer?
Dim num2 As Integer? = Nothing
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.