By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

Save query return in variable

P: n/a
Hi. I would like to query a database, given several where clauses to refine
my search, and return the value of one single field in the database.

eg: I have a table that lists teachers. Their first name, last name, age,
unique teacher number, etc is in the file.

I want to return the unique teacher number, for example, of the teacher
whose first name is Jane and last name is Doe.

How does one do this?

Adrian
Jul 17 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
select teacherid from tblteachers where firstname = 'Jane' and lastname =
'Doe'

greets John

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z********************@news20.bellglobal.com. ..
Hi. I would like to query a database, given several where clauses to refine my search, and return the value of one single field in the database.

eg: I have a table that lists teachers. Their first name, last name, age, unique teacher number, etc is in the file.

I want to return the unique teacher number, for example, of the teacher
whose first name is Jane and last name is Doe.

How does one do this?

Adrian

Jul 17 '05 #2

P: n/a

"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
select teacherid from tblteachers where firstname = 'Jane' and lastname =
'Doe'
Thank you.

Can I do that without making a new RecordSet variable? Storing the entire
recordset just to return one integer seems wasteful.

Ideally I want something like this:

Dim driverID as integer
driverID = 'Query here which returns the driverID for one record from
database'
Adrian
"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z********************@news20.bellglobal.com. ..
Hi. I would like to query a database, given several where clauses to

refine
my search, and return the value of one single field in the database.

eg: I have a table that lists teachers. Their first name, last name,

age,
unique teacher number, etc is in the file.

I want to return the unique teacher number, for example, of the teacher
whose first name is Jane and last name is Doe.

How does one do this?

Jul 17 '05 #3

P: n/a
Yes you can make a function that will search your recordset and return the
id

Private function ReturnId(firstname as string,lastname as string) as long
dim rst as adodb.recordset
set rst = new adodb.recordset
with rst
.open "select teacherid from tblteachers where firstname = '" &
firstname "' and lastname = '" & lastname & "'", db, adOpenDynamic,
adLockReadOnly
if not .eof and not .bof then
.movefirst
returnid = .fields("teacherid").value
else
returid = 0 'no teacherid found
end if
.close
end with
set rst = nothing

end function

(PS function not tested)

Greets John

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z*******************@news20.bellglobal.com.. .

"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
select teacherid from tblteachers where firstname = 'Jane' and lastname = 'Doe'


Thank you.

Can I do that without making a new RecordSet variable? Storing the entire
recordset just to return one integer seems wasteful.

Ideally I want something like this:

Dim driverID as integer
driverID = 'Query here which returns the driverID for one record from
database'
Adrian
"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z********************@news20.bellglobal.com. ..
Hi. I would like to query a database, given several where clauses to

refine
my search, and return the value of one single field in the database.

eg: I have a table that lists teachers. Their first name, last name,

age,
unique teacher number, etc is in the file.

I want to return the unique teacher number, for example, of the teacher whose first name is Jane and last name is Doe.

How does one do this?


Jul 17 '05 #4

P: n/a
Then off course you have to call the function

Dim driverID as long
driverID = ReturnId("Jane","Doe")

greets John

"John Lauwers" <no****@fictief.com> schreef in bericht
news:40*********************@news.skynet.be...
Yes you can make a function that will search your recordset and return the
id

Private function ReturnId(firstname as string,lastname as string) as long
dim rst as adodb.recordset
set rst = new adodb.recordset
with rst
.open "select teacherid from tblteachers where firstname = '" &
firstname "' and lastname = '" & lastname & "'", db, adOpenDynamic,
adLockReadOnly
if not .eof and not .bof then
.movefirst
returnid = .fields("teacherid").value
else
returid = 0 'no teacherid found
end if
.close
end with
set rst = nothing

end function

(PS function not tested)

Greets John

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z*******************@news20.bellglobal.com.. .

"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
select teacherid from tblteachers where firstname = 'Jane' and lastname
=
'Doe'


Thank you.

Can I do that without making a new RecordSet variable? Storing the entire
recordset just to return one integer seems wasteful.

