How to display my sql 'Count' result...

[JavaFish]

Hi

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?

Thanks....

[hollystyles]

the ExecuteScalar method returns an object (in VB.NET in VB6 I can't remember perhaps a variant) not a reader. The object is the value in the first column of the first row of the odbc result set.

Also your code doesn't look like valid VB.NET to me. Don't you have to declare the return type of the function? it's looking like VB6 or VBA syntax to me, especially with the brackets missing from the end of ExecuteScalar. Are you in the right forum?

[JavaFish]

Thanks for the reply. I am using VB.net but just messing up my syntax I'm afraid!

I was using executescalar() was initially I was doing a Count, so point taken for my revised sql statement I should change this to ExecuteReader()

I will continue looking at this, but thanks for he response