Hi my fellow programmer! Here's a quick scenario of my logic. I call a stored procedure and bind the result to a data table then loop depends on how many data are there in my data table to fill in the fields that are empty. It is done by opening a table and look for the code per row on data table. However, as soon as it enters the rs.open line, it always leads me to Finally block which should not be the case because the loop is not done yet. Can anyone help me where I went wrong? I am using vb.net. Below is my code for your reference.
Try
Dim strErrMsg As String = ""
Dim iLoopCtr As Integer
Dim iLoopCtr2 As Integer
Dim sHASD As String
Dim sHAMD As String
Dim objAdapter As New OleDbDataAdapter
Dim objReader As OleDbDataReader = Nothing
cdbcn = New clsOleDB
cdbcn.ConnectionString = sProdReportConnection
If cdbcn.OpenConnection = True Then
cdbcn.proc_usp_sel_ProdReport(objAdapter, objReader)
Do While objReader.Read = True
iLoopCtr += 1
Loop
objReader.Close()
If iLoopCtr <> 0 Then
objAdapter.Fill(dt_ProdReport)
Dim iProdCtr As Integer
Dim iRecID As Integer
Dim sAgentCode As String
Dim iAgentCode As Integer
Dim dtIssueDate As Date
Dim dtReceiveDate As Date
Dim sTblName As String = ""
Dim sSQLWhere As String = ""
Dim rs As New ADODB.Recordset()
Dim i As Integer = 0
For iProdCtr = 0 To dt_ProdReport.Rows.Count - 1
''PASS PARAMETER HERE
blnProdRep = False
iRecID = dt_ProdReport.Rows.Item(iProdCtr).Item(0)
iAgentCode = dt_ProdReport.Rows.Item(iProdCtr).Item(5)
dtIssueDate = dt_ProdReport.Rows.Item(iProdCtr).Item(4)
dtReceiveDate = dt_ProdReport.Rows.Item(iProdCtr).Item(9)
''CLASS FOR HIERARCHY GOES HERE
adoCN.ConnectionString = sProdReportConnection
adoCN.Open()
rs.CursorLocation = CursorLocationEnum.adUseClient
rs.CursorType = CursorTypeEnum.adOpenStatic
rs.LockType = LockTypeEnum.adLockOptimistic
If dtIssueDate.Date = "1/1/1900" Then
sTblName = "ALFA_POINTS.DBO.TBLHIERARCHY" & dtReceiveDate.ToString("yy") & dtReceiveDate.ToString("MM")
If rs.State = 1 Then rs.Close()
sSQLWhere = sTblName & " where AgentCode = '" & iAgentCode & "'"
rs.Open("Select * from " & sSQLWhere, adoCN)
If rs.RecordCount >= 1 Then
dt_ProdReport.Rows(iProdCtr)("ASDCode") = rs("ASDCode").Value
dt_ProdReport.Rows(iProdCtr)("ASDName") = rs("ASDName").Value
dt_ProdReport.Rows(iProdCtr)("AMDCode") = rs("AMDCode").Value
dt_ProdReport.Rows(iProdCtr)("AMDName") = rs("AMDName").Value
Else
blnProdRep = True
'MsgBox("Looking to other table......", MsgBoxStyle.Information, "System Notification")
End If
adoCN.Close()
Else
sTblName = "ALFA_POINTS.DBO.TBLHIERARCHY" & dtIssueDate.ToString("yy") & dtIssueDate.ToString("MM")
If rs.State = 1 Then rs.Close()
sSQLWhere = sTblName & " where AgentCode = '" & iAgentCode & "'"
rs.Open("Select * from " & sSQLWhere, adoCN)
If rs.RecordCount >= 1 Then
dt_ProdReport.Rows(iProdCtr)("ASDCode") = rs("ASDCode").Value
dt_ProdReport.Rows(iProdCtr)("ASDName") = rs("ASDName").Value
dt_ProdReport.Rows(iProdCtr)("AMDCode") = rs("AMDCode").Value
dt_ProdReport.Rows(iProdCtr)("AMDName") = rs("AMDName").Value
Else
blnProdRep = True
'MsgBox("Looking to other table......", MsgBoxStyle.Information, "System Notification")
End If
adoCN.Close()
End If
If blnProdRep = True Then
sTblName = "Agents_Comparison.dbo.mst_Hierarchy where AgentCode = '" & iAgentCode & "'"
If rs.State = 1 Then rs.Close()
rs.Open("Select * from " & sTblName, adoCN, CursorTypeEnum.adOpenKeyset)
If rs.RecordCount >= 1 Then
dt_ProdReport.Rows(iProdCtr)("ASDCode") = rs("ASDCode").Value
'dt_ProdReport.Rows(iProdCtr)("ASDName") = rs("ASDName").Value
dt_ProdReport.Rows(iProdCtr)("AMDCode") = rs("AMDCode").Value
'dt_ProdReport.Rows(iProdCtr)("AMDName") = rs("AMDName").Value
Else
blnProdRep = False
MsgBox("No record found", MsgBoxStyle.Information, "System Notification")
End If
adoCN.Close()
End If
''CLASS FOR HAMD HASD GOES HERE
Next
End If
End If
Catch ex As Exception
Finally
ImportExcel()
End Try
