Calling parameterised views from VB

I'd really appreciate your help creating a parameterised view in SQL
Server, which I can then invoke from VB5. I'm using DAO and ODBC to
connect to SQL Server 2000.

I can open Tables and Views, but can't find how to create or use a
"query" parameter. If I use the "?" (in the SQL Enterprise manager
View design mode), the syntax checker verifies the query ok, but I
then get the message "Parameters cannot be used in this query type".
It doesn't like the %var% terminology at all here.

I am sure I could build the full query in VB and save it to the Server
"on-the-fly", but this partly defeats the object, doesn't it?

I could really do with a couple of pointers: (a) how to define the
view/query in Ent Manager and (b) how to invoke it from VB, supplying
the parameter.

Thanks ever so in anticipation.

Phil

re: Calling parameterised views from VB

Phil Sowden (philsowden@dataservicesltd.co.uk) writes:[color=blue]
> I'd really appreciate your help creating a parameterised view in SQL
> Server, which I can then invoke from VB5. I'm using DAO and ODBC to
> connect to SQL Server 2000.
>
> I can open Tables and Views, but can't find how to create or use a
> "query" parameter. If I use the "?" (in the SQL Enterprise manager
> View design mode), the syntax checker verifies the query ok, but I
> then get the message "Parameters cannot be used in this query type".
> It doesn't like the %var% terminology at all here.
>
> I am sure I could build the full query in VB and save it to the Server
> "on-the-fly", but this partly defeats the object, doesn't it?
>
> I could really do with a couple of pointers: (a) how to define the
> view/query in Ent Manager and (b) how to invoke it from VB, supplying
> the parameter.[/color]

Views cannot be parameterized in SQL Server. However there are table-valued
functions, which are about the same thing. Example:

CREATE FUNCTION myorders (@custid nchar(5)) RETURNS TABLE AS
RETURN (SELECT * FROM Northwind..Orders WHERE CustomerID = @custid)
go
SELECT * FROM myorders(N'ALFKI')


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Calling parameterised views from VB

Thanks very much indeed for your help, Erland.

I had tried this, amongst many other things. However, I didn't realise
I'd got it right, as I couldn't work out how to populate a VB recordset
object from an SQL function.

I normally (in Access) open a database object, then use something like:

lsSQL = "Select * from Northwind.Orders WHERE CustomerID = " &
llCustID
Set lrsOrders = gdbMain.OpenRecordset(lsSQL)
lrsOrders.MoveFirst

where:
gdbMain is a globally-defined database object
lrsOrders is a locally-defined recordset object

This second part of my question probably belongs in the VB or Access
newsgroups, I'm sure, but maybe you can help again, Erland?

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Calling parameterised views from VB

Phil (philsowden@nospam.dataservicesltd.co.uk) writes:[color=blue]
> I had tried this, amongst many other things. However, I didn't realise
> I'd got it right, as I couldn't work out how to populate a VB recordset
> object from an SQL function.
>
> I normally (in Access) open a database object, then use something like:
>
> lsSQL = "Select * from Northwind.Orders WHERE CustomerID = " &
> llCustID
> Set lrsOrders = gdbMain.OpenRecordset(lsSQL)
> lrsOrders.MoveFirst
>
> where:
> gdbMain is a globally-defined database object
> lrsOrders is a locally-defined recordset object[/color]

So to call the table function you would say:

lsSQL = "Select * from myorders(" & llCustID & ")"




--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Calling parameterised views from VB

That's brilliant, thanks, Erland. I've applied that example to my
system and got it to work fine within SQL Server now.

However, when I try:
lsSQL = "Select * from FindCust('07940437220')"
Set lrsTemp = gdbSparc.OpenRecordset(lsSQL)

...in VB, I get "Error 3131 - Syntax Error in FROM clause". It's as if
VB doesn't recognise the function exists at all?

The identical code works fine (as a View) within SQL Server (n.b. the
customer reference I'm using is a 14 character nvarchar field).

I'm sorry to be such a novice! I also realise that this may not be the
correct forum - but it may be that you are able to help.

Thanks once again,

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Calling parameterised views from VB

Phil (philsowden@nospam.dataservicesltd.co.uk) writes:[color=blue]
> That's brilliant, thanks, Erland. I've applied that example to my
> system and got it to work fine within SQL Server now.
>
> However, when I try:
> lsSQL = "Select * from FindCust('07940437220')"
> Set lrsTemp = gdbSparc.OpenRecordset(lsSQL)
>
> ..in VB, I get "Error 3131 - Syntax Error in FROM clause". It's as if
> VB doesn't recognise the function exists at all?[/color]

That right. In fact VB does not know anything about SQL at all. I don't
know what this OpenRecordset is, but that much I can tell that the
error message you get does not come from SQL Server. And since VB
does not SQL, it must be someother data source you are querying. Didn't
you mention Access? Well, I don't know anything about Access, so...



--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Calling parameterised views from VB

Cool. Thanks again for your help, Erland, I'm sure I'll resolve it
shortly.

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Calling parameterised views from VB

In article <403a0e0e$0$198$75868355@news.frii.net>, Phil <philsowden@nospam.dataservicesltd.co.uk> wrote:[color=blue]
>That's brilliant, thanks, Erland. I've applied that example to my
>system and got it to work fine within SQL Server now.
>
>However, when I try:
>lsSQL = "Select * from FindCust('07940437220')"
>Set lrsTemp = gdbSparc.OpenRecordset(lsSQL)
>
>...in VB, I get "Error 3131 - Syntax Error in FROM clause". It's as if
>VB doesn't recognise the function exists at all?
>
>The identical code works fine (as a View) within SQL Server (n.b. the
>customer reference I'm using is a 14 character nvarchar field).
>
>I'm sorry to be such a novice! I also realise that this may not be the
>correct forum - but it may be that you are able to help.
>
>Thanks once again,
>
>Phil
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it![/color]


You have a table named FindCust('07940437220') in your database? Who came
up with an impossible to remember name like that?

re: Calling parameterised views from VB

Rico,

Thanks for your interest.

If you're able to tell me how to design a parameterised SQL View and
call it from VB, I'd be ecstatic! I'm sure I can fix that function name
quite easily!

The SQL table is called "Cust", but I had to create a table-type
function to return a view / query of selected records with a parameter.
The "FindCust()" function returns a recordset of customers found for the
parameter value given. I thought the name was quite meaningful,
actually!

The call you're looking at is my failed attempt to call that same
function from within VB (version 5, using DAO with ODBC connection to
SQL Server).

Ideas most welcome....!

Phil


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Calling parameterised views from VB

Phil (philsowden@nospam.dataservicesltd.co.uk) writes:[color=blue]
> The call you're looking at is my failed attempt to call that same
> function from within VB (version 5, using DAO with ODBC connection to
> SQL Server).[/color]

Aha, so that OpenRowset is DAO? Now, I don't know DAO, and it might be
that DAO is itself trying interpreting the SQL statement. In such case
you are probably out of luck, because DAO is too old to know about
table-value functions added to SQL2000. I would guess that DAO is not
equipped to handle that. But if it's ODBC which performs the syntax
check, then it's only a matter of getting version 2.6 or later of the
MDAC. They are available at:
http://www.microsoft.com/downloads/d...DisplayLang=en


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp