I am trying to get to grips with querying an access database and displaying the information. I have started with a coun sql command to just return the number of rows but wanted to display this to the screen but each time it shows as null - there are records in my db so either my sql is wrong or a msgbox will not retrieve the data as perhaps the data is already lost by the time I try calling the msgbox?
My code is:
Function TotalOutOfSpec() Dim conn = New Odbc.OdbcConnection ' connectionstring using session variable ConnString to connect to user's database conn.ConnectionString = Session.Item("ConnString") Dim sql = "SELECT count (*) FROM TestResults WHERE OutofSpec = 'True'" Dim command As New Odbc.OdbcCommand(sql, conn) Dim returnValue As Odbc.OdbcDataReader Try 'Open the connection conn.Open() returnValue = command.ExecuteScalar TotalOutOfSpec = returnValue 'close and dispose of the connection to the database conn.close() conn.dispose() command.Dispose() returnValue.Close() Catch ex As Exception End Try End Function
I then try and display it using:
MsgBox("Record count: " & OutOfSpec)
But it always shows as nothing!
I am sure I am missing something obvious(well to everyone else but not me!).
Can anyone help...?
Maybe it is easier to display in a list?