Ideally I want something like this:

Dim driverID as integer
driverID = 'Query here which returns the driverID for one record from
database'
Adrian
"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z********************@news20.bellglobal.com. ..
> Hi. I would like to query a database, given several where clauses

to refine
> my search, and return the value of one single field in the database.
>
> eg: I have a table that lists teachers. Their first name, last name, age,
> unique teacher number, etc is in the file.
>
> I want to return the unique teacher number, for example, of the

teacher > whose first name is Jane and last name is Doe.
>
> How does one do this?



Jul 17 '05 #5

P: n/a
"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
Yes you can make a function that will search your recordset and return the
id

Private function ReturnId(firstname as string,lastname as string) as long
dim rst as adodb.recordset
set rst = new adodb.recordset
with rst
.open "select teacherid from tblteachers where firstname = '" &
firstname "' and lastname = '" & lastname & "'", db, adOpenDynamic,
adLockReadOnly
if not .eof and not .bof then
.movefirst
returnid = .fields("teacherid").value
else
returid = 0 'no teacherid found
end if
.close
end with
set rst = nothing

But this will return the entire database into the recordset, and I
understand it, and then put the userID into returnID. That is not very
scallable if the database is very large.

Adrian

end function

(PS function not tested)

Greets John

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z*******************@news20.bellglobal.com.. .

"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
select teacherid from tblteachers where firstname = 'Jane' and lastname
=
'Doe'


Thank you.

Can I do that without making a new RecordSet variable? Storing the entire
recordset just to return one integer seems wasteful.

Ideally I want something like this:

Dim driverID as integer
driverID = 'Query here which returns the driverID for one record from
database'
Adrian
"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z********************@news20.bellglobal.com. ..
> Hi. I would like to query a database, given several where clauses

to refine
> my search, and return the value of one single field in the database.
>
> eg: I have a table that lists teachers. Their first name, last name, age,
> unique teacher number, etc is in the file.
>
> I want to return the unique teacher number, for example, of the

teacher > whose first name is Jane and last name is Doe.
>
> How does one do this?



Jul 17 '05 #6

P: n/a
Try to search msdn for the execute command of a connection , that is maybe
what you are searching for

greets John
"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:Z_*******************@news20.bellglobal.com.. .
"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
Yes you can make a function that will search your recordset and return the
id

Private function ReturnId(firstname as string,lastname as string) as long dim rst as adodb.recordset
set rst = new adodb.recordset
with rst
.open "select teacherid from tblteachers where firstname = '" &
firstname "' and lastname = '" & lastname & "'", db, adOpenDynamic,
adLockReadOnly
if not .eof and not .bof then
.movefirst
returnid = .fields("teacherid").value
else
returid = 0 'no teacherid found
end if
.close
end with
set rst = nothing

But this will return the entire database into the recordset, and I
understand it, and then put the userID into returnID. That is not very
scallable if the database is very large.

Adrian

end function

(PS function not tested)

Greets John

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z*******************@news20.bellglobal.com.. .

"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
> select teacherid from tblteachers where firstname = 'Jane' and

lastname
=
> 'Doe'

Thank you.

Can I do that without making a new RecordSet variable? Storing the

entire recordset just to return one integer seems wasteful.

Ideally I want something like this:

Dim driverID as integer
driverID = 'Query here which returns the driverID for one record from
database'
Adrian

> "Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht > news:1Z********************@news20.bellglobal.com. ..
> > Hi. I would like to query a database, given several where clauses to > refine
> > my search, and return the value of one single field in the database. > >
> > eg: I have a table that lists teachers. Their first name, last name, > age,
> > unique teacher number, etc is in the file.
> >
> > I want to return the unique teacher number, for example, of the

teacher
> > whose first name is Jane and last name is Doe.
> >
> > How does one do this?



Jul 17 '05 #7

P: n/a
Like:

