Results 1 to 15 of 15

Thread: [RESOLVED] MysqlDataReader return False if we close the connection

  1. #1

    Thread Starter
    Addicted Member ryanframes's Avatar
    Join Date
    Apr 2012
    Posts
    210

    Resolved [RESOLVED] MysqlDataReader return False if we close the connection

    Hello, i want to create function to get data from mysql using this code :
    Code:
    Public Function executeSQL(ByVal SQLQuery As String, ByVal returnRecordset As Boolean) As MySqlDataReader
            Dim crs As Cursor = Cursor.Current
            Cursor.Current = Cursors.WaitCursor
            Try
                Using objConn As New MySqlConnection(My.Settings.pubConnStr)
                    Dim cmd As New MySqlCommand
                    objConn.Open()
                    cmd.Connection = objConn
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = SQLQuery
                    If returnRecordset Then
                        executeSQL = cmd.ExecuteReader()
                        executeSQL.Read()
                    Else
                        cmd.ExecuteNonQuery()
                    End If
                End Using
                ErrNo = 0
            Catch ex As MySqlException
                ErrNo = Err.Number
                If InStr(ex.Message, "Cannot delete or update a parent row: a foreign key") <> 0 Then
                    MsgBox("Data masih di gunakan pada data atau transaksi yang masih aktif.", MsgBoxStyle.Critical, "ExecuteSQL")
                Else
                    MsgBox(Err.Description & " @ExecuteSQL", MsgBoxStyle.Critical, "ExecuteSQL")
                End If
            End Try
            Cursor.Current = crs
        End Function
    but if i call the function from my form, it always return HasRows = False .
    Sorry for bad english.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: MysqlDataReader return False if we close the connection

    If HasRows is False then there are no rows. I'm fairly sure that you already know that. What would you like us to do about it?

  3. #3
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: MysqlDataReader return False if we close the connection

    Quote Originally Posted by ryanframes View Post
    Hello, i want to create function to get data from mysql using this code :
    but if i call the function from my form, it always return HasRows = False .
    At first glance, it seems to me that you're never returning anything since I don't see a return statement. Shouldn't there be one inside the "If returnRecordset Then" block?

    Code:
    If returnRecordset Then
        'executeSQL = cmd.ExecuteReader()
        'executeSQL.Read()
        'I don't get what the above statements are doing, since executeSQL is your function name and not a variable, right?
        'I would think all you need inside the If statement is
        Return cmd.ExecuteReader()
    Else

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: MysqlDataReader return False if we close the connection

    Quote Originally Posted by OptionBase1 View Post
    At first glance, it seems to me that you're never returning anything since I don't see a return statement.
    The code is using the old VB6 way of returning something (which should never be used in VB.NET) of assigning a value to the implicit local variable named after the method. The function name IS a variable in a VB function.

  5. #5
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: MysqlDataReader return False if we close the connection

    I had no idea that was even valid in VB.NET, that seems like functionality that didn't need to survive 6 -> .NET.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: MysqlDataReader return False if we close the connection

    Quote Originally Posted by OptionBase1 View Post
    I had no idea that was even valid in VB.NET, that seems like functionality that didn't need to survive 6 -> .NET.
    Like a number of things, it would have been kept so that existing VB6 code that used it could be upgraded without having to change too much. There's no good reason to use it in new VB.NET code.

  7. #7
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: MysqlDataReader return False if we close the connection

    Ok, so a DataReader can only read data when a connection is open, right? But, as referenced in the subject, it returns no rows, because the connection is automatically closed at the end of the Using objCon block. So I'm pretty sure you need to rework that.

    Not sure how workable this is, but what if you did this instead:

    Outside of your executeSQL function:

    Code:
    Dim objConn As New MySqlConnection(My.Settings.pubConnStr)
    objConn.Open()
    resultReader = executeSQL (objConn, strSQLQuery, blnReturnRecordSet)
    'Do things with resultReader
    objConn.Close()
    Then your function would look like this:

    Code:
        Public Function executeSQL(objConn As MySqlConnection, ByVal SQLQuery As String, ByVal returnRecordset As Boolean) As MySqlDataReader
            Dim crs As Cursor = Cursor.Current
            Cursor.Current = Cursors.WaitCursor
            Try
                Dim cmd As New MySqlCommand
                cmd.Connection = objConn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = SQLQuery
                If returnRecordset Then
                    executeSQL = cmd.ExecuteReader()
                    executeSQL.Read()  'Not sure, but I'm guessing this line isn't necessary
                Else
                    cmd.ExecuteNonQuery()
                End If
                ErrNo = 0
            Catch ex As MySqlException
                ErrNo = Err.Number
                If InStr(ex.Message, "Cannot delete or update a parent row: a foreign key") <> 0 Then
                    MsgBox("Data masih di gunakan pada data atau transaksi yang masih aktif.", MsgBoxStyle.Critical, "ExecuteSQL")
                Else
                    MsgBox(Err.Description & " @ExecuteSQL", MsgBoxStyle.Critical, "ExecuteSQL")
                End If
            End Try
            Cursor.Current = crs
        End Function
    That removes the connection opening and closing from being done inside of the executeSQL function, which ensures that the resulting data reader is still accessible after the function returns it.

  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: MysqlDataReader return False if we close the connection

    executeSQL.Read() 'Not sure, but I'm guessing this line isn't necessary

    Not only is that line not necessary, it's quite likely a bad idea. Normally, when you use a datareader, you'd iterate through the rows with something like:

    Do While myReader.Read

    The return from Read indicates whether or not there is a row. The only time you'd use a reader.Read outside of a loop would be if there was only one row. So perhaps the OP is just wanting that first row, but that doesn't make sense because they talk about calling HasRows. You wouldn't do that AFTER calling Read, because why would you bother reading if HasRows returned False?

    As to the question, why not just return a datatable and be done with it? Datareaders have their place, but data is clearly being passed around, and a datatable would do that more readily than a datareader would. You can fill the table from the reader anyways.
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    Addicted Member ryanframes's Avatar
    Join Date
    Apr 2012
    Posts
    210

    Re: MysqlDataReader return False if we close the connection

    Quote Originally Posted by jmcilhinney View Post
    Like a number of things, it would have been kept so that existing VB6 code that used it could be upgraded without having to change too much. There's no good reason to use it in new VB.NET code.
    Yes, i tried to convert my old vb6 project function to vb.net, it used adodb.recordset instead of mysqldatareader.
    Basically this is what i'm trying to achieve with that function:

    Open connection -> Check if return recordset true or not -> if not return recordset (which mean insert / update / delete) its just execute the query w/o returning recordset -> if return recordset = true (select query) -> its return recordset.

    in vb6 if we use Adodb.recordset the data(recordset) stored on variable Adodb.Recordset (even the connection is closed), but in vb.net using it seems if i close the connection it somehow dispose the data on that variable so even there's data on my select query it return HasRows=False.
    Sorry for bad english.

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: MysqlDataReader return False if we close the connection

    I was writing as you were posting that. Having now read what you posted: Use a datatable.

    There isn't a perfect one-to-one conversion from VB6 techniques to ADO.NET. The newer tech is based more around the idea of opening, getting your data, and closing. VB6 was much more about leaving a connection open for some time. A DataReader is somewhat faster than filling a datatable, but you'd never see the difference. So, open the connection, fill a datatable (either with a dataadapter or a datareader), close the connection, then return the datatable. All changes are made to the datatable, and all those changes can be pushed back to the database with a single step (though not a single line to set it up) using a dataadapter.
    My usual boring signature: Nothing

  11. #11

    Thread Starter
    Addicted Member ryanframes's Avatar
    Join Date
    Apr 2012
    Posts
    210

    Re: MysqlDataReader return False if we close the connection

    Quote Originally Posted by OptionBase1 View Post
    Ok, so a DataReader can only read data when a connection is open, right? But, as referenced in the subject, it returns no rows, because the connection is automatically closed at the end of the Using objCon block. So I'm pretty sure you need to rework that.

    Not sure how workable this is, but what if you did this instead:

    Outside of your executeSQL function:

    Code:
    Dim objConn As New MySqlConnection(My.Settings.pubConnStr)
    objConn.Open()
    resultReader = executeSQL (objConn, strSQLQuery, blnReturnRecordSet)
    'Do things with resultReader
    objConn.Close()
    Then your function would look like this:

    Code:
        Public Function executeSQL(objConn As MySqlConnection, ByVal SQLQuery As String, ByVal returnRecordset As Boolean) As MySqlDataReader
            Dim crs As Cursor = Cursor.Current
            Cursor.Current = Cursors.WaitCursor
            Try
                Dim cmd As New MySqlCommand
                cmd.Connection = objConn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = SQLQuery
                If returnRecordset Then
                    executeSQL = cmd.ExecuteReader()
                    executeSQL.Read()  'Not sure, but I'm guessing this line isn't necessary
                Else
                    cmd.ExecuteNonQuery()
                End If
                ErrNo = 0
            Catch ex As MySqlException
                ErrNo = Err.Number
                If InStr(ex.Message, "Cannot delete or update a parent row: a foreign key") <> 0 Then
                    MsgBox("Data masih di gunakan pada data atau transaksi yang masih aktif.", MsgBoxStyle.Critical, "ExecuteSQL")
                Else
                    MsgBox(Err.Description & " @ExecuteSQL", MsgBoxStyle.Critical, "ExecuteSQL")
                End If
            End Try
            Cursor.Current = crs
        End Function
    That removes the connection opening and closing from being done inside of the executeSQL function, which ensures that the resulting data reader is still accessible after the function returns it.
    hmmm, yes this could work.. i'll try later. thanks.
    Sorry for bad english.

  12. #12

    Thread Starter
    Addicted Member ryanframes's Avatar
    Join Date
    Apr 2012
    Posts
    210

    Re: MysqlDataReader return False if we close the connection

    Quote Originally Posted by Shaggy Hiker View Post
    I was writing as you were posting that. Having now read what you posted: Use a datatable.

    There isn't a perfect one-to-one conversion from VB6 techniques to ADO.NET. The newer tech is based more around the idea of opening, getting your data, and closing. VB6 was much more about leaving a connection open for some time. A DataReader is somewhat faster than filling a datatable, but you'd never see the difference. So, open the connection, fill a datatable (either with a dataadapter or a datareader), close the connection, then return the datatable. All changes are made to the datatable, and all those changes can be pushed back to the database with a single step (though not a single line to set it up) using a dataadapter.
    Okay, thanks for your suggestion.
    I'm really new to vbnet, and i think datatable could do what i'm trying to achieve.
    Sorry for bad english.

  13. #13
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: [RESOLVED] MysqlDataReader return False if we close the connection

    It will.

    In .NET, if you want a single value you use ExecuteScalar. If you want to go one time through a series of records, you use a DataReader, if you want those records to hang around for a time till you get round to doing something with them, or if you want to display the records, or if you want to edit anything in the records, you use a datatable. You might say that filling a datatable is the basic database interaction in .NET, with the other two being slightly faster for those special cases where their (limited) functionality is all you need.

    Datatables are like little databases held in memory. They can be VERY easily displayed by setting them as the datasource of a DGV. You can also sort them, or do selections from them using a variety of different techniques (there are at least three four commonly used methods). All the rows have a RowState property, too. If you edit a row, add a row, or delete a row, you are just changing that RowState property (and the data, if that applies, of course). Therefore, ALL the changes you have made can be blasted back to the database using a Dataadapter.Update command.

    So, the datatable is your database in memory.
    My usual boring signature: Nothing

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

    Re: [RESOLVED] MysqlDataReader return False if we close the connection

    I'd like to point out that this thread is exactly why I abandoned trying to use generic routines for DB access. It actually less effort to just do it all inline, rather than trying to create a one-size doesn't quite fit all method.

    -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??? *

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [RESOLVED] MysqlDataReader return False if we close the connection

    Quote Originally Posted by Shaggy Hiker View Post
    Datatables are like little databases held in memory.
    It's probably more accurate to say that a DataSet is like a database and a DataTable is like a database table. That said, you should still use just a DataTable if all you want is one table's worth of data. If you need to group multiple tables and especially if you need DataRelations between them, then you should use a DataSet.

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