|
 |
asp_databases thread: getting a recordset from a view
Message #1 by Robert Link <robert@n...> on Tue, 12 Dec 2000 15:21:56 -0800
|
|
I'm all confused about the syntax to get a recordset from a view in
sql server 7 instead of from a table. I'm sure I've read that it's
doable, I just can't figure out just the right way to do it. Here's
a snip I'd use to get a recordset from a table:
conn = "Driver={SQL Server}; Server=Server; Database=DB; UID=Login;
PWD=Pass"
set rs_foo = server.createobject("adodb.recordset")
sql_foo = "select * from tblBar"
rs.open sql_foo, conn, 1, 3
What would I change to pull from a view instead of from a table?
- --
Robert Link
---
FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
IN YOUR INBOX!
Get the latest and best HTML, XML, and JavaScript tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb's
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #2 by Imar Spaanjaars <Imar@S...> on Wed, 13 Dec 2000 10:07:40 +0100
|
|
AFAIK, you don't need to change anything to the SQL, except change the name
from the table to the name of the view.
sql_foo = "select * from vwBar"
rs.open sql_foo, conn, 1, 3
I tried this on my system and it works.
Try naming your cursor and locktype instead of using the number.
IMO adOpenKeyset, adLockOptimistic is easier to read than 1,3.
HtH
Imar
At 07:22 AM 12/13/2000 -0800, you wrote:
>I'm all confused about the syntax to get a recordset from a view in
>sql server 7 instead of from a table. I'm sure I've read that it's
>doable, I just can't figure out just the right way to do it. Here's
>a snip I'd use to get a recordset from a table:
>
>conn = "Driver={SQL Server}; Server=Server; Database=DB; UID=Login;
>PWD=Pass"
>set rs_foo = server.createobject("adodb.recordset")
>sql_foo = "select * from tblBar"
>rs.open sql_foo, conn, 1, 3
>
>What would I change to pull from a view instead of from a table?
>- --
>Robert Link
>
>
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #3 by Robert Link <robert@n...> on Wed, 13 Dec 2000 08:47:48 -0800
|
|
At 10:07 AM 12/13/2000 +0100, Imar wrote:
>IMO adOpenKeyset, adLockOptimistic is easier to read than 1,3
IMO2!!! The part I didn't get was that sqlserver won't let me have a
view and a table with the same name. I have since been clued in to
that little detail.
- --
Robert Link
---
FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
IN YOUR INBOX!
Get the latest and best HTML, XML, and JavaScript tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb's
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #4 by Imar Spaanjaars <Imar@S...> on Wed, 13 Dec 2000 20:01:41 +0100
|
|
AFAIK, you can't have a View with the same name as a table. The Books
online states somewhere that the name for a table (and for a View) has to
be unique for the whole database, not just for the table collection in the
database, if I recall correctly.
When you think about it, it makes sense: you reference a table and a view
in the following way:
databaseName.Owner.ObjectName.
How would the database be able to decide whether to take the table or the
view if both are present within the same database.
I could be wrong, but I don't think there is something like a
type-identifier in SQL, so the following wouldn't be possible (although it
would be handy if it could be done ;-))
databaseName.Owner.Tables.ObjectName to get a table and
databaseName.Owner.Views.ObjectName to get a view.
That is why (and you already did that) you could prefix your tables with
tbl and views with vw for example.
Imar
At 08:47 AM 12/13/2000 -0800, you wrote:
>At 10:07 AM 12/13/2000 +0100, Imar wrote:
> >IMO adOpenKeyset, adLockOptimistic is easier to read than 1,3
>IMO2!!! The part I didn't get was that sqlserver won't let me have a
>view and a table with the same name. I have since been clued in to
>that little detail.
>- --
>Robert Link
>
>
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #5 by "Harsh Nandu" <harsh_stgil@h...> on Thu, 21 Dec 2000 06:34:42 -0000
|
|
Hi Friend,
You are on the right track.
Just use the view name instead of table name in ya select statement.
That's all.
Regds,
Harsh & Prasad
>From: Robert Link <robert@n...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] getting a recordset from a view
>Date: Wed, 13 Dec 2000 07:22:27 -0800
>
>I'm all confused about the syntax to get a recordset from a view in
>sql server 7 instead of from a table. I'm sure I've read that it's
>doable, I just can't figure out just the right way to do it. Here's
>a snip I'd use to get a recordset from a table:
>
>conn = "Driver={SQL Server}; Server=Server; Database=DB; UID=Login;
>PWD=Pass"
>set rs_foo = server.createobject("adodb.recordset")
>sql_foo = "select * from tblBar"
>rs.open sql_foo, conn, 1, 3
>
>What would I change to pull from a view instead of from a table?
>- --
>Robert Link
>
>
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
|
|
|
View Cart
