VBS Recordcount issue 3

[BarryCrosby]

Hi,
I have the following script to connect to a database to check a value that has been submitted via an input box. The three scenarios are:
1) A single match exists
2) Duplicate matches exist
3) No matches exist

However when I am trying to test scenario all I get is a record count of -1?????

Any help would be much appreciated. It's been a little while since I dabbled with VBS so I think I am out of practice. Below is the script I have so far.

'Declare the parameters used in this VBScript.
Dim sSQL
Dim sServer
Dim sDB
Dim slogin
Dim sPwd
Dim oFso, oExec, rc
Dim oRs
Dim vRows
Dim iFieldCount
Dim iRecordCount
Dim EventRef
Dim EventType
Dim EventId
Dim strReqDateTime
Dim FilePrefix
Dim ImportFileName
Dim fname

fname=Inputbox("Enter the affected users login:")

strReqDateTime = Year(Now) & Right("0" & Month(Now),2) & Right("0" & Day(Now),2) & Right("0" & Hour(Now),2) & Right("0" & Minute(Now),2) & Right("0" & second(Now),2)

'Set the Parameters in order to Connect to the database
sSQL = "SQLServer"
sServer = "127.0.0.1\SQLEXPRESS"
sDB = "aweb75demo"
sLogin = "barry"
sPwd = "barry"
FilePrefix = "C:\\Imports\\"
ImportFileName = FilePrefix & "FTF_newcall_" & strReqDateTime & ".imp"

'Create the connection to the database and open a recordset.
Set oCn = CreateObject( "ADODB.Connection" )
Set oRs = CreateObject( "ADODB.Recordset" )
'create the connection to a file to create for import

'construct the connectionstring
oCn.ConnectionString = "PROVIDER=SQLOLEDB" & _
";SERVER=" & sServer & _
";UID=" & sLogin & _
";PWD=" & sPwd & _
";DATABASE=" & sDB & " "
'...and open the connection to the database
oCn.Open

test = 1

'Create the SQL and execute
SQLLINE = "SELECT usr.usr_sc FROM usr WHERE usr_sc = '" & ucase(fname) & "'"

msgbox(SQLLINE)
Set oRs = oCn.Execute(SQLLINE)
'oRs.Movefirst
msgbox("test")
if not oRs.EOF then
If oRs.Fields(0).Value = ucase(fname) Then
msgbox("There are records")
msgbox(oRs.Fields(0).Value)
msgbox oRs.Fields("USR_SC").Value
msgbox oRs.recordcount
end if
else
msgbox("There are no matching records")
msgbox oRs.recordcount
end if

if not oRs.EOF then
Call WriteAIMFile()
'Call ACLI()
oRs.Close
'Clear SSQL and recordset
Set oRs=Nothing
oCn.Close
Set objConnection=Nothing
Set oFso=Nothing
wscript.quit
else
fname = inputbox("You have entered an incorrect login, please enter the correct affected users login:")
if not oRs.EOF then
Call WriteAIMFile()
'Call ACLI()
oRs.Close
'Clear SSQL and recordset
Set oRs=Nothing
oCn.Close
Set objConnection=Nothing
Set oFso=Nothing
wscript.quit
else
Msgbox("You have entered an incorrect login twice. Please click on the toolbar to run this script again to try again.")
End if
End if


Sub WriteAIMFile()
Set oFso = CreateObject("Scripting.FileSystemObject")
Set newevent = oFso.CreateTextFile(ImportFileName, True)
newevent.writeline("@*@AFFECTED@*@:"&ucase(fname))
newevent.writeline("@*@ITEM@*@:AD SECURITY")
newevent.writeline("@*@CATEGORY@*@:ACCOUNT LOCKOUT")
newevent.writeline("1st Time Fix")
newevent.writeline("Please unlock network account")
newevent.writeline("User ID: "&ucase(fname))
newevent.writeline("|END OF INCIDENT|")
newevent.close
Set fso=Nothing
End Sub

