troubleshooting Question

How to rewrite VB code to VB.net

Avatar of avi4
avi4 asked on
Visual Basic.NET
2 Comments1 Solution254 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
YZlat

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 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 2 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