Click here to Skip to main content
15,997,776 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Error:
"There is already an open DataReader associated with this Command which must be closed first"


If the menu name is already exists in the database, display an error message else insert menu name.

What I have tried:

VB
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
Posted
Updated 8-Mar-18 16:42pm
v2
Comments
PIEBALDconsult 8-Mar-18 22:27pm    
Why are you even using a DataReader? Try ExecuteScalar to get a Count of matching rows instead.
John Th 8-Mar-18 22:42pm    
Thanks brother. Fixed.

1 solution

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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900