Results 1 to 4 of 4

Thread: [RESOLVED] Read & Update MySQL Using the Same Open Connection

  1. #1

    Thread Starter
    Hyperactive Member Vladamir's Avatar
    Join Date
    Feb 2012
    Location
    Miami, FL
    Posts
    486

    Resolved [RESOLVED] Read & Update MySQL Using the Same Open Connection

    We're trying to use the same connection to do two (2) operations in a MySQL project. We want to read in a record, process some values from it and then UPDATE the record based on the new values. But for some reason, this only reads, but it does not update the database.

    Code:
    Private Sub ServerForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            dbconn = New MySqlConnection("Data Source=fabeng;user id=my_username;password=xxxx;database=MyDataBase")
            sql = "SELECT * FROM getCIO WHERE id='" & My.Application.CommandLineArgs(0) & "'"
    
            Try
                dbconn.Open()
                dbcomm = New MySqlCommand(sql, dbconn)
                dbcomm1 = New MySqlCommand(sqlUpdate, dbconn)
                dbReader = dbcomm.ExecuteReader()
    
                While dbReader.Read()
    
                    . DO SOME SQL READING AND PROCESS
    
    
                End While
     
                dbReader.Close()
    
    
                ' NOW TRY TO UPDATE A RECORD USING THE SAME OPEN CONNECTION
    
                Dim sqlUpdate = "UPDATE getCIO SET calcsComplete = 1, field1=" & field1value & ", field2=" & field2value & " WHERE id='" & My.Application.CommandLineArgs(0) & "'"
    
                dbReader.Close()
                dbcomm1.CommandText = sqlUpdate
                dbcomm1.ExecuteNonQuery()
    
            Catch ex As Exception
                MsgBox("Error while reading the database: " & ex.Message)
    
            Finally
    
                dbconn.Close()
            End Try
    
        End Sub

  2. #2

    Thread Starter
    Hyperactive Member Vladamir's Avatar
    Join Date
    Feb 2012
    Location
    Miami, FL
    Posts
    486

    Re: Read & Update MySQL Using the Same Open Connection

    Okay, we got this one working. Another developer who specializes in php was assisting with this and he had mixed up some ideas using php vs VB.NET. That's what I get for listening to other developers..... LOL!

  3. #3
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    Re: [RESOLVED] Read & Update MySQL Using the Same Open Connection

    so for those who read the post.. what did you do wrong?


    btw.... many of the posters here are developers
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] Read & Update MySQL Using the Same Open Connection

    I know why it didn't work...
    you closed the reader twice... the second dbreader.close throws an exception... it's in the form load event, which in some cases eats any exceptions... specifically in Win7, 64-bit... so the first will run then the reader is closed, and on the second attempt at closing the already closed reader, an exception is thrown and the code stops, kicks out, and meanwhile the app keeps otherwise running along swimmingly.

    I bet if you put a break point on the first dbreader.close, then press Ctrl+Alt+E - bring up the exceptions window, and then mark the selections so that it breaks as soon as an exception is thrown... run the app... as soon as it hits the break point, step through the rest of hte code... you'll see one of two things happen... 1) you'll actually see the exception happen as it should on the second dbreader.close call, or (less likely) 2) you'll see the code kick out, unexpectedly, and the form loads.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width