Wednesday, January 30, 2013

Looping with ADO.NET on DataTable filled from SQL

Private Sub IsAgencyFormCompleted()
        Dim application_id As Int32
        Dim sqlString As String
        Dim dtl_id As String = String.Empty
        Dim dt As New DataTable()
        Dim MyCtrl As ctrl_db_commands

        If Not Session("application_id") Is Nothing Then
            Try
                application_id = Int32.Parse(Session("application_id"))
                'SQL to check to see if the Agency Form is completed
                sqlString = "SELECT agcy_xxx_number, agcy_npx_number, agcy_number, agcy_primary_contact_title, agcy_executive_director, agcy_medical_director, agcy_entity_type_id, agcy_tax_status FROM [dbo].[TBL_101_APPLICATION] WHERE application_id = " & application_id
                Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("conn").ConnectionString)
                    conn.Open()
                    Using sda As New SqlDataAdapter(sqlString, conn)
                        sda.Fill(dt)
                    End Using
                End Using
            Catch ex As Exception
                Throw New ArgumentException("Exception Occured Contact Cardinal Innovations Solutions Delievery")
            End Try
        End If
        For Each dataRow As DataRow In dt.Rows
            For Each dataColumn As DataColumn In dt.Columns
                Dim fieldValue As String = dataRow(dataColumn).ToString()
                'The AgencyForm is not completed Exit For and display message
                If (fieldValue = "" Or fieldValue = "-1") Then
                    Me.Master.ShowSysMessage("Please complete the entire Section1-1 (Agency) before adding Facilities", "Error")
                    MyCtrl = DirectCast(Me.fv1.FindControl("ctrl_db_commands1"), ctrl_db_commands)
                    MyCtrl.Show_Add = False
                    MyCtrl.Show_Cancel = False
                    MyCtrl.Header_Text_HTML = "Please complete the entire Section1-1 (Agency) before adding Facilities"
                    Exit For
                End If
            Next
        Next
    End Sub

No comments: