I don't know why I have this error

Curious 2009

What I try to do is reading any existed row in the table 'Info' (normally only 1 row existed) then update that row immediately, but I have an error when execute at cmd2.ExecuteNonQuery() - However, if I move the If room_N > 0 Then out of the While loop & place it under lrd.Close() then it works OK! Can some one know the reason?

    Dim ra As Integer
    Dim cmd As New SqlCommand

    Dim cnt\_del As Integer
    Dim room\_N As Integer
    Dim str As String

    con.ConnectionString = strOpen        
    con.Open()

    cmd.Connection = con
    cmd.CommandText = "SELECT \* FROM Info"
    Dim lrd As SqlDataReader = cmd.ExecuteReader()

    If lrd.HasRows Then

        Try
            'Dim lwr As SqlDataReader

            While lrd.Read()
                ListBox1.Items.Clear()

                str = lrd.GetValue(0).ToString() + ","
                str = str + lrd.GetValue(1).ToString() + ","
                str = str + lrd.GetValue(2).ToString() + ","
                str = str + lrd.GetValue(3).ToString() + ","
                str = str + lrd.GetValue(4).ToString() + ","
                str = str + lrd.GetValue(5).ToString()+ ","
                str = str + lrd.GetValue(6).ToString()

                ListBox1.Items.Insert(cnt\_del, str)
                cnt\_del = cnt\_del + 1

                ListBox\_Available.Items.Clear()
                room\_N = SearchAvailableRooms(lrd.GetValue(2), lrd.GetValue(3), lrd.GetValue(4), lrd.GetValue(0), lrd.GetValue(1))

                If room\_N > 0 Then
                    cmd.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
                    cmd.ExecuteNonQuery()
                End If

            End While

            lrd.Close()


        Catch ex As Exception
            cnt\_error = cnt\_error + 1
            TextBox\_Error.Text = cnt\_error.ToString()
        End Try
    End If

    con.Close() 'Whether there is error or not. Close the connection.

DJ Matthews

What is the error you are getting? Just telling us you have an error won't help.

loyal ginger

When you use a reader, the connection is not closed while the reading is going on. Therefore you cannot use a different command associated with that connection to do the update. Try to create a different connection and command to do the update.

Curious 2009

I forgot to display the error message, here it is: Error while connecting SQL Server. There is already an open DataRedaer associate with this command which must be close first! However, since I have to update that row before scan the next row (while loop), I can't close it ... may I need another cmd2.ExecuteNonQuery()? But then I need to connect database again? Is there better command to avoid the abobe problems?

Curious 2009

You are right, however my database is already open & connect ... now I have open & connect anothe one with the same database?

con.ConnectionString = strOpenSQLDatabase
con.Open()

con2.ConnectionString = strOpenSQLDatabase
con2.Open()

Is there other way around?

loyal ginger

Since nobody else offers any new ideas, I will post the modified version here, just for your information:

    Dim ra As Integer
    Dim cmd As New SqlCommand

    Dim cnt\_del As Integer
    Dim room\_N As Integer
    Dim str As String

    con.ConnectionString = strOpen        
    con.Open()

    cmd.Connection = con
    cmd.CommandText = "SELECT \* FROM Info"

**'con2 should be declared somewhere
    con2.ConnectionString = strOpen        
    con2.Open()

'cmd2 should be declared somewhere
    cmd2.Connection = con2**
    Dim lrd As SqlDataReader = cmd.ExecuteReader()

    If lrd.HasRows Then

        Try
            'Dim lwr As SqlDataReader

            While lrd.Read()
                ListBox1.Items.Clear()

                str = lrd.GetValue(0).ToString() + ","
                str = str + lrd.GetValue(1).ToString() + ","
                str = str + lrd.GetValue(2).ToString() + ","
                str = str + lrd.GetValue(3).ToString() + ","
                str = str + lrd.GetValue(4).ToString() + ","
                str = str + lrd.GetValue(5).ToString()+ ","
                str = str + lrd.GetValue(6).ToString()

                ListBox1.Items.Insert(cnt\_del, str)
                cnt\_del = cnt\_del + 1

                ListBox\_Available.Items.Clear()
                room\_N = SearchAvailableRooms(lrd.GetValue(2), lrd.GetValue(3), lrd.GetValue(4), lrd.GetValue(0), lrd.GetValue(1))

                If room\_N > 0 Then
                    cmd.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
                    cmd.ExecuteNonQuery()
                    cmd2.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
                    cmd2.ExecuteNonQuery()
                End If

            End While

            lrd.Close()


        Catch ex As Exception
            cnt\_error = cnt\_error + 1
            TextBox\_Error.Text = cnt\_error.ToString()
        End Try
    End If

    con.Close() 'Whether there is error or not. Close the connection.
**con2.Close()**

I really don't know if there is an alternative to this. Sorry!

Curious 2009

I will use your update codes There is an alternative, but I don't know which one is better regarding of process speed (considering there are many rows existed) 1. I move the update If room_N > 0 Then outside the while loop, in the loop I will put LOOP's str values into a string array, when Loop done ---> lrd.Close() 2. Then I use a While Loop to Update all rows from the array *) This will avoid Open/Connect twice & Close twice ... but might it be a clumsy programming? Thanks for help :)

loyal ginger

If you use transactions on the second connection, the performance will be improved. I don't know how much improvement you will get. On a different database engine, I once improved the performance of a bunch of updates by over 100 times just by putting them into transactions. Your solution of saving the stuff into an array and updating after the loop is also good. Good luck!

Curious 2009

Then I will try both methods, the latter is just for curiously Thanks & ;)