-
Dec 29th, 2017, 04:39 AM
#1
Thread Starter
Addicted Member
Sorry for bad english.
-
Dec 29th, 2017, 05:43 AM
#2
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?
-
Dec 29th, 2017, 05:44 AM
#3
Re: MysqlDataReader return False if we close the connection
Originally Posted by ryanframes
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
-
Dec 29th, 2017, 06:07 AM
#4
Re: MysqlDataReader return False if we close the connection
Originally Posted by OptionBase1
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.
-
Dec 29th, 2017, 06:13 AM
#5
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.
-
Dec 29th, 2017, 06:33 AM
#6
Re: MysqlDataReader return False if we close the connection
Originally Posted by OptionBase1
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.
-
Dec 29th, 2017, 07:06 AM
#7
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.
-
Dec 29th, 2017, 10:18 AM
#8
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
-
Dec 29th, 2017, 10:18 AM
#9
Thread Starter
Addicted Member
Re: MysqlDataReader return False if we close the connection
Originally Posted by jmcilhinney
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.
-
Dec 29th, 2017, 10:23 AM
#10
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
-
Dec 29th, 2017, 10:24 AM
#11
Thread Starter
Addicted Member
Re: MysqlDataReader return False if we close the connection
Originally Posted by OptionBase1
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.
-
Dec 29th, 2017, 10:32 AM
#12
Thread Starter
Addicted Member
Re: MysqlDataReader return False if we close the connection
Originally Posted by Shaggy Hiker
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.
-
Dec 29th, 2017, 10:42 AM
#13
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
-
Dec 29th, 2017, 12:07 PM
#14
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
-
Dec 29th, 2017, 10:46 PM
#15
Re: [RESOLVED] MysqlDataReader return False if we close the connection
Originally Posted by Shaggy Hiker
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|