| Author |
Count # Rows Return From SQL Server?
|
|
| Chris Moore 2006-03-31, 6:55 pm |
| I am using the following code to pull some data from a SQL Server table.
I need to know how to fin dout the # of rows that were returned. Thanks
With cn1
.Provider = "SQLOLEDB.1"
.ConnectionString = "User ID=sa;password=sa;Initial
Catalog=AZ_Board_of_ME;Data Source = (local);Use Procedure for Prepare=
1;Auto Translate=True;Packet Size=4096"
.Open
End With
txtLog.Text = Trim(txtLog.Text ) & Chr(13) & Chr(10) & "Connected to
the Access database"
txtLog.Refresh
Set rs = New Recordset
sSQL = "SELECT * FROM MD_Data order by Name"
rs.Open sSQL, cn1, adOpenStatic, adLockOptimistic
| |
|
| Here:
Set rs = New Recordset
I would add the ADODB qualifier
Set rs = New ADODB.Recordset
Set cursor location
rs.CursorLocation = adClient
After opening it try:
msgbox rs.RecordCount
Saga
"Chris Moore" <chrisNON@SPAMMEdblayoutdotcom> wrote in message
news:Xns9797947576DC4cabubba@207.46.248.16...
>I am using the following code to pull some data from a SQL Server
>table.
> I need to know how to fin dout the # of rows that were returned.
> Thanks
>
> With cn1
> .Provider = "SQLOLEDB.1"
> .ConnectionString = "User ID=sa;password=sa;Initial
> Catalog=AZ_Board_of_ME;Data Source = (local);Use Procedure for
> Prepare=
> 1;Auto Translate=True;Packet Size=4096"
> .Open
> End With
>
> txtLog.Text = Trim(txtLog.Text ) & Chr(13) & Chr(10) & "Connected
> to
> the Access database"
> txtLog.Refresh
>
> Set rs = New Recordset
> sSQL = "SELECT * FROM MD_Data order by Name"
> rs.Open sSQL, cn1, adOpenStatic, adLockOptimistic
| |
| Jeff Johnson [MVP: VB] 2006-03-31, 6:55 pm |
|
"Saga" <antiSpam@somewhere.com> wrote in message
news:eicKCkQVGHA.224@TK2MSFTNGP10.phx.gbl...
> Set cursor location
>
> rs.CursorLocation = adClient
By using adOpenStatic you automatically get adUseClient. Gone are the days
when you could open a static recordset server-side.... (Well, unless you use
RDO!)
| |
| Chris Moore 2006-03-31, 6:55 pm |
| RecordCount returns -1. But I know there are over 35000 rows.
What am I missing?
Thanks
"Saga" <antiSpam@somewhere.com> wrote in news:eicKCkQVGHA.224
@TK2MSFTNGP10.phx.gbl:
> Here:
>
> Set rs = New Recordset
>
> I would add the ADODB qualifier
>
> Set rs = New ADODB.Recordset
>
> Set cursor location
>
> rs.CursorLocation = adClient
>
> After opening it try:
>
> msgbox rs.RecordCount
>
> Saga
>
>
> "Chris Moore" <chrisNON@SPAMMEdblayoutdotcom> wrote in message
> news:Xns9797947576DC4cabubba@207.46.248.16...
>
>
>
| |
| Chris Moore 2006-03-31, 6:55 pm |
| I am new to connecting to a SQL Server table. I have been looking for a
decent example. Is there a better way for me to connect that what I am
doing in my example?
Thanks
"Jeff Johnson [MVP: VB]" <i.get@enough.spam> wrote in
news:#hquisQVGHA.5660@TK2MSFTNGP12.phx.gbl:
>
> "Saga" <antiSpam@somewhere.com> wrote in message
> news:eicKCkQVGHA.224@TK2MSFTNGP10.phx.gbl...
>
>
> By using adOpenStatic you automatically get adUseClient. Gone are the
> days when you could open a static recordset server-side.... (Well,
> unless you use RDO!)
>
>
| |
|
|
Thanks for the heads up. I believed that the set cursor location
overrode the open statement.
Saga
"Jeff Johnson [MVP: VB]" <i.get@enough.spam> wrote in message
news:%23hquisQVGHA.5660@TK2MSFTNGP12.phx.gbl...
>
> "Saga" <antiSpam@somewhere.com> wrote in message
> news:eicKCkQVGHA.224@TK2MSFTNGP10.phx.gbl...
>
>
> By using adOpenStatic you automatically get adUseClient. Gone are the
> days when you could open a static recordset server-side.... (Well,
> unless you use RDO!)
>
| |
|
|
Try using the open statement without specifying the type or lock:
rs.Open sSQL, cn1
See if that makes a difference.
Otherwise, try doing an rs.movelast, then rs.movefirst, then seeing
what RecordCount is.
All is docuemnted, but I don't have the docs handy and I have a
photographic memory that gets exposed to light every day<g>.
There are some combinations of how you set the cursor location and the
recordset type that do not support the recordcount property, hence the
-1.
Goo dluck!
Saga
"Chris Moore" <chris@dblayoutdotcom> wrote in message
news:Xns9797A00B4A1CFcabubba@207.46.248.16...
> RecordCount returns -1. But I know there are over 35000 rows.
>
> What am I missing?
>
> Thanks
>
>
>
> "Saga" <antiSpam@somewhere.com> wrote in news:eicKCkQVGHA.224
> @TK2MSFTNGP10.phx.gbl:
>
>
| |
| Chris Moore 2006-03-31, 6:55 pm |
| I'll give it a shot. SQL Server doesn't like Movelast, it errors off
saying 'Rowset does not support fetching backward'. It works fine if I
am accessing an Access database. Removing those parameters did nothing.
Thanks for trying to help.
"Saga" <antiSpam@somewhere.com> wrote in news:uiCY0IRVGHA.5364
@tk2msftngp13.phx.gbl:
>
> Try using the open statement without specifying the type or lock:
>
> rs.Open sSQL, cn1
>
> See if that makes a difference.
>
> Otherwise, try doing an rs.movelast, then rs.movefirst, then seeing
> what RecordCount is.
>
> All is docuemnted, but I don't have the docs handy and I have a
> photographic memory that gets exposed to light every day<g>.
>
> There are some combinations of how you set the cursor location and the
> recordset type that do not support the recordcount property, hence the
> -1.
>
> Goo dluck!
> Saga
>
>
>
> "Chris Moore" <chris@dblayoutdotcom> wrote in message
> news:Xns9797A00B4A1CFcabubba@207.46.248.16...
>
>
>
| |
|
|
It is not SQL Server, it is the way that you are opening the
recordset. Look there, sorry that I can't help any further.
Good luck! Carbon units will persevere :-)
Saga
"Chris Moore" <chrisNON@SPAMdblayoutdotcom> wrote in message
news:Xns9797ABFECA225cabubba@207.46.248.16...
> I'll give it a shot. SQL Server doesn't like Movelast, it errors off
> saying 'Rowset does not support fetching backward'. It works fine if
> I
> am accessing an Access database. Removing those parameters did
> nothing.
>
> Thanks for trying to help.
>
>
> "Saga" <antiSpam@somewhere.com> wrote in news:uiCY0IRVGHA.5364
> @tk2msftngp13.phx.gbl:
>
>
|
|
|
|