Visual Basic 2010 and Mysql Nested Query 1

[lexer]

Hi

I'm using VB2010 Express, I've got a Mysql tablet with the following Fields Ext, CostAssing and CostAct, I'm trying to update a Mysql table from the result got in a first query:

- First query Gets Ext and CostAssing
- Second Query Update field CostAct with CostAssing value

Imports MySql.Data.MySqlClient
Imports System
Imports System.IO
Public Class Form1
    Public dbconn As New MySqlConnection
    Public sql As String
    Public sqlQuery As String
    Public SQLcmd As MySqlCommand
    Public dbcomm As MySqlCommand
    Public dbread As MySqlDataReader
    Dim Ext As String
    Dim CostAssing As Integer

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        dbconn = New MySqlConnection("Data Source=localhost ; user id=root ; password=password ; database=calls")
        'First Query Get Extension and CostAssing
          Try
            dbconn.Open()
            sqlQuery = "SELECT Ext,CostAssing FROM estruc "
            SQLcmd = New MySqlCommand(sqlQuery, dbconn)
            dbread = SQLcmd.ExecuteReader
            While dbread.Read()
                Ext = dbread.Item("Ext")
                CostAssing = dbread.Item("CostAssing")
                MsgBox("Ext:" & Ext)
                'Second Query Update Ext from CostAct to CostAssing
                Try
                    sqlQuery = "UPDATE estruc SET CostAct = '" & "1000" & "'  WHERE Ext = '" & Ext & "'"
                    SQLcmd = New MySqlCommand(sqlQuery, dbconn)
                    dbread = SQLcmd.ExecuteReader
                Catch ex As Exception
                    MsgBox("Error 2 is :" & ex.Message)
                End Try
            End While
        Catch ex As Exception
            MsgBox("Error 1 is :" & ex.Message)
        End Try
        dbread.Close()
    End Sub
End Class

The firts query run ok, I get fields Ext and CostAssing, But When the second Query try to Update de field CostAct I get following error (Reported by Catch ex As Exception MsgBox("Error 2 is :" & ex.Message)):

"Error 2 is: There is already an open Datareader associated with this Connection which must be closed first."

Please, Any Ideas?

 

[Andrzejek]

Here states: "Retrieving data using a DataReader involves creating an instance of the Command object and then creating a DataReader by calling Command.ExecuteReader to retrieve rows from a data source"
I don;t think you can use DataReader to Update datain a table.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

You may want to read this How to use the SQL UPDATE command in VB.NET

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[lexer]

Hi, I changed the program to use the command ExecuteNonQuery() for Updating Mysql tablet records:

Imports MySql.Data.MySqlClient
Imports System
Imports System.IO
Public Class Form1
    Public dbconn As New MySqlConnection
    Public sqlQuery As String
    Public SQLcmd As MySqlCommand
    Public dbread As MySqlDataReader
    Dim Ext As String
    Dim CostAssing As Integer
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dbconn = New MySqlConnection("Data Source=localhost ; user id=root ; password=mysq1passw0rd ; database=calls;")
        Try
            dbconn.Open()
            'First Query for getting the Extension and CostAssing
            sqlQuery = "SELECT Ext,CostAssing,CostAct FROM estruc "
            SQLcmd = New MySqlCommand(sqlQuery, dbconn)
            dbread = SQLcmd.ExecuteReader
            While dbread.Read()
                Ext = dbread.Item("Ext")
                CostAssing = dbread.Item("CostAssing")
                'Update Ext CostAct with CostAssing value
                sqlQuery = "UPDATE estruc SET CostAct = '" & CostAssing & "'  WHERE Ext = '" & Ext & "'"
                SQLcmd = New MySqlCommand(sqlQuery, dbconn)
                SQLcmd.ExecuteNonQuery() 'Fails Update
            End While
            dbread.Close()
            dbconn.Close()
        Catch ex As Exception
            MsgBox("Error is 2:" & ex.Message)
            'Exit Sub
        End Try
    End Sub
End Class

When The program tries to execute the Update:

sqlQuery = "UPDATE estruc SET CostAct = '" & CostAssing & "'  WHERE Ext = '" & Ext & "'"
                SQLcmd = New MySqlCommand(sqlQuery, dbconn)
                SQLcmd.ExecuteNonQuery()

