How to rewrite VB code to VB.net

Hi,

Could someone please help me on how to rewrite this code in VB.NET?

Thanks in advance.


Public Function GenerateReport()

Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Dim GRrs As New ADODB.Recordset
Dim GR2rs As New ADODB.Recordset
Dim GR3rs As New ADODB.Recordset
Dim txtFile As Scripting.TextStream
Dim SOLfso1 As New FileSystemObject
Dim tempdate As String
Dim thelockbox As String
Dim UseDateX As String

thelockbox = XType

UseDateX = FormatDateTime(Now, vbShortDate)

tempdate = theDateX

sql1 = "select count(amount) as totalRecs, sum(cast(amount as money)) as totalamt " & _
"From customer" & _
"where acctnum='" & MstrAcct & "' and transstatus=1 and transdate>='" & UseDateX & "' "

sql2 = "declare @Spacer varchar(4) " & _
"select @Spacer = '    ' " & _
"select acctnum, storenbr as VisionAcct, amount, retid, returnreason, retaction,  micrcheck, cast(maker1 as varchar(10)) as pmtdate, @spacer, entrydate " & _
"From customer " & _
"where acctnum='" & MstrAcct & "' and transstatus=1 and transdate>='" & UseDateX & "' "

sql3 = "select acctnum, storenbr as VisionAcct, amount, retid, returnreason, retaction,  micrcheck, micracct, micrrt, maker1 as pmtdate, entrydate " & _
"From customer " & _
"where acctnum='" & MstrAcct & "' and transstatus<>1 and transdate>='" & UseDateX & "' "


