ADO Record Count Issue

Good Morning,

What should be very easy, seems to be complicating the heck out of my day.

I am developing a automatic (possible windows service) that will generate and
distribute reports on a given schedule.

I am using two different ado recordsets (one for the distribtution list, the
second for the data), and then the the information is pushed on to an Excel
document (Customers, preference, not mine), and then emailed as an attachment.


The problem I am having is that the RecordCount value of my data recordset
keeps going to -1. Now, the data gets copied to the excel *** fine, but
when i refer to the recordcount, it gets all messed up because of the value.

I probably need a second pair of eyes. (or more...)

Some Code:

Dim s As String

'~~Clear Repository Folder First~~!
For Each s In Directory.GetFiles(nPath, "*.xls", SearchOption.
TopDirectoryOnly)
File.Delete(s)
Next

'Configure the email server
smtp.Host = smtpServer
smtp.Port = tPort
smtp.DeliveryMethod = SmtpDeliveryMethod.Network
smtp.Credentials = New Net.NetworkCredential(Creds)
eBody = "Test - New Report"

'~~Set Basic Values for Email
With msg
.IsBodyHtml = False
.From = addFrom
.To.Add(addTo)
.Subject = "New Report"
End With

'~~Set Connection String~~!
connStr = "connection string"


Try 'Try to connect to database
conn.Open(connStr)
sqlCode = "SELECT CENTERNBR,CENTER FROM CENTERS WHERE ACTIVE =
'TRUE'"

'Load the Centers from the database
cl.Open(sqlCode, connStr, ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockBatchOptimistic, 1)

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, My.Application.Info.
Title)

End Try

'get the needed dates
Calcs.GetSchedule(Date.Today.DayOfWeek, sDate)

Try

Do Until cl.EOF
xlApp = New Excel.Application
xlbook = xlApp.Workbooks.Add

'~~Configure First ***~~!
xlSheet = xlApp.Worksheets.Add
sqlCode2 = "SELECT Center,WRKORDNBR,Name,CallType FROM
SchedReport WHERE CenterNbr In(" _
& cl("CENTERNBR").Value.ToString & ") And Scheddate = '"
& sDate(1) & "'"

rs.Open(sqlCode2, connStr, ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockBatchOptimistic, 1)

With xl***
~Format ***
.Range("A4").CopyFromRecorset(rs)

{Here i refer to the rs.recordcount, and the watch says
it is -1, which is not true}

rs.Close() 'close and reset rs so it can be used again
End With
'~~!

'~~Add And Configure Sheet2~~!
xlsheet2 = xlApp.Worksheets.Add

With xlSheet2
{Format sheet2}
rs.Close()
End With
'~~!

'~~Save File in Repository Folder~~!
xlbook.SaveAs(nPath & cl("CENTER").Value.ToString & ".xls")
xlApp.Quit()

'~~Close remaining excel process~~!
Call System.Runtime.InteropServices.Marshal.ReleaseComObject
(xlApp)

'~~Create Attachment from Excel File~~!
oAttach = New Attachment(nPath & cl("CENTER").Value.ToString
& ".xls")

'~~AddAttachment~~!
With msg
.Attachments.Add(oAttach)
End With

'~~Send Email~~!
smtp.Send(msg)

'~~Clear attachments for next email~~!
msg.Attachments.Clear()

cl.MoveNext()

Loop

cl = Nothing
rs = Nothing

Call System.Runtime.InteropServices.Marshal.ReleaseComObject
(xlApp)

Me.Close()

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, My.Application.Info.
Title)

End Try

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/vs-net-debugging/200809/1

.

Loading