I receive the following error:

"There is already an open DataReader associated with this connection which must be closed first"

But If I close the DataReader inside the While, The while loop stop and doesn't continue reading throught the mysql tablet.
Plea

 

[Andrzejek]

I would populate the data in the DataTable using the DataReader, close the DataReader and loop thru DataTable to update the records, but...

You ask for this data:
SELECT Ext, CostAssing, CostAct FROM estruc

and then do the loop to:
sqlQuery = "UPDATE estruc SET CostAct = '" & CostAssing & "' WHERE Ext = '" & Ext & "'"

If you want the two fields to be the same (for some reason), wouldn't be easier just to:
UPDATE estruc SET CostAct = CostAct

???

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[softhemc]

I don't know whether or not this would work - I've never had cause to try it, but you could open two connections to the database and in the while loop read from the first connection as you currently do and update using the second connection:

Public Class Form1
    Public dbconn As New MySqlConnection
    [b]Public dbconn2 As New MySqlExtension
    Public sqlQuery2 As String
    Public SQLcmd2 As MySqlCommand[/b]
    Public sqlQuery As String
....
....
....
        [b]dbconn2 = New MySqlConnection("Data Source=localhost ; user id=root ; password=mysq1passw0rd ; database=calls;")[/b]
        Try
            dbconn.Open()
            [b]dbconn2.open()[/b]
....
....
....
            [b]sqlQuery2 = "UPDATE estruc SET CostAct = '" & CostAssing & "'  WHERE Ext = '" & Ext & "'"
            SQLcmd2 = New MySqlCommand(sqlQuery2, dbconn2)
            SQLcmd2.ExecuteNonQuery()[/b]
....
....
....

I've used bold font to indicate what you should add to try this out. But as I said I've never done this, but I can't see why it won't work.

Let me know how you get on.

 

[lexer]

Thanks for your answers, As you recommended I created a second connection for the second execute and It worked, this is the code:

Imports MySql.Data.MySqlClient
Imports System
Imports System.IO
Public Class Form1
    Public dbconn As New MySqlConnection
    Public sqlQuery As String
    Public SQLcmd As MySqlCommand
    Public sqlQuery2 As String
    Public dbconn2 As New MySqlConnection
    Public SQLcmd2 As MySqlCommand
    Public dbread As MySqlDataReader
    Dim Ext As String
    Dim CostAssing As Integer

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        dbconn = New MySqlConnection("Data Source=localhost ; user id=root ; password=mysq1passw0rd ; database=calls")
        dbconn2 = New MySqlConnection("Data Source=localhost ; user id=root ; password=mysq1passw0rd ; database=calls")
        Try
            dbconn.Open()
            dbconn2.Open()
	    'First Query for getting the Extension and CostAssing
            sqlQuery = "SELECT Ext,CostAssing,CostAct FROM estruc "
            SQLcmd = New MySqlCommand(sqlQuery, dbconn)
            dbread = SQLcmd.ExecuteReader
            While dbread.Read()
                Ext = dbread.Item("Ext")
                CostAssing = dbread.Item("CostAssing")
		'Second Query Update CostAct with CostAssing Value
                sqlQuery2 = "UPDATE estruc SET CostAct = '" & CostAssing & "'  WHERE Ext = '" & Ext & "'"
                SQLcmd2 = New MySqlCommand(sqlQuery2, dbconn2)
                SQLcmd2.ExecuteNonQuery()
            End While
        Catch ex As Exception
            MsgBox("Error 1 is :" & ex.Message)
        End Try
        dbread.Close()
        dbconn.Close()
        dbconn2.Close()
    End Sub

End Class

 

[softhemc]

I'm glad it worked.

 

[Andrzejek]

I am probably missing something here, but I will ask again:

If you want the two fields to be the same (for some reason), wouldn't be easier just to:
UPDATE estruc SET CostAct = CostAct

Unless this is purely ‘academic’ exercise of how such approach may be done.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[lexer]

Andrzejek This code (Sub) is part of bigger program and the field "CostAct" is modified in another Sub, I call this code (Sub) to set CostAct with the CostAssing Value