function result as criteria in a query

[Paul]

i've successfully used a function result as the criteria for a query in
access mdb database (as in KB210388) but i cant use the function in a similar
query in an ADP database. I get the error: "ADO error 'functionName()' is not
a recognized function name". Can anyone show me how to sort this out!

 

[Sylvain Lafontaine]

Some details about your function, your query and how you are using this
stuff on ADP and SQL-Server could help.

Also, if you are using an UDF function on Sql-Server, it is often required
to add the prefixe dbo. before its name.

 

[Vadim Rapp]

Hello,
You wrote on Wed, 22 Jun 2005 08:58:07 -0700:

P> i've successfully used a function result as the criteria for a query in
P> access mdb database (as in KB210388) but i cant use the function in a
P> similar query in an ADP database. I get the error: "ADO error
P> 'functionName()' is not a recognized function name". Can anyone show me
P> how to sort this out!

In mdb, the query with function is executed in Access, which has table,
query, and your function all in one bottle.

In adp, the query is sent for execution to sql server, which is another
program on another computer, therefore, it has no access to your adp and the
function.

The way around:

Let's say, your initial query, before you even applied KB10388, was:

select name from table1 where id=V

where V is your variable. Now if you want to send this to sql server, you
have to substitute the value of the variable _before_ you send it for
execution.

"select name from table1 where id = " & V

The limitation of this method is that you can't specify an expression
directly in form's datasource or in the rowsource of combobox. You have to
use a code in the program:

combo1.rowsource = "select name from table1 where id = " & V



Regards,
Vadim Rapp

 

[Paul]

Here's more info:
What i'm trying to do is base an SQL (stored procedure / view) query
on the Public variable "userId" so that various forms
can display records relevant to the 'logged-in' user.

This method works in an MDB query like a charm!
What i'd like to do is use a similar method
the ADP version of the database using sql server 2000 (desktop engine)

I have created a public variable called "userId" it stores the user id number
which was entered in a text box on a 'log-in' form.

The function i've created uses the following syntax (as recomended in
KB210388)

Public Function fnUserId()
fnUserId = userId
End Function

The syntax of the stored procedure is as follows:

SELECT dbo.tblUsers.*
FROM dbo.tblUsers
WHERE (Id = fnUserId())

Error message returned "ADO error 'fnUserId' is not a recognized function
name"

How do I get the stored procedure / view to base the value in the WHERE
clause that is being held
held in the public variable "userId"?

Tried adding "dbo." prefix but no joy

 

[Paul]

I seem to recall trying that but i still get the error relating 'function not
recognised'
Heres the details:

What i'm trying to do is base an SQL (stored procedure / view) query
on the Public variable "userId" so that various forms
can display records relevant to the 'logged-in' user.

This method works in an MDB query like a charm!
What i'd like to do is use a similar method
the ADP version of the database using sql server 2000 (desktop engine)

I have created a public variable called "userId" it stores the user id number
which was entered in a text box on a 'log-in' form.

The function i've created uses the following syntax (as recomended in
KB210388)

Public Function fnUserId()
fnUserId = userId
End Function

The syntax of the stored procedure is as follows:

SELECT dbo.tblUsers.*
FROM dbo.tblUsers
WHERE (Id = fnUserId())

Error message returned "ADO error 'fnUserId' is not a recognized function
name"

How do I get the stored procedure / view to base the value in the WHERE
clause that is being held
held in the public variable "userId"?

 

[Vadim Rapp]

P> The syntax of the stored procedure is as follows:

P> SELECT dbo.tblUsers.*
P> FROM dbo.tblUsers
P> WHERE (Id = fnUserId())

P> Error message returned "ADO error 'fnUserId' is not a recognized
P> function name"

P> How do I get the stored procedure / view to base the value in the WHERE
P> clause that is being held
P> held in the public variable "userId"?

As I said, sql server and its stored procedure does not have access to your
function, so it can't use it.

Therefore, you have to tell the stored procedure about your variable when
you call the stored procedure. Create a parameter in stored procedure, and
pass your variable as parameter when calling:

in s.p.

==============
create procedure MyProc(@Userid varchar(50)) as
....
select * from tblusers where ld=@Userid
==============

Though, unless you have much more logic in your s.p. , you don't need using
s.p at all - use sql statement directly in your adp, dynamically
constructing it when needed as I wrote in the previous post. If the variable
is string type, you will also have to include single-quotes in the sql
query: "... where ld='" & userid & "'"


Vadim

 

[aaron.kempf]

you should STORE all your variables in SQL Server and then refer to
them in subQueries.

good luck

 

[Paul]

Thanks.. tried your method and hey presto!!

Thanks again

Paul

 

[Paul]

How do i store my variables in sql server?

Paul