Save query return in variable

[Adrian Parker]

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

[John Lauwers]

Re: Save query return in variable

select teacherid from tblteachers where firstname = 'Jane' and lastname =
'Doe'

greets John

"Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
news:1ZaPb.1051 4$cQ6.319437@ne ws20.bellglobal .com...[color=blue]
> Hi. I would like to query a database, given several where clauses to[/color]
refine[color=blue]
> 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,[/color]
age,[color=blue]
> 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
>
>[/color]

[Adrian Parker]

Re: Save query return in variable


"John Lauwers" <nobody@fictief .com> wrote in message
news:400d43db$0 $296$ba620e4c@n ews.skynet.be.. .[color=blue]
> select teacherid from tblteachers where firstname = 'Jane' and lastname =
> 'Doe'[/color]

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


[color=blue]
> "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> news:1ZaPb.1051 4$cQ6.319437@ne ws20.bellglobal .com...[color=green]
> > Hi. I would like to query a database, given several where clauses to[/color]
> refine[color=green]
> > 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,[/color]
> age,[color=green]
> > 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?[/color][/color]

[John Lauwers]

Re: Save query return in variable

Yes you can make a function that will search your recordset and return the
id

Private function ReturnId(firstn ame 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("teache rid").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" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
news:1ZePb.5212 $rW5.293982@new s20.bellglobal. com...[color=blue]
>
> "John Lauwers" <nobody@fictief .com> wrote in message
> news:400d43db$0 $296$ba620e4c@n ews.skynet.be.. .[color=green]
> > select teacherid from tblteachers where firstname = 'Jane' and lastname[/color][/color]
=[color=blue][color=green]
> > 'Doe'[/color]
>
> 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
>
>
>[color=green]
> > "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> > news:1ZaPb.1051 4$cQ6.319437@ne ws20.bellglobal .com...[color=darkred]
> > > Hi. I would like to query a database, given several where clauses to[/color]
> > refine[color=darkred]
> > > 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,[/color]
> > age,[color=darkred]
> > > unique teacher number, etc is in the file.
> > >
> > > I want to return the unique teacher number, for example, of the[/color][/color][/color]
teacher[color=blue][color=green][color=darkred]
> > > whose first name is Jane and last name is Doe.
> > >
> > > How does one do this?[/color][/color]
>
>[/color]

[John Lauwers]

Re: Save query return in variable

Then off course you have to call the function

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

greets John

"John Lauwers" <nobody@fictief .com> schreef in bericht
news:400f9587$0 $316$ba620e4c@n ews.skynet.be.. .[color=blue]
> Yes you can make a function that will search your recordset and return the
> id
>
> Private function ReturnId(firstn ame 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("teache rid").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" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> news:1ZePb.5212 $rW5.293982@new s20.bellglobal. com...[color=green]
> >
> > "John Lauwers" <nobody@fictief .com> wrote in message
> > news:400d43db$0 $296$ba620e4c@n ews.skynet.be.. .[color=darkred]
> > > select teacherid from tblteachers where firstname = 'Jane' and[/color][/color][/color]
lastname[color=blue]
> =[color=green][color=darkred]
> > > 'Doe'[/color]
> >
> > Thank you.
> >
> > Can I do that without making a new RecordSet variable? Storing the[/color][/color]
entire[color=blue][color=green]
> > 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
> >
> >
> >[color=darkred]
> > > "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> > > news:1ZaPb.1051 4$cQ6.319437@ne ws20.bellglobal .com...
> > > > Hi. I would like to query a database, given several where clauses[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
name,[color=blue][color=green][color=darkred]
> > > age,
> > > > unique teacher number, etc is in the file.
> > > >
> > > > I want to return the unique teacher number, for example, of the[/color][/color]
> teacher[color=green][color=darkred]
> > > > whose first name is Jane and last name is Doe.
> > > >
> > > > How does one do this?[/color]
> >
> >[/color]
>
>[/color]

[Adrian Parker]

Re: Save query return in variable

"John Lauwers" <nobody@fictief .com> wrote in message
news:400f9587$0 $316$ba620e4c@n ews.skynet.be.. .[color=blue]
> Yes you can make a function that will search your recordset and return the
> id
>
> Private function ReturnId(firstn ame 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("teache rid").value
> else
> returid = 0 'no teacherid found
> end if
> .close
> end with
> set rst = nothing[/color]


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


[color=blue]
>
> end function
>
> (PS function not tested)
>
> Greets John
>
> "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> news:1ZePb.5212 $rW5.293982@new s20.bellglobal. com...[color=green]
> >
> > "John Lauwers" <nobody@fictief .com> wrote in message
> > news:400d43db$0 $296$ba620e4c@n ews.skynet.be.. .[color=darkred]
> > > select teacherid from tblteachers where firstname = 'Jane' and[/color][/color][/color]
lastname[color=blue]
> =[color=green][color=darkred]
> > > 'Doe'[/color]
> >
> > Thank you.
> >
> > Can I do that without making a new RecordSet variable? Storing the[/color][/color]
entire[color=blue][color=green]
> > 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
> >
> >
> >[color=darkred]
> > > "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> > > news:1ZaPb.1051 4$cQ6.319437@ne ws20.bellglobal .com...
> > > > Hi. I would like to query a database, given several where clauses[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
name,[color=blue][color=green][color=darkred]
> > > age,
> > > > unique teacher number, etc is in the file.
> > > >
> > > > I want to return the unique teacher number, for example, of the[/color][/color]
> teacher[color=green][color=darkred]
> > > > whose first name is Jane and last name is Doe.
> > > >
> > > > How does one do this?[/color]
> >
> >[/color]
>
>[/color]

[John Lauwers]

Re: Save query return in variable

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

greets John


"Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
news:Z_VPb.6384 $rW5.504007@new s20.bellglobal. com...[color=blue]
> "John Lauwers" <nobody@fictief .com> wrote in message
> news:400f9587$0 $316$ba620e4c@n ews.skynet.be.. .[color=green]
> > Yes you can make a function that will search your recordset and return[/color][/color]
the[color=blue][color=green]
> > id
> >
> > Private function ReturnId(firstn ame as string,lastname as string) as[/color][/color]
long[color=blue][color=green]
> > 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("teache rid").value
> > else
> > returid = 0 'no teacherid found
> > end if
> > .close
> > end with
> > set rst = nothing[/color]
>
>
> 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
>
>
>[color=green]
> >
> > end function
> >
> > (PS function not tested)
> >
> > Greets John
> >
> > "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> > news:1ZePb.5212 $rW5.293982@new s20.bellglobal. com...[color=darkred]
> > >
> > > "John Lauwers" <nobody@fictief .com> wrote in message
> > > news:400d43db$0 $296$ba620e4c@n ews.skynet.be.. .
> > > > select teacherid from tblteachers where firstname = 'Jane' and[/color][/color]
> lastname[color=green]
> > =[color=darkred]
> > > > 'Doe'
> > >
> > > Thank you.
> > >
> > > Can I do that without making a new RecordSet variable? Storing the[/color][/color]
> entire[color=green][color=darkred]
> > > 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" <adrian.parker@ NOSPAMsympatico .ca> schreef in[/color][/color][/color]
bericht[color=blue][color=green][color=darkred]
> > > > news:1ZaPb.1051 4$cQ6.319437@ne ws20.bellglobal .com...
> > > > > Hi. I would like to query a database, given several where clauses[/color][/color]
> to[color=green][color=darkred]
> > > > refine
> > > > > my search, and return the value of one single field in the[/color][/color][/color]
database.[color=blue][color=green][color=darkred]
> > > > >
> > > > > eg: I have a table that lists teachers. Their first name, last[/color][/color]
> name,[color=green][color=darkred]
> > > > age,
> > > > > unique teacher number, etc is in the file.
> > > > >
> > > > > I want to return the unique teacher number, for example, of the[/color]
> > teacher[color=darkred]
> > > > > whose first name is Jane and last name is Doe.
> > > > >
> > > > > How does one do this?
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Adrian Parker]

Re: Save query return in variable

Like:

db.execute(DELE TE 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" <nobody@fictief .com> wrote in message
news:4010306b$0 $777$ba620e4c@n ews.skynet.be.. .[color=blue]
> Try to search msdn for the execute command of a connection , that is maybe
> what you are searching for
>
> greets John
>
>
> "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> news:Z_VPb.6384 $rW5.504007@new s20.bellglobal. com...[color=green]
> > "John Lauwers" <nobody@fictief .com> wrote in message
> > news:400f9587$0 $316$ba620e4c@n ews.skynet.be.. .[color=darkred]
> > > Yes you can make a function that will search your recordset and return[/color][/color]
> the[color=green][color=darkred]
> > > id
> > >
> > > Private function ReturnId(firstn ame as string,lastname as string) as[/color][/color]
> long[color=green][color=darkred]
> > > dim rst as adodb.recordset
> > > set rst = new adodb.recordset
> > > with rst
> > > .open "select teacherid from tblteachers where firstname = '"[/color][/color][/color]
&[color=blue][color=green][color=darkred]
> > > firstname "' and lastname = '" & lastname & "'", db, adOpenDynamic,
> > > adLockReadOnly
> > > if not .eof and not .bof then
> > > .movefirst
> > > returnid = .fields("teache rid").value
> > > else
> > > returid = 0 'no teacherid found
> > > end if
> > > .close
> > > end with
> > > set rst = nothing[/color]
> >
> >
> > 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
> >
> >
> >[color=darkred]
> > >
> > > end function
> > >
> > > (PS function not tested)
> > >
> > > Greets John
> > >
> > > "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> > > news:1ZePb.5212 $rW5.293982@new s20.bellglobal. com...
> > > >
> > > > "John Lauwers" <nobody@fictief .com> wrote in message
> > > > news:400d43db$0 $296$ba620e4c@n ews.skynet.be.. .
> > > > > select teacherid from tblteachers where firstname = 'Jane' and[/color]
> > lastname[color=darkred]
> > > =
> > > > > 'Doe'
> > > >
> > > > Thank you.
> > > >
> > > > Can I do that without making a new RecordSet variable? Storing the[/color]
> > entire[color=darkred]
> > > > 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[/color][/color][/color]
from[color=blue][color=green][color=darkred]
> > > > database'
> > > >
> > > >
> > > > Adrian
> > > >
> > > >
> > > >
> > > > > "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in[/color][/color]
> bericht[color=green][color=darkred]
> > > > > news:1ZaPb.1051 4$cQ6.319437@ne ws20.bellglobal .com...
> > > > > > Hi. I would like to query a database, given several where[/color][/color][/color]
clauses[color=blue][color=green]
> > to[color=darkred]
> > > > > refine
> > > > > > my search, and return the value of one single field in the[/color][/color]
> database.[color=green][color=darkred]
> > > > > >
> > > > > > eg: I have a table that lists teachers. Their first name, last[/color]
> > name,[color=darkred]
> > > > > 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?
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Mike and Jo]

Re: Save query return in variable


"Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> wrote in message
news:Z_VPb.6384 $rW5.504007@new s20.bellglobal. com...[color=blue]
> "John Lauwers" <nobody@fictief .com> wrote in message
> news:400f9587$0 $316$ba620e4c@n ews.skynet.be.. .[color=green]
> > Yes you can make a function that will search your recordset and return[/color][/color]
the[color=blue][color=green]
> > id
> >
> > Private function ReturnId(firstn ame as string,lastname as string) as[/color][/color]
long[color=blue][color=green]
> > 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("teache rid").value
> > else
> > returid = 0 'no teacherid found
> > end if
> > .close
> > end with
> > set rst = nothing[/color]
>
>
> 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
>[/color]

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

[color=blue]
>
>[color=green]
> >
> > end function
> >
> > (PS function not tested)
> >
> > Greets John
> >
> > "Adrian Parker" <adrian.parker@ NOSPAMsympatico .ca> schreef in bericht
> > news:1ZePb.5212 $rW5.293982@new s20.bellglobal. com...[color=darkred]
> > >
> > > "John Lauwers" <nobody@fictief .com> wrote in message
> > > news:400d43db$0 $296$ba620e4c@n ews.skynet.be.. .
> > > > select teacherid from tblteachers where firstname = 'Jane' and[/color][/color]
> lastname[color=green]
> > =[color=darkred]
> > > > 'Doe'
> > >
> > > Thank you.
> > >
> > > Can I do that without making a new RecordSet variable? Storing the[/color][/color]
> entire[color=green][color=darkred]
> > > 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" <adrian.parker@ NOSPAMsympatico .ca> schreef in[/color][/color][/color]
bericht[color=blue][color=green][color=darkred]
> > > > news:1ZaPb.1051 4$cQ6.319437@ne ws20.bellglobal .com...
> > > > > Hi. I would like to query a database, given several where clauses[/color][/color]
> to[color=green][color=darkred]
> > > > refine
> > > > > my search, and return the value of one single field in the[/color][/color][/color]
database.[color=blue][color=green][color=darkred]
> > > > >
> > > > > eg: I have a table that lists teachers. Their first name, last[/color][/color]
> name,[color=green][color=darkred]
> > > > age,
> > > > > unique teacher number, etc is in the file.
> > > > >
> > > > > I want to return the unique teacher number, for example, of the[/color]
> > teacher[color=darkred]
> > > > > whose first name is Jane and last name is Doe.
> > > > >
> > > > > How does one do this?
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]