Description:
I have .NET Application creates a dataset on the following sqlcommands
1. select * from <tablename> where isactive = ‘Y’ order by mid(regcode,1,4)
2. select * from <tablename> where isactive = ‘Y’ order by mid(regcode,1,4), CAST(mid(regcode, 5, LENGTH(regcode) -4) AS unsigned)
Application runs perfectly with first query. Whereas it is giving the following error with 2nd query:
“There is already an open DataReader associated with this Connection which must be closed first.”
The following is my code:
Public Sub PopulateRecords()
Try
sConnStr = ConfigurationSettings.AppSettings("connectionString")
Dim dbConnection As New MySqlConnection(sConnStr)
Dim da As New MySqlDataAdapter(strSQL, dbConnection)
Dim Ds As New DataSet
Dim dr As DataRow
Dim rowCount As Integer
da.Fill(Ds, "TableName")
If RegSearch = True Then
rowCount = Ds.Tables(0).Rows.Count
If rowCount <> 0 Then
dr = Ds.Tables(0).Rows(rowCount - 1)
LastRegCode = dr("RegCode")
Else
RegSearch = False
End If
End If
If Ds.Tables(0).Rows.Count = 0 Then
NullResult.InnerHtml = "There are no records"
DataGrid1.Visible = False
Else
DataGrid1.Visible = True
NullResult.InnerHtml = ""
End If
DataGrid1.DataSource = Ds.Tables(0)
Try
DataGrid1.DataBind()
Catch
Try
DataGrid1.CurrentPageIndex = 0
DataGrid1.DataBind()
Catch
'Response.Write("Catch")
NullResult.InnerHtml = "There are no records"
DataGrid1.Visible = False
End Try
End Try
If (dbConnection.State <> ConnectionState.Broken And dbConnection.State <> ConnectionState.Closed) Then
dbConnection.Close()
End If
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Sub
The error is coming at da.Fill(Ds, "TableName") statement.
I am closing all data readers as well as db connection wherever I used. The backend database is MySQL 5.0 version.
Please help me where I am doing wrong.
How to repeat:
If the following query is given then the above error is comming:
select * from <tablename> where isactive = ‘Y’ order by mid(regcode,1,4), CAST(mid(regcode, 5, LENGTH(regcode) -4) AS unsigned)
Description: I have .NET Application creates a dataset on the following sqlcommands 1. select * from <tablename> where isactive = ‘Y’ order by mid(regcode,1,4) 2. select * from <tablename> where isactive = ‘Y’ order by mid(regcode,1,4), CAST(mid(regcode, 5, LENGTH(regcode) -4) AS unsigned) Application runs perfectly with first query. Whereas it is giving the following error with 2nd query: “There is already an open DataReader associated with this Connection which must be closed first.” The following is my code: Public Sub PopulateRecords() Try sConnStr = ConfigurationSettings.AppSettings("connectionString") Dim dbConnection As New MySqlConnection(sConnStr) Dim da As New MySqlDataAdapter(strSQL, dbConnection) Dim Ds As New DataSet Dim dr As DataRow Dim rowCount As Integer da.Fill(Ds, "TableName") If RegSearch = True Then rowCount = Ds.Tables(0).Rows.Count If rowCount <> 0 Then dr = Ds.Tables(0).Rows(rowCount - 1) LastRegCode = dr("RegCode") Else RegSearch = False End If End If If Ds.Tables(0).Rows.Count = 0 Then NullResult.InnerHtml = "There are no records" DataGrid1.Visible = False Else DataGrid1.Visible = True NullResult.InnerHtml = "" End If DataGrid1.DataSource = Ds.Tables(0) Try DataGrid1.DataBind() Catch Try DataGrid1.CurrentPageIndex = 0 DataGrid1.DataBind() Catch 'Response.Write("Catch") NullResult.InnerHtml = "There are no records" DataGrid1.Visible = False End Try End Try If (dbConnection.State <> ConnectionState.Broken And dbConnection.State <> ConnectionState.Closed) Then dbConnection.Close() End If Catch ex As Exception Response.Write(ex.Message) End Try End Sub The error is coming at da.Fill(Ds, "TableName") statement. I am closing all data readers as well as db connection wherever I used. The backend database is MySQL 5.0 version. Please help me where I am doing wrong. How to repeat: If the following query is given then the above error is comming: select * from <tablename> where isactive = ‘Y’ order by mid(regcode,1,4), CAST(mid(regcode, 5, LENGTH(regcode) -4) AS unsigned)