FoxPro Help and Support - Newbie SQL Server query

Author

Newbie SQL Server query

2005-07-06, 8:25 pm

Please help, I can't even get the simplest query to work. What am I doing
wrong in this code. rsResult.recordcount always equals -1. I have tried a
few different queries that must return some rows but always the same.
Thanks.

dcnDB = CreateObject("ADODB.Connection")

dbStr = "Provider=SQLOLEDB.1;Initial Catalog=TESNEW;Data
Source=paubne17;User
ID=sa;Password=sa;"

dcnDB.open( dbStr )

IF dcnDB.errors.count <> 0 THEN
MESSAGEBOX("Could not connect to database")
RETURN
ENDIF

LOCAL rsResult as adodb.recordset
rsResult = CREATEOBJECT("adodb.recordset")
rsResult = dcnDB.execute("SELECT * from sysobjects")
ntemp = rsResult.recordcount

man-wai chang

2005-07-07, 3:30 am

shouldn't you use adodb.command object to execute sql statement?

> LOCAL rsResult as adodb.recordset
> rsResult = CREATEOBJECT("adodb.recordset")
> rsResult = dcnDB.execute("SELECT * from sysobjects")
> ntemp = rsResult.recordcount
>
>

Jack Jackson

2005-07-07, 3:30 am

On Thu, 7 Jul 2005 10:16:31 +1000, "lh" <lh@nowhere.com> wrote:

>Please help, I can't even get the simplest query to work. What am I doing
>wrong in this code. rsResult.recordcount always equals -1. I have tried a
>few different queries that must return some rows but always the same.
>Thanks.
>
>
>
>dcnDB = CreateObject("ADODB.Connection")
>
>dbStr = "Provider=SQLOLEDB.1;Initial Catalog=TESNEW;Data
> Source=paubne17;User
ID=sa;Password=sa;"
>
>dcnDB.open( dbStr )
>
>IF dcnDB.errors.count <> 0 THEN
> MESSAGEBOX("Could not connect to database")
> RETURN
>ENDIF
>
>LOCAL rsResult as adodb.recordset
>rsResult = CREATEOBJECT("adodb.recordset")
>rsResult = dcnDB.execute("SELECT * from sysobjects")
>ntemp = rsResult.recordcount
>

For one thing, you haven't associated your connection object with the
recordset. I think this is correct:

rsResult.ActiveConnection = dcnDB

2005-07-07, 3:30 am

I am missing something thats for sure. Whether I experiment with
adodb.command or assigning the connection to the recordset makes no diff,
recordcount always = -1. I have no clue as to why this doesn't work.

LOCAL dcnDB as ADODB.connection
dcnDB = CreateObject("ADODB.Connection")
dbStr = "Provider=SQLOLEDB.1;Initial Catalog=TESNEW;Data Source=paubne17;" +
;
"User ID=sa;Password=sa;"

dcnDB.open( dbStr )
IF dcnDB.errors.count <> 0 THEN
MESSAGEBOX("Could not connect to database")
RETURN
ENDIF

LOCAL rsResult as adodb.recordset
rsResult = CREATEOBJECT("adodb.recordset")

LOCAL loCommand as adodb.command
loCommand = CREATEOBJECT("adodb.command")

rsResult.ActiveConnection = dcnDB
loCommand.ActiveConnection = dcnDB

rsResult = dcnDB.execute("SELECT * from sysobjects")
ntemp = rsResult.recordcount

loCommand.CommandText = "SELECT * from sysobjects"
rsResult = loCommand.Execute
ntemp = rsResult.recordcount

"Jack Jackson" < jacknospam@pebblerid
ge.com> wrote in message
news:a94pc1p4t0jndbs
8k7n096lbct080cjgj8@
4ax.com...
> On Thu, 7 Jul 2005 10:16:31 +1000, "lh" <lh@nowhere.com> wrote:
>
>
> For one thing, you haven't associated your connection object with the
> recordset. I think this is correct:
>
> rsResult.ActiveConnection = dcnDB

Cindy Winegarden

2005-07-07, 9:24 am

Hi LH,

Did you know that you can bypass ADO and select data directly into a FoxPro
cursor, or create a parameterized, updatable view?

For the data selection look at SQLStringConnect() and SQLExec(). For the
view create a connection in your database and use the view designer to
create a view.

--
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"lh" <lh@nowhere.com> wrote in message
news:Av_ye.23400$Le2.151539@nasal.pacific.net.au...
> Please help, I can't even get the simplest query to work. What am I doing
> wrong in this code. rsResult.recordcount always equals -1. I have tried
> a few different queries that must return some rows but always the same.
> Thanks.
>
>
>
> dcnDB = CreateObject("ADODB.Connection")
>
> dbStr = "Provider=SQLOLEDB.1;Initial Catalog=TESNEW;Data
> Source=paubne17;User
ID=sa;Password=sa;"
>
> dcnDB.open( dbStr )
>
> IF dcnDB.errors.count <> 0 THEN
> MESSAGEBOX("Could not connect to database")
> RETURN
> ENDIF
>
> LOCAL rsResult as adodb.recordset
> rsResult = CREATEOBJECT("adodb.recordset")
> rsResult = dcnDB.execute("SELECT * from sysobjects")
> ntemp = rsResult.recordcount
>

Craig Berntson

2005-07-07, 11:25 am

Yes, you can do that, but it uses ODBC, which may not be desirable.

--
Craig Berntson
MCSD, Visual FoxPro MVP
www.craigberntson.com
Salt Lake City Fox User Group
www.slcfox.org
www.foxcentral.net

"Cindy Winegarden" < cindy_winegarden@msn
.com> wrote in message
news:uNWCWPwgFHA.2372@TK2MSFTNGP14.phx.gbl...
> Hi LH,
>
> Did you know that you can bypass ADO and select data directly into a
> FoxPro cursor, or create a parameterized, updatable view?
>
> For the data selection look at SQLStringConnect() and SQLExec(). For the
> view create a connection in your database and use the view designer to
> create a view.
>
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "lh" <lh@nowhere.com> wrote in message
> news:Av_ye.23400$Le2.151539@nasal.pacific.net.au...
>
>

2005-07-07, 8:30 pm

Yes, I have done that now to solve my immediate needs. I was just
experimenting with something new.

"Cindy Winegarden" < cindy_winegarden@msn
.com> wrote in message
news:uNWCWPwgFHA.2372@TK2MSFTNGP14.phx.gbl...
> Hi LH,
>
> Did you know that you can bypass ADO and select data directly into a
> FoxPro cursor, or create a parameterized, updatable view?
>
> For the data selection look at SQLStringConnect() and SQLExec(). For the
> view create a connection in your database and use the view designer to
> create a view.
>
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "lh" <lh@nowhere.com> wrote in message
> news:Av_ye.23400$Le2.151539@nasal.pacific.net.au...
>
>

Sponsored Links