troubleshooting Question

Export to text files based on a value

Avatar of Queennie L
Queennie L asked on
Microsoft SQL ServerVisual Basic.NETSQL
18 Comments1 Solution217 ViewsLast Modified:
Export to text files based on the value from SQL table. What I want to accomplish is if Filename Column in SQL table dbo.Employee is (See attached):
1) "Filename1" then export with the name Filename1_today'sdate.txt.
2) "Filename2" then export with the name Filename2_today'sdate.txt.
3) "Filename3" then export with the name Filename3_today'sdate.txt.


Here is my vb.net code that works but only for 1 file.

Private Sub ExportToTXT()

        'CONSTANT RECORDSET:
        Dim conn As New ADODB.Connection
        Dim rsConstantPage As New ADODB.Recordset

        'LOOP RECORDSET:
        '/* EMPLOYEE LOOP */
        Dim rsEmployeeNameLoop As New ADODB.Recordset
        Dim rsEmployeeAddressLoop As New ADODB.Recordset
        Dim rsEmployeeCityStateZipLoop As New ADODB.Recordset
        Dim rsEmployeeEmploymentDateLoop As New ADODB.Recordset

        '/* EMPLOYER LOOP */
        Dim rsEmployerNameLoop As New ADODB.Recordset
        Dim rsEmployerAddressLoop As New ADODB.Recordset
        Dim rsEmployerCityStateZipLoop As New ADODB.Recordset

        '- - - - - - - - - - - - - - - - -

        'PAGE STRING CONSTANT:
        Dim ConstantPage As String

        'SPACE STRING CONSTANT:
        Dim r As String

        'DASH STRING CONSTANT:
        Dim s As String

        'COMMA STRING CONSTANT:
        Dim t As String


       
        


        Try
            ConstantPage = "Page 1"

            '/*SPACE CONSTANT*/
            r = " "

            '/*DASH CONSTANT*/
            s = "-"

            '/*COMMA CONSTANT*/
            t = ","



            conn.Open("Provider=SQLOLEDB;DSN=DATABASE;Data Source=SQLSERVER;Initial Catalog=EMPLOYEEDATA;Persist Security Info=True;User ID=sa;Password=111111;")
            conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

            Dim fileName As String = "\\NETWORKFOLDER\EMPLOYEE.txt"




            FileOpen(1, fileName, OpenMode.Output)

            'CONSTANT STRING LOOPS:
            rsConstantPage = conn.Execute("[EMPLOYEEDATA].[dbo].[ConstantVariable] WHERE UniqueID = 1", , ADODB.CommandTypeEnum.adCmdTable)

            'PAGE LOOP:
            Do Until rsConstantPage.EOF

                PrintLine(1, s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & r & ConstantPage & r & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s)

                '**************************************


                    'EMPLOYEE NAME LOOP DETAILS:
                    rsEmployeeNameLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee]", , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployeeNameLoop.EOF

                        lsEmployeeame = rsEmployeeNameLoop.Fields("EmployeeName").Value

                        PrintLine(1, vbCrLf & lsEmployeeName)

                        rsEmployeeNameLoop.MoveNext()
                    Loop

                    ' EMPLOYEE ADDRESS LOOP DETAILS:
                    rsEmployeeAddressLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee]", , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployeeAddressLoop.EOF

                        lsEmployeeAddress = rsEmployeeAddressLoop.Fields("EmployeeAddress").Value

                        PrintLine(1, lsEmployeeAddress)

                        rsEmployeeAddressLoop.MoveNext()
                    Loop

                    ' EMPLOYEE CITY STATE ZIPCODE LOOP DETAILS:
                    rsEmployeeCityStateZipLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee]", , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployeeCityStateZipLoop.EOF

                        lsEmployeeCity = rsEmployeeCityStateZipLoop.Fields("EmployeeCity").Value
                        lsEmployeeState = rsEmployeeCityStateZipLoop.Fields("EmployeeState").Value
                        lsEmployeeZip = rsEmployeeCityStateZipLoop.Fields("EmployeeZipcode").Value

                        PrintLine(1, ls EmployeeCity & t & r & ls EmployeeState & r & ls EmployeeZip)

                        rsEmployeeCityStateZipLoop.MoveNext()
                    Loop

                    ' EMPLOYEE EMPLOYMENT DATE LOOP DETAILS:
                    rsEmployeeEmploymentDateLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee]", , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployeeEmploymentDateLoop.EOF

                        lsEmployeeEmploymentDate = rsEmployeeEmploymentDateLoop.Fields("EmploymentDate").Value


                        PrintLine(1, lsEmployeeEmploymentDate)

                        rsEmployeeEmploymentDateLoop.MoveNext()
                    Loop

                    '******************************************

                    'EMPLOYER NAME LOOP DETAILS:
                    rsEmployerNameLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee], , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployerNameLoop.EOF

                        lsEmployerName = rsEmployerNameLoop.Fields("EmployerName").Value

                        PrintLine(1, vbCrLf & lsEmployerName)

                        rsEmployerNameLoop.MoveNext()
                    Loop

                    ' EMPLOYER ADDRESS LOOP DETAILS:
                    rsEmployerAddressLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee], , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployerAddressLoop.EOF

                        lsEmployerAddress = rsEmployerAddressLoop.Fields("EmployerAddress").Value

                        PrintLine(1, lsEmployerAddress)

                        rsEmployerAddressLoop.MoveNext()
                    Loop

                    ' EMPLOYER CITY STATE ZIPCODE LOOP DETAILS:
                    rsEmployerCityStateZipLoop = conn.Execute("[EMPLOYEEDATA].[dbo].[Employee], , ADODB.CommandTypeEnum.adCmdTable)

                    Do While Not rsEmployerCityStateZipLoop.EOF

                        lsEmployerCity = rsEmployerCityStateZipLoop.Fields("EmployerCity").Value
                        lsEmployerState = rsEmployerCityStateZipLoop.Fields("EmployerState").Value
                        lsEmployerZip = rsEmployerCityStateZipLoop.Fields("EmployerZipcode").Value

                        PrintLine(1, lsEmployerCity & t & r & lsEmployerState & r & lsEmployerZip)

                        rsEmployerCityStateZipLoop.MoveNext()
                    Loop


                            rsConstantPage.Close()
                            rsEmployeeNameLoop.MoveNext()
                        Loop


            FileClose(1)





        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Error")
        Finally

        End Try
    End Sub


SQLTables.xlsx



Please help.

Thank you very much.
File1_12272017.txt
File2_12272017.txt
File3_12272017.txt
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros