Re: ADO Record Count Issue

You are right about things that should be simple.

Your dynamic cursor allows for runtime updates so it can not determine the
exact count from your query and returns -1. I checked my code, I use
RecordCount a lot and all of those queries use adOpenStatic for the cursor
type.

You can check the ADO documentation that comes with the MDAC SDK for more
information on this.

Good Luck.
--
Scott


"Krzysztof via DotNetMonster.com" wrote:

Found it.

If it helps anyone, when i was opening the recordsert, i was using cursortype
dynamic, instead of static. i am not 100% sure what they do, although i know
it affects your working relationship when using your recordset.

HTH,
K

Krzysztof wrote:
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


.