John Th Ответов: 1

Как я исправил ошибку tbese?


Ошибка:
"There is already an open DataReader associated with this Command which must be closed first"


Если имя меню уже существует в базе данных, отобразите сообщение об ошибке else insert menu name.

Что я уже пробовал:

Public Sub addMenu()
        Try
            dbConnection()
            search_query = "SELECT * FROM tblfood_menu WHERE menu_name = @menu_name;"
            command = New SqlCommand
            With command
                .Connection = connection
                .CommandText = search_query
                .Parameters.Clear()
                .Parameters.Add(New SqlParameter With {.ParameterName = "@menu_name", .SqlDbType = SqlDbType.VarChar, .Value = formFoodMenu.txtMenuName.Text})
                dataReader = command.ExecuteReader()
                If dataReader.HasRows Then
                    MsgBox("Menu name is already exists!", MsgBoxStyle.Exclamation, "Add Menu")
                Else
                    insert_query = "INSERT INTO tblfood_menu(menu_name) VALUES(@menu_name);"
                    command = New SqlCommand
                    With command
                        .Connection = connection
                        .CommandText = insert_query
                        result = .ExecuteNonQuery()
                        If result = 0 Then
                            MsgBox("Error in adding menu!", MsgBoxStyle.Exclamation)
                        Else
                            MsgBox("Successfully added menu!", MsgBoxStyle.Information)
                        End If
                    End With
                End If
            End With
        Catch ex As SqlException
            MsgBox("Error: " + ex.Message)
        Finally
            connection.Close()
            command.Dispose()
        End Try
    End Sub

PIEBALDconsult

Почему вы вообще используете DataReader? Вместо этого попробуйте ExecuteScalar, чтобы получить количество совпадающих строк.

John Th

Спасибо, брат. Исправлено.

1 Ответов

Рейтинг:
6

John Th

With command
                .Connection = connection
                .CommandText = search_query
                .Parameters.Clear()
                .Parameters.Add(New SqlParameter With {.ParameterName = "@menu_name", .SqlDbType = SqlDbType.VarChar, .Value = formFoodMenu.txtMenuName.Text})
                result = .ExecuteScalar()
                If result > 0 Then
                    MsgBox("Menu name is already exists!", MsgBoxStyle.Exclamation, "Add Menu")
                Else
                    insert_query = "INSERT INTO tblfood_menu(menu_name) VALUES(@menu_name);"
                    command = New SqlCommand
                    With command
                        .Connection = connection
                        .CommandText = insert_query
                        .Parameters.Clear()
                        .Parameters.Add(New SqlParameter With {.ParameterName = "@menu_name", .SqlDbType = SqlDbType.VarChar, .Value = formFoodMenu.txtMenuName.Text})
                        result = .ExecuteNonQuery()
                        If result = 0 Then
                            MsgBox("Error in adding menu!", MsgBoxStyle.Exclamation)
                        Else
                            MsgBox("Successfully added menu!", MsgBoxStyle.Information)
                        End If
                    End With
                End If
            End With