Sub ACLI()
'msgbox("Launch ACLI")
'Then Proceed with ACLI
Set WshShell = CreateObject("WScript.Shell")
CommandLine = """C:\Program Files\assyst Enterprise 8.0\acli.exe"""
CommandLine = CommandLine &" -v:" &sSQL &" -h:"&sServer &" -d:" &sDB &" -u:" &slogin &" -p:"&sPwd
CommandLine = CommandLine & " -f:"""& ImportFileName & """ -t -ep:ANET -ap:ANET -ui:n"
WshShell.Exec(CommandLine)
'msgbox(commandline)
End Sub

 

[RoyVidar]

I've never quite trusted the .recordcount of ADO recordsets, and rather use a count(*) using the same condition (WHERE clause) in stead.

Here is one discussion on the topic, with different opinions and methods (and a couple of links) thread709-1052131

BTW - since this is vbscript, shouldn't this question rather bee in forum329

Roy-Vidar

 

[BarryCrosby]

Thanks Roy-Vidar using count(*) does the trick,
Apologies for putting it in the wrong forum. I've joined the VBScript one now, so will use this in future.

Barry

 

[Andrzejek]

Isn’t that redundant? (code in blue)

[green]'Create the SQL and execute[/green]
SQLLINE = "SELECT usr.usr_sc FROM usr WHERE [blue]usr_sc = '" & ucase(fname) & "'"[/blue]

msgbox(SQLLINE)
Set oRs = oCn.Execute(SQLLINE)
[green]'oRs.Movefirst[/green]
msgbox("test")
if not oRs.EOF then
    [blue]If oRs.Fields(0).Value = ucase(fname) Then[/blue]
        msgbox("There are records")

If the recordset has any records, oRs.Fields(0).Value HAS to be equal UCase(fname), that's your requirement from your SQL.

Have fun.

---- Andy

 

[TysonLPrice]

In order to get the row count you nee to use the right cursor options. I don't know them that well but this:

adOpenForwardOnly, adLockReadOnly

Always brings a record count back to me.

 

[genomon]

You can cheat by using a SQL update with an ADO command object:

    strSQL = "UPDATE tblValidHerds SET whatever = itself WHERE ..."
    cmdCommand.CommandText = strSQL
    cmdCommand.Execute lngRecordCount

The optional variable lngRecordCount returns the number of affected records.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[dilettante]

The documentation can be enlightening: RecordCount Property (ADO)
.

I've also seen many sources say:

If you use COUNT(*) the query will retrieve all fields from the table in order to calculate count. To avoid this we can use COUNT(1) instead of COUNT(*). It will merely retrieve the numeric value of 1 for each record that meets your criteria and count those. This can be used as a performance tip.

Shave some more cost if you use the option adExecuteRecord on a Connection.Execute, which returns a single Record object instead of a whole Recordset object.

 

[JoeAtWork]

A star today for dilettante, who has taught me something new and useful.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[TysonLPrice]

I'm not aware of .RecordCount bringing back the wrong count. The suggested methods hit the database twice. One for the count and one for the data (unless the count is included in the original request). Why not just use the correct cursor settings?

 

[strongm]

>you nee to use the right cursor options. I don't know them that well but this:

>adOpenForwardOnly, adLockReadOnly

>Always brings a record count back to me.

<ahem> adOpenForwardOnly is not[ the right cursor option to bring back record count. Indeed it is clearly documented that it doesn't. The reason you may have 'always' been getting an accurate record count back will almost certainly be because you have been requesting a client-side cursor, and that gives you a static cursor even when you ask for a forwardonly cursor (it also means that you may have been operating under the illusion that you have been working with a nice fast, lightweight cursor, and you haven't ...)

 

[strongm]

>we can use COUNT(1) instead of COUNT(*) ... a performance tip

Myth, I'm afraid - unless you are using a fairly ancient RDBMS, generally without an optimiser (e.g. more than 10 years old in the case of Oracle; you have to go back to version 7 for it to be true). Just check the execution plans for Select Count(*) and for Select Count(1) on Oracle (or SQL Server) and you will find that they are the same.

 

[SBerthold]

Quite so - I was also going to say...
COUNT(1) and COUNT(*) should be the same concerning performance.

Also, just to mention it, using the adExecuteRecord (a hidden enum value) option is only possible if the provider supports this, which not all do.

When using COUNT, the performance hit is when COUNT(SomeField) is used, whether criteria is used or not.

If using COUNT(SomeField) this way, or there is criteria in the SQL Statement, if the fields used have NULLs, by default they will usually not be included in the count. In order to include the NULLs in the count under these two conditions, the fields with NULLs can usually be included NULLs in the criteria (...WHERE SomeField1 ='xyz' AND (NOT SomeField1 IS NULL), provider depending.


>I'm not aware of .RecordCount bringing back the wrong count.

It will depending where the CursorLocation is, and how the provider handles these. If set to adUseClient, then it should always return the correct record count, but you need to use batch updating or an sql UPDATE statement in order to have updates reflect changes in the db table.
If set to adUseServer, then you cannot get a record count when using a CursorType of adOpenForwardOnly, such as is the case when opening the recordset through the connection or command .Execute method, but need to instead use one of the adOpenKeyset or adOpenStatic cursor types through the rs.Open method, and then call the .MoveLast .MoveFirst method to make sure you get an accurate record count (some providers will fill the cursor completely and therefore .MoveLast is not always needed).

BarryCrosby, if you instead use instead rs.Open and a cursor location and type used under the mentioned conditions, then you should get an accurate record count.
If you want to stay with using the .Execute method to return the recordset, or explicitly use a adOpenForwardOnly cursor type, then you will need to additionally use a COUNT() SQL Statement, as mentioned.

thread222-1359107 also discusses record counts and how cursor types and locations affect it.

 

[SBerthold]

strongm, somehow I over looked your first post, so I didn't need to mention the same....

 

[dilettante]

So the conclusion would be "Use RecordCount - and when you can't, do a COUNT(*)?

 

[mattKnight]

I always thought that

select count(PrimaryIndexField) where ...

was more efficient than

select count(*) where ...

or is this optimiser dependant?

(I don't have an SQL server to test against at the moment)

Take Care

Matt
I have always wished that my computer would be as easy to use as my telephone.
My wish has come true. I no longer know how to use my telephone.

 

[gmmastros]

Just to clarify....

Count(*) returns the count of all rows in the table.
Count(1) returns a count of all rows in the table.
Count(ColumnName) returns the number of rows in the table where the value in the column is NOT NULL.

With SQL Server, count will use an index scan to return the results (unless it's a heap table without any indexes, then it will table scan).

So, Matt, Count(*), Count(1) and Count(PrimaryKeyColumn) will always return the same count because a Primary Key Column cannot contain null values. Both queries would perform an index scan or clustered index scan.

I just checked, and all queries produced an identical execution plan and ran in the same amount of time.

If you really want to get the count of all rows in a table, and there is a primary key column, there is a faster way than COUNT. In SQL Server, there are many system tables that keep track of various things. Of particular interest is the sysindex system table. It tracks the number of rows in an index. So you could do...

Select  rowcnt
From    sysindexes 
Where   Status & 2048 = 2048 [green]-- Primary key index[/green]
        And Status & 2 = 2 [green]-- Unique index[/green]
        And id = Object_Id('YourTableNameHere')

Since there can be multiple indexes, we want to make sure we are using the unique primary key index to get our counts. That's where there are 2 filter conditions. Also, consider that there are usually more rows in a table than there are tables in database. In my DB, there's only 760 rows in the sysindex table. Add to that... the sysindex table is indexed too. This query will perform a clustered index seek to return the row count instead of a scan. As such, it's many many many times faster.

I have a Numbers table with 1 integer column, clustered primary key with 1,000,000 rows. Running this code...

Declare @Start DateTime
Set @Start = GetDate()

Select Count(*) From Numbers

Select DateDiff(Millisecond, @Start, GetDate())
Set @Start = GetDate()

Select  rowcnt
From    sysindexes 
Where   Status & 2048 = 2048
        And Status & 2 = 2
        And id = Object_Id('Numbers')

Select DateDiff(Millisecond, @Start, GetDate())

It takes approximately 500 milliseconds for the first count and 16 milliseconds for the second.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dilettante]

Then I suppose you have odd variations like:

    Set rsStats = cn.OpenSchema(adSchemaStatistics)
    With rsStats
        .Filter = "TABLE_NAME = 'MyTable'"
        lngRecords = !CARDINALITY
        .Close
    End With

Which has costs of its own of course.

 

[mattKnight]

Thanks George...

That's a good technique - I'll try and remember for the next time I need to do some coding...

Take Care

Matt
I have always wished that my computer would be as easy to use as my telephone.
My wish has come true. I no longer know how to use my telephone.