Set txtFile = SOLfso1.CreateTextFile("\\***\***\***\***\MyFolder\" & thelockbox & "_DailyReport_" & tempdate & ".txt", True)

GRrs.Open sql1, VRcn

txtFile.Write ("Report." & vbCrLf)
txtFile.Write ("" & vbCrLf)
txtFile.Write ("Lockbox: " & thelockbox & vbCrLf)
txtFile.Write ("Total items submitted: " & GRrs.Fields("totalRecs").Value & vbCrLf)
txtFile.Write ("Total amount of items submitted: " & GRrs.Fields("totalamt").Value & vbCrLf)
txtFile.Write ("Lockbox Acct" & vbTab & "Account No " & vbTab & "Amount" & vbTab & "Return ID" & vbTab & "Reason" & vbTab & "Action" & vbTab & "Check No" & vbTab & "Pmt Date" & vbTab & "Submission Date" & vbCrLf)

GR2rs.Open sql2, VRcn

txtFile.Write (GR2rs.GetString(adClipString, , vbTab, vbCrLf, ""))

txtFile.Write ("Account that could not be submitted for automatic reversal:" & vbCrLf)
txtFile.Write ("" & vbCrLf)

GR3rs.Open sql3, VRcn

If GR3rs.EOF Or GR3rs.BOF Then
    txtFile.Write ("All Accounts provided were submitted for processing." & vbCrLf)
Else

txtFile.Write ("Lockbox Acct" & vbTab & "Account No " & vbTab & "Amount" & vbTab & "Return ID" & vbTab & "Reason" & vbTab & "Action" & vbTab & "Check No" & vbTab & "Ck Acct No" & vbTab & "Ck Routing No" & vbTab & "Pmt Date" & vbTab & "Rev Submission Date" & vbCrLf)

txtFile.Write (Trim(GR3rs.GetString(adClipString, , vbTab, vbCrLf, "")))

End If

GR3rs.Close
GR2rs.Close
GRrs.Close

Set GRrs = Nothing
Set GR2rs = Nothing
Set GR3rs = Nothing

Exit Function
avi4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
Public Sub GenerateReport()

        Dim sql1 As String
        Dim sql2 As String
        Dim sql3 As String
        Dim GRrs As New DataSet
        Dim GR2rs As New DataSet
        Dim GR3rs As New DataSet
        ''Dim txtFile As Scripting.TextStream
        Dim sw As StreamWriter
        Dim tempdate As String
        Dim thelockbox As String
        Dim UseDateX As String
        Try
            thelockbox = XType

            UseDateX = Now.ToShortDateString

            tempdate = theDateX

            sql1 = "select count(amount) as totalRecs, sum(cast(amount as money)) as totalamt " & _
            "From customer" & _
            "where acctnum='" & MstrAcct & "' and transstatus=1 and transdate>='" & UseDateX & "' "

            sql2 = "declare @Spacer varchar(4) " & _
            "select @Spacer = '    ' " & _
            "select acctnum, storenbr as VisionAcct, amount, retid, returnreason, retaction,  micrcheck, cast(maker1 as varchar(10)) as pmtdate, @spacer, entrydate " & _
            "From customer " & _
            "where acctnum='" & MstrAcct & "' and transstatus=1 and transdate>='" & UseDateX & "' "

            sql3 = "select acctnum, storenbr as VisionAcct, amount, retid, returnreason, retaction,  micrcheck, micracct, micrrt, maker1 as pmtdate, entrydate " & _
            "From customer " & _
            "where acctnum='" & MstrAcct & "' and transstatus<>1 and transdate>='" & UseDateX & "' "


            sw = New StreamWriter("\\***\***\***\***\MyFolder\" & thelockbox & "_DailyReport_" & tempdate & ".txt", True)
            VRcn = New OleDbConnection("your connection string")
            VRcn.Open()
            Try
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql1, VRcn)

                ''make sure command does not timeout
                da.SelectCommand.CommandTimeout = 0
                ''fill dataset
                da.Fill(GRrs)
            Catch ex As OleDbException
                Console.Write("OleDb ERROR: " & ex.Message)
            Catch ex As Exception
                Console.Write("ERROR: " & ex.Message)
            End Try

            If GRrs.Tables(0).Rows.Count > 0 Then

                sw.WriteLine("Report." & vbCrLf)
                sw.WriteLine("" & vbCrLf)
                sw.WriteLine("Lockbox: " & thelockbox & vbCrLf)
                sw.WriteLine("Total items submitted: " & GRrs.Tables(0).Rows(0).Item("totalRecs").Value & vbCrLf)
                sw.WriteLine("Total amount of items submitted: " & GRrs.Tables(0).Rows(0).Item("totalamt").Value & vbCrLf)
                sw.WriteLine("Lockbox Acct" & vbTab & "Account No " & vbTab & "Amount" & vbTab & "Return ID" & vbTab & "Reason" & vbTab & "Action" & vbTab & "Check No" & vbTab & "Pmt Date" & vbTab & "Submission Date" & vbCrLf)
            End If
            Try
                Dim da2 As OleDbDataAdapter = New OleDbDataAdapter(sql2, VRcn)
                ''make sure command does not timeout
                da2.SelectCommand.CommandTimeout = 0
                ''fill dataset
                da2.Fill(GR2rs)
            Catch ex As OleDbException
                Console.Write("OleDb ERROR: " & ex.Message)
            Catch ex As Exception
                Console.Write("ERROR: " & ex.Message)
            End Try
            If GR2rs.Tables(0).Rows.Count > 0 Then
                sw.WriteLine(GR2rs.Tables(0).Rows(0).Item(0).ToString)

                sw.WriteLine("Account that could not be submitted for automatic reversal:" & vbCrLf)
                sw.WriteLine("" & vbCrLf)
            End If
            Try
                Dim da3 As OleDbDataAdapter = New OleDbDataAdapter(sql3, VRcn)
                ''make sure command does not timeout
                da3.SelectCommand.CommandTimeout = 0
                ''fill dataset
                da3.Fill(GR3rs)
            Catch ex As OleDbException
                Console.Write("OleDb ERROR: " & ex.Message)
            Catch ex As Exception
                Console.Write("ERROR: " & ex.Message)
            End Try


            If GR2rs.Tables(0).Rows.Count > 0 Then
                sw.WriteLine("Lockbox Acct" & vbTab & "Account No " & vbTab & "Amount" & vbTab & "Return ID" & vbTab & "Reason" & vbTab & "Action" & vbTab & "Check No" & vbTab & "Ck Acct No" & vbTab & "Ck Routing No" & vbTab & "Pmt Date" & vbTab & "Rev Submission Date" & vbCrLf)

                sw.WriteLine(GR3rs.Tables(0).Rows(0).Item(0).ToString)
            Else
                sw.WriteLine("All Accounts provided were submitted for processing." & vbCrLf)
            End If
        Catch ex As Exception
        Finally
            sw.Close()
            sw = Nothing

            GRrs = Nothing
            GR2rs = Nothing
            GR3rs = Nothing
        End Try
    End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
avi4Author Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.