Runtime error: Invalid attempt to read when no data is present

[Matthew Louden]

I want to read how many records in the table, and insert a record with id
field which increment the counter by 1. However, I had the following runtime
on Dim s As Integer = CInt(dr("t")). Since "t" (I want to represent the
count, but just a tempoary variable, not a field in table) doesnt exist in
the table


sqlStmt = "SELECT COUNT(*) As t FROM TimeSlot;"
cmd = New SqlCommand(sqlStmt, conn)
cmd.Connection.Open()
dr = cmd.ExecuteReader
Dim s As Integer = CInt(dr("t")) 'Runtime error: Invalid attempt to read
when no data is present.


any ideas?? please advise!

 

[Paul Glavich]

dr = cmd.ExecuteReader
Dim s As Integer
if Not dr.IsDBNull(0) then
s = dr.GetInt32(0)
EndIf

- Paul Glavich

 

[Val Mazur]

Hi,

You should use GetInt32 method to get integer value and you should call Read
before starting fetching data. Also you need to specify column index, not
name

Dim s As Integer
While dr.Read
s = dr.GetInt32(0)
end While

Anyway I do not like an idea to handle identity field this way.Why not to
declare your actual field as an IDENTITY in a database? I this case you
should not worry about next ID. Otherwise you could get into trouble if some
records are deleted from the table. For example, If you had two records with
IDs 1 and 2 and then deleted record with ID 1, your SQL statement will
return count 1 and next ID will be 2, but you already have it.