473,322 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Save query return in variable

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
8 4794
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

"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
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
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
"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
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
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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Clay Beatty | last post by:
When you create database diagrams in Enterprise Manager, the details for constructing those diagrams is saved into the dtproperties table. This table includes an image field which contains most of...
5
by: George | last post by:
This program need to draw the some triangles into a 512 × 512 buffer (in memory). Or save it to a file. #include "project3.h" Image::Image(int xres, int yres): xres(xres), yres(yres) {...
8
by: MacDermott | last post by:
I have a query, which gathers up information, which is subsequently dumped into an instance of Excel using recordsetcopy. For one of the query fields, I have written what should be a pretty...
3
by: Felix Kater | last post by:
Hi, normally I use a local variable assigned to different return values inside the function and return it like this: int f(){ int my_err_code; my_err_code=1;
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
8
by: Roland Hall | last post by:
In Access you use "*" + + "*", + can be replaced with & Calling a parameterized query in Access requires % be used in place of *, however, all that I have read show dynamic SQL passed to Access: ...
2
by: Flic | last post by:
Hi, I have a basic db that I access with MySQL query browser. Everything seems fine to me but I am using this db as part of a php shopping basket and when I try to add an item I get: Notice:...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
1
by: kiranbabu | last post by:
<html> <head> <style type="text/css"> h2{color:#A02820} </style> </head> <script language=javascript>
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.