How to handle the error " MySQL :: invalid attempt to access a field before calling "

[cjjack88]

Hi, i am using vb.net2008 and mySql 5.1.36 to do an assignment. I need to count the number of seat available in a table in my database. When i run my code, the error
MySQL :: invalid attempt to access a field before calling Read()
occurred in the code below.

no_seat_available = result2.GetString(result2.GetOrdinal("myCount"))

This is the coding.

Private Sub updateAll()

        If Not conn Is Nothing Then conn.Close()

        Dim _host As String = "localhost"
        Dim _user As String = "root"
        Dim _pass As String = ""
        Dim _database As String = "cinema.vb"

        conn = New MySqlConnection("Server= " + _host + ";User Id=" + _user + ";Password=" + _pass + ";database = " + _database)
        conn2 = New MySqlConnection("Server= " + _host + ";User Id=" + _user + ";Password=" + _pass + ";database = " + _database)
        ListView1.Columns.Clear()

        With ListView1
            .Items.Clear()
            .FullRowSelect = True
            .View = View.Details
            .GridLines = True
            .Sorting = Windows.Forms.SortOrder.Ascending
            .Columns.Add("ID", 60, HorizontalAlignment.Left)
            .Columns.Add("Title", 350, HorizontalAlignment.Left)
            .Columns.Add("Price", 50, HorizontalAlignment.Left)
            .Columns.Add("Genre", 200, HorizontalAlignment.Left)
            .Columns.Add("Rate", 50, HorizontalAlignment.Left)
            .Columns.Add("Length", 50, HorizontalAlignment.Left)
            .Columns.Add("Show Time", 70, HorizontalAlignment.Left)
            .Columns.Add("Show Date", 200, HorizontalAlignment.Left)
            .Columns.Add("Hall no.", 50, HorizontalAlignment.Left)
            .Columns.Add("No. Seat Available", 80, HorizontalAlignment.Left)
        End With

        Dim NewItem As ListViewItem

        Dim movie_id As String
        Dim movie_title As String
        Dim movie_price As Integer
        Dim movie_genre As String
        Dim movie_rate As String
        Dim movie_length As String
        Dim movie_time As Integer
        Dim movie_date As String
        Dim movie_hall As Integer
        Dim no_seat_available As Integer

        Dim count As Integer

        Dim result As MySqlDataReader
        result = Nothing
        Dim result2 As MySqlDataReader
        result2 = Nothing

        Dim cmd As New MySqlCommand
        Dim cmd2 As New MySqlCommand

        Try
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        With cmd
            .CommandText = "SELECT * FROM movietable"
            .Connection = conn
        End With

        conn.Open()
        result = cmd.ExecuteReader


        With ListView1
            .Items.Clear()

            Do While result.Read

                movie_id = result.GetString(result.GetOrdinal("id"))
                movie_title = result.GetString(result.GetOrdinal("title"))
                movie_price = result.GetString(result.GetOrdinal("price"))
                movie_genre = result.GetString(result.GetOrdinal("genre"))
                movie_rate = result.GetString(result.GetOrdinal("rate"))
                movie_length = result.GetInt32(result.GetOrdinal("movieLength"))
                movie_time = result.GetInt32(result.GetOrdinal("showTime"))
                movie_date = result.GetString(result.GetOrdinal("showDate"))
                movie_hall = result.GetInt32(result.GetOrdinal("hallNo"))

                With cmd2
                    .CommandText = "SELECT COUNT(status) AS myCount FROM seatstatus WHERE status = '1' AND m_Id = @mId"
                    .Connection = conn2
                    .Parameters.Clear()
                    .Parameters.AddWithValue("@mId", movie_id)
                End With

                conn2.Open()
                result2 = cmd2.ExecuteReader

                no_seat_available = result2.GetString(result2.GetOrdinal("myCount"))
                conn2.Close()

                NewItem = New ListViewItem(movie_id)
                NewItem.SubItems.Add(movie_title)
                NewItem.SubItems.Add(movie_price)
                NewItem.SubItems.Add(movie_genre)
                NewItem.SubItems.Add(movie_rate)
                NewItem.SubItems.Add(movie_length)
                NewItem.SubItems.Add(movie_time)
                NewItem.SubItems.Add(movie_date)
                NewItem.SubItems.Add(movie_hall)
                NewItem.SubItems.Add(no_seat_available)
                .Items.Add(NewItem)

                count = count + 1

            Loop
        End With

        conn.Close()

    End Sub

Please help me to get through this.

Thank you.
Cjjack88

[TomW]

If your only returning a single value, you do not need to use a reader object or the ExecuteReader method. I would suggest using ExecuteScalar instead, it will return the singular value and use less resources. The help file will show detailed info with an example. If you still need help just let me know. :)

[cjjack88]

ok, i try it 1st. Thank you for reply.

[cjjack88]

Thank you TomW, it is works.

[TomW]

Great, glad it helped.