Description:
I get the following error when trying to execute stored procedures:
MySql.Data.MySqlClient.MySqlException was caught
Message="There is already an open DataReader associated with this Connection which must be closed first."
Source="Microsoft.VisualBasic"
StackTrace:
at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at DButils.executeSp(String pstrSpName, String[][] pstrarrParamInfo) in c:\inetpub\wwwroot\Motonew\App_Code\DbUtils.vb:line 78
How to repeat:
The following vb.net code is used:
Const CONNECTION_STRING As String = "Database=thedb;Data Source=localhost;User Id=root;Password=xxxx"
Public Sub insertRepairPart(ByVal updatedBy As String, ByVal jobId As String, ByVal partCode As String, ByVal prtQuantity As String, ByVal typeId As String, ByVal designatorCode As String)
Dim lstrParamInfo(5)() As String
lstrParamInfo(0) = New String() {"iUpdatedBy", "Int", updatedBy, "11", "in"}
lstrParamInfo(1) = New String() {"iJOB_ID", "Int", jobId, "11", "in"}
lstrParamInfo(2) = New String() {"vPRT_Code", "varchar", partCode, "20","in"}
lstrParamInfo(3) = New String() {"iPrtQuantity", "Int", prtQuantity, "11", "in"}
lstrParamInfo(4) = New String() {"iTYP_ID", "Int", typeId, "11", "in"}
lstrParamInfo(5) = New String() {"vDesignatorCode", "varchar", designatorCode, "20", "in"}
executeSp("MotoDB.SP_iRepairPart", lstrParamInfo)
End Sub
Private Function executeSp(ByVal pstrSpName As String, ByVal pstrarrParamInfo As String()()) As DataTable
Dim lsqlconDBConnection As MySqlConnection = New MySqlConnection(CONNECTION_STRING)
Dim lsqldapDataAdapter = New MySqlDataAdapter
Dim lsqlprmSqlParameter As MySqlParameter
Dim lsqldbtSqlParamType As MySqlDbType = MySqlDbType.VarChar
Dim lrsSpResults As DataSet = New DataSet()
Dim lrsReturnResults As DataTable = New DataTable()
Dim lintOuputParamCount As Integer = 0
Dim lobjarrOuputParameterReuslts As Object()
Dim lblnNoResults As Boolean = False
Dim i As Integer
Try
lsqldapDataAdapter.SelectCommand = New MySqlCommand(pstrSpName, lsqlconDBConnection)
lsqldapDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
If Not (pstrarrParamInfo Is Nothing) Then
For i = 0 To (pstrarrParamInfo.Length - 1)
Select Case (pstrarrParamInfo(i)(1).ToLower())
Case "int"
lsqldbtSqlParamType = MySqlDbType.Int64
Case "varchar"
lsqldbtSqlParamType = MySqlDbType.VarChar
Case "datetime"
lsqldbtSqlParamType = MySqlDbType.Datetime
Case "decimal"
lsqldbtSqlParamType = MySqlDbType.Double
End Select
lsqlprmSqlParameter = lsqldapDataAdapter.SelectCommand.Parameters.Add(pstrarrParamInfo(i)(0), lsqldbtSqlParamType)
If (isNumber(pstrarrParamInfo(i)(3))) Then
lsqlprmSqlParameter.Size = Integer.Parse(pstrarrParamInfo(i)(3))
End If
If Not (pstrarrParamInfo(i)(1).ToLower() = "datetime" And pstrarrParamInfo(i)(2) = "") Then
lsqlprmSqlParameter.Value = pstrarrParamInfo(i)(2)
End If
If (pstrarrParamInfo(i)(4).ToLower().Equals("out")) Then
lintOuputParamCount += 1
lsqlprmSqlParameter.Direction = ParameterDirection.InputOutput
Else
lsqlprmSqlParameter.Direction = ParameterDirection.Input
End If
Next
End If
Try
lsqldapDataAdapter.Fill(lrsSpResults)
Catch ex As Exception
'If (lrsSpResults.Tables.Count = 0) Then
'Thread.Sleep(40)
lsqldapDataAdapter.Fill(lrsSpResults)
'End If
End Try
End Sub
the stored procedure is:
DROP PROCEDURE IF EXISTS `MotoDB`.`SP_iPart`;
CREATE PROCEDURE `MotoDB`.`SP_iPart`
( INOUT iID INT,
IN iUpdatedBy INT ,
IN vPart_Code VARCHAR(5),
IN vDesignator_Code VARCHAR(5),
IN cCost DECIMAL(9,2),
IN iRecordStatus INT
)
BEGIN
DECLARE iExists INT;
DECLARE bInsertNew Boolean;
SET bInsertNew := TRUE ;
IF (iID is not null) THEN
SELECT COUNT(1)
INTO iExists
FROM PRT_Parts
WHERE ID=iID
AND MasterID=ID;
IF (iExists>0) THEN
SET bInsertNew := FALSE ;
END IF;
END IF;
IF (bInsertNew = TRUE) THEN
INSERT INTO PRT_Parts
(
Recordstatus,
ValidFrom,
ValidTo,
UpdatedBy,
Part_Code ,
Designator_Code,
Cost
)
VALUES
(
1,
CURRENT_TIMESTAMP,
NULL,
iUpdatedBy,
vPart_Code ,
vDesignator_Code,
cCost
);
UPDATE PRT_Parts
SET MasterID = LAST_INSERT_ID()
WHERE ID = LAST_INSERT_ID();
SET iID:=LAST_INSERT_ID();
ELSE
INSERT INTO PRT_Parts
(
Recordstatus,
ValidFrom,
ValidTo,
MasterID,
UpdatedBy,
Part_Code ,
Designator_Code,
Cost
)
SELECT
2,
ValidFrom,
CURRENT_TIMESTAMP,
MasterID,
UpdatedBy,
Part_Code ,
Designator_Code,
Cost
FROM PRT_Parts
WHERE ID=iID;
UPDATE PRT_Parts
SET
Recordstatus = iRecordStatus ,
ValidFrom = CURRENT_TIMESTAMP,
ValidTo = NULL,
MasterID = iID,
UpdatedBy = iUpdatedBy,
Part_Code = vPart_Code,
Designator_Code = vDesignator_Code,
Cost = cCost
WHERE ID=iID;
END IF;
END;
table creation script:
CREATE TABLE `prt_parts` (
`ID` int(11) NOT NULL auto_increment,
`MasterID` int(11) default NULL,
`Recordstatus` int(11) default NULL,
`ValidFrom` datetime default NULL,
`ValidTo` datetime default NULL,
`UpdatedBy` int(11) default NULL,
`Part_Code` int(11) default NULL,
`Cost` decimal(9,2) default NULL,
`TYP_ID` int(11) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Description: I get the following error when trying to execute stored procedures: MySql.Data.MySqlClient.MySqlException was caught Message="There is already an open DataReader associated with this Connection which must be closed first." Source="Microsoft.VisualBasic" StackTrace: at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags) at Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure) at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn) at DButils.executeSp(String pstrSpName, String[][] pstrarrParamInfo) in c:\inetpub\wwwroot\Motonew\App_Code\DbUtils.vb:line 78 How to repeat: The following vb.net code is used: Const CONNECTION_STRING As String = "Database=thedb;Data Source=localhost;User Id=root;Password=xxxx" Public Sub insertRepairPart(ByVal updatedBy As String, ByVal jobId As String, ByVal partCode As String, ByVal prtQuantity As String, ByVal typeId As String, ByVal designatorCode As String) Dim lstrParamInfo(5)() As String lstrParamInfo(0) = New String() {"iUpdatedBy", "Int", updatedBy, "11", "in"} lstrParamInfo(1) = New String() {"iJOB_ID", "Int", jobId, "11", "in"} lstrParamInfo(2) = New String() {"vPRT_Code", "varchar", partCode, "20","in"} lstrParamInfo(3) = New String() {"iPrtQuantity", "Int", prtQuantity, "11", "in"} lstrParamInfo(4) = New String() {"iTYP_ID", "Int", typeId, "11", "in"} lstrParamInfo(5) = New String() {"vDesignatorCode", "varchar", designatorCode, "20", "in"} executeSp("MotoDB.SP_iRepairPart", lstrParamInfo) End Sub Private Function executeSp(ByVal pstrSpName As String, ByVal pstrarrParamInfo As String()()) As DataTable Dim lsqlconDBConnection As MySqlConnection = New MySqlConnection(CONNECTION_STRING) Dim lsqldapDataAdapter = New MySqlDataAdapter Dim lsqlprmSqlParameter As MySqlParameter Dim lsqldbtSqlParamType As MySqlDbType = MySqlDbType.VarChar Dim lrsSpResults As DataSet = New DataSet() Dim lrsReturnResults As DataTable = New DataTable() Dim lintOuputParamCount As Integer = 0 Dim lobjarrOuputParameterReuslts As Object() Dim lblnNoResults As Boolean = False Dim i As Integer Try lsqldapDataAdapter.SelectCommand = New MySqlCommand(pstrSpName, lsqlconDBConnection) lsqldapDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure If Not (pstrarrParamInfo Is Nothing) Then For i = 0 To (pstrarrParamInfo.Length - 1) Select Case (pstrarrParamInfo(i)(1).ToLower()) Case "int" lsqldbtSqlParamType = MySqlDbType.Int64 Case "varchar" lsqldbtSqlParamType = MySqlDbType.VarChar Case "datetime" lsqldbtSqlParamType = MySqlDbType.Datetime Case "decimal" lsqldbtSqlParamType = MySqlDbType.Double End Select lsqlprmSqlParameter = lsqldapDataAdapter.SelectCommand.Parameters.Add(pstrarrParamInfo(i)(0), lsqldbtSqlParamType) If (isNumber(pstrarrParamInfo(i)(3))) Then lsqlprmSqlParameter.Size = Integer.Parse(pstrarrParamInfo(i)(3)) End If If Not (pstrarrParamInfo(i)(1).ToLower() = "datetime" And pstrarrParamInfo(i)(2) = "") Then lsqlprmSqlParameter.Value = pstrarrParamInfo(i)(2) End If If (pstrarrParamInfo(i)(4).ToLower().Equals("out")) Then lintOuputParamCount += 1 lsqlprmSqlParameter.Direction = ParameterDirection.InputOutput Else lsqlprmSqlParameter.Direction = ParameterDirection.Input End If Next End If Try lsqldapDataAdapter.Fill(lrsSpResults) Catch ex As Exception 'If (lrsSpResults.Tables.Count = 0) Then 'Thread.Sleep(40) lsqldapDataAdapter.Fill(lrsSpResults) 'End If End Try End Sub the stored procedure is: DROP PROCEDURE IF EXISTS `MotoDB`.`SP_iPart`; CREATE PROCEDURE `MotoDB`.`SP_iPart` ( INOUT iID INT, IN iUpdatedBy INT , IN vPart_Code VARCHAR(5), IN vDesignator_Code VARCHAR(5), IN cCost DECIMAL(9,2), IN iRecordStatus INT ) BEGIN DECLARE iExists INT; DECLARE bInsertNew Boolean; SET bInsertNew := TRUE ; IF (iID is not null) THEN SELECT COUNT(1) INTO iExists FROM PRT_Parts WHERE ID=iID AND MasterID=ID; IF (iExists>0) THEN SET bInsertNew := FALSE ; END IF; END IF; IF (bInsertNew = TRUE) THEN INSERT INTO PRT_Parts ( Recordstatus, ValidFrom, ValidTo, UpdatedBy, Part_Code , Designator_Code, Cost ) VALUES ( 1, CURRENT_TIMESTAMP, NULL, iUpdatedBy, vPart_Code , vDesignator_Code, cCost ); UPDATE PRT_Parts SET MasterID = LAST_INSERT_ID() WHERE ID = LAST_INSERT_ID(); SET iID:=LAST_INSERT_ID(); ELSE INSERT INTO PRT_Parts ( Recordstatus, ValidFrom, ValidTo, MasterID, UpdatedBy, Part_Code , Designator_Code, Cost ) SELECT 2, ValidFrom, CURRENT_TIMESTAMP, MasterID, UpdatedBy, Part_Code , Designator_Code, Cost FROM PRT_Parts WHERE ID=iID; UPDATE PRT_Parts SET Recordstatus = iRecordStatus , ValidFrom = CURRENT_TIMESTAMP, ValidTo = NULL, MasterID = iID, UpdatedBy = iUpdatedBy, Part_Code = vPart_Code, Designator_Code = vDesignator_Code, Cost = cCost WHERE ID=iID; END IF; END; table creation script: CREATE TABLE `prt_parts` ( `ID` int(11) NOT NULL auto_increment, `MasterID` int(11) default NULL, `Recordstatus` int(11) default NULL, `ValidFrom` datetime default NULL, `ValidTo` datetime default NULL, `UpdatedBy` int(11) default NULL, `Part_Code` int(11) default NULL, `Cost` decimal(9,2) default NULL, `TYP_ID` int(11) default NULL, PRIMARY KEY (`ID`), UNIQUE KEY `ID` (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;