INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Visual Basic 2010 and Mysql Nested Query

Visual Basic 2010 and Mysql Nested Query

(OP)
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



CODE --> VB2010

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?

RE: Visual Basic 2010 and Mysql Nested Query

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.

RE: Visual Basic 2010 and Mysql Nested Query

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.

RE: Visual Basic 2010 and Mysql Nested Query

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

CODE --> vb

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:


CODE --> vb

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

RE: Visual Basic 2010 and Mysql Nested Query

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.

RE: Visual Basic 2010 and Mysql Nested Query

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:

CODE

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

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.

RE: Visual Basic 2010 and Mysql Nested Query

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

CODE --> VB

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 

RE: Visual Basic 2010 and Mysql Nested Query

I'm glad it worked.

RE: Visual Basic 2010 and Mysql Nested Query

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.

RE: Visual Basic 2010 and Mysql Nested Query

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close