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

It looks like rs.Open is throwing an exception, but you're ignoring it. You should do something in that Catch block to report what went wrong.

Do not understand your think and what did you want to do by these lines.

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

Hi shark!

The logic for that is that i will be looking on TableA for a record using select and where statement, if record exists then bind it to datatable, if not then look at tableB and lastly look at tableC.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.