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 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
"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?
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?
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?
"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?
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?
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? > >
"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?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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)
{...
|
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...
|
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;
|
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 ...
|
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:
...
|
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:...
|
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...
|
by: kiranbabu |
last post by:
<html>
<head>
<style type="text/css">
h2{color:#A02820}
</style>
</head>
<script language=javascript>
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
| |