Member Avatar for 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....

  • 2 Contributors
  • 2 Replies
  • 611 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by JavaFish

Dani AI

Generated

A few concrete points to get this working for an ASP.NET/VB.NET page.

As hinted, a COUNT(*) query should be read with ExecuteScalar (it returns a single value, not a reader). , your function also needs an explicit return type and a safe way to convert the scalar object into an Integer. Important: using MsgBox on the server is not appropriate for ASP.NET — it won’t show in the browser. Put the result into a page control (Label) or write it to the response instead.

Example corrected function (uses Using so the connection/command are disposed, returns Integer and handles DBNull):

Function TotalOutOfSpec() As Integer
    Dim sql As String = "SELECT COUNT(*) FROM TestResults WHERE OutOfSpec = 'True'"
    Using conn As New Odbc.OdbcConnection(Session.Item("ConnString").ToString())
        Using cmd As New Odbc.OdbcCommand(sql, conn)
            conn.Open()
            Dim obj As Object = cmd.ExecuteScalar()
            If obj Is Nothing OrElse IsDBNull(obj) Then
                Return 0
            End If
            Return Convert.ToInt32(obj)
        End Using
    End Using
End Function

How to show it on the page (server side):

Dim count As Integer = TotalOutOfSpec()
LabelCount.Text = count.ToString()

Troubleshooting tips: don’t swallow exceptions — log or surface ex.Message so you can see SQL/connection errors. If the field is a boolean (Access), use WHERE OutOfSpec = True (no quotes); for MySQL booleans use = 1 or = TRUE. If results are still unexpected, run the same COUNT query in your DB client to confirm the data and the SQL literal/type you should use.

Recommended Answers

All 2 Replies

Member Avatar for 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?

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.