db.execute(DELETE from SomeTable Where someThing = SomeOtherThing....

I think .execute() just covers Action Queries to Access does it not? I'll
check MSDN, which I don't own, at school tomorrow.

I just have student edition of VB, which has no MSDN.

Adrian
"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
Try to search msdn for the execute command of a connection , that is maybe
what you are searching for

greets John
"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:Z_*******************@news20.bellglobal.com.. .
"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
Yes you can make a function that will search your recordset and return the id

Private function ReturnId(firstname as string,lastname as string) as long dim rst as adodb.recordset
set rst = new adodb.recordset
with rst
.open "select teacherid from tblteachers where firstname = '" & firstname "' and lastname = '" & lastname & "'", db, adOpenDynamic,
adLockReadOnly
if not .eof and not .bof then
.movefirst
returnid = .fields("teacherid").value
else
returid = 0 'no teacherid found
end if
.close
end with
set rst = nothing

But this will return the entire database into the recordset, and I
understand it, and then put the userID into returnID. That is not very
scallable if the database is very large.

Adrian

end function

(PS function not tested)

Greets John

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z*******************@news20.bellglobal.com.. .
>
> "John Lauwers" <no****@fictief.com> wrote in message
> news:40*********************@news.skynet.be...
> > select teacherid from tblteachers where firstname = 'Jane' and

lastname
=
> > 'Doe'
>
> Thank you.
>
> Can I do that without making a new RecordSet variable? Storing the

entire
> recordset just to return one integer seems wasteful.
>
> Ideally I want something like this:
>
> Dim driverID as integer
> driverID = 'Query here which returns the driverID for one record from > database'
>
>
> Adrian
>
>
>
> > "Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht > > news:1Z********************@news20.bellglobal.com. ..
> > > Hi. I would like to query a database, given several where
clauses
to
> > refine
> > > my search, and return the value of one single field in the

database. > > >
> > > eg: I have a table that lists teachers. Their first name, last

name,
> > age,
> > > unique teacher number, etc is in the file.
> > >
> > > I want to return the unique teacher number, for example, of the
teacher
> > > whose first name is Jane and last name is Doe.
> > >
> > > How does one do this?
>
>



Jul 17 '05 #8

P: n/a

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:Z_*******************@news20.bellglobal.com.. .
"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
Yes you can make a function that will search your recordset and return the
id

Private function ReturnId(firstname as string,lastname as string) as long dim rst as adodb.recordset
set rst = new adodb.recordset
with rst
.open "select teacherid from tblteachers where firstname = '" &
firstname "' and lastname = '" & lastname & "'", db, adOpenDynamic,
adLockReadOnly
if not .eof and not .bof then
.movefirst
returnid = .fields("teacherid").value
else
returid = 0 'no teacherid found
end if
.close
end with
set rst = nothing

But this will return the entire database into the recordset, and I
understand it, and then put the userID into returnID. That is not very
scallable if the database is very large.

Adrian


John has the right approach.

The Recorset Query will only return the data requested - not the entire
database. More than likely the recordset will only contain 1 record
consisting of 1 field (*teacherID*). A Recordset is not the same as a
Database. A Recordset is a Table. The Query designates the amount and type
of data returned/referenced in that table.

Regards
Mike



end function

(PS function not tested)

Greets John

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:1Z*******************@news20.bellglobal.com.. .

"John Lauwers" <no****@fictief.com> wrote in message
news:40*********************@news.skynet.be...
> select teacherid from tblteachers where firstname = 'Jane' and

lastname
=
> 'Doe'

Thank you.

Can I do that without making a new RecordSet variable? Storing the

entire recordset just to return one integer seems wasteful.

Ideally I want something like this:

Dim driverID as integer
driverID = 'Query here which returns the driverID for one record from
database'
Adrian

> "Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht > news:1Z********************@news20.bellglobal.com. ..
> > Hi. I would like to query a database, given several where clauses to > refine
> > my search, and return the value of one single field in the database. > >
> > eg: I have a table that lists teachers. Their first name, last name, > age,
> > unique teacher number, etc is in the file.
> >
> > I want to return the unique teacher number, for example, of the

teacher
> > whose first name is Jane and last name is Doe.
> >
> > How does one do this?



Jul 17 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.