SQL View (SIMPLE!!!) from VBSCRIPT 
Author Message
 SQL View (SIMPLE!!!) from VBSCRIPT

    Presently I can query a SQL database by passing a SQL string to the
RecordSet.Execute object.method.

    I would like have a recordset returned from a SQL database view?  How do
I do this, I cannot find a code s{*filter*}t please provide.

Thanks!

------------------------
Const sDB_Server = "COW"
Const sDB = "EnterpriseManagement"
Const sDB_UserID = "userid"
Const sDB_UserID_Password = "password"
------------------------
 Dim dConnection , dRecordSet, sSQL, sConnect
 'dConnection ADO.Connection Object, dRecordSet ADO.RecordSet Object, sSQL
SQL Strings, sConnect Connection String

 Set dConnection = CreateObject("ADODB.Connection")
 Set dRecordSet = CreateObject("ADODB.Recordset")

 sConnect = "Provider=SQLOLEDB;Data Source="& sDB_Server & ";" & "Initial
Catalog=" & sDB & ";User ID=" & sDB_UserID & ";Password=" &
sDB_UserID_Password & ";"

 dConnection.ConnectionString = sConnect
 dConnection.Open

 Set dRecordSet = dConnection.Execute(sSQLView)

 If dRecordSet.EOF <> TRUE Then
  MsgBox dRecordSet.RecordCount
 End If
 dRecordSet.close
 dConnection.Close



Sun, 17 Jul 2005 05:14:08 GMT  
 SQL View (SIMPLE!!!) from VBSCRIPT
Did you cross-post to enough groups?

Selecting from a view is just like selecting from a table.  You have a lot
more code than you need.

' set up sConnect and constants here
set conn = server.createobject("ADODB.Connection")
conn.open sConnect
set rs = conn.execute("SELECT columns FROM name_of_table_or_view")

--
Aaron Bertrand, SQL Server MVP
http://www.*-*-*.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:
>     Presently I can query a SQL database by passing a SQL string to the
> RecordSet.Execute object.method.

>     I would like have a recordset returned from a SQL database view?  How
do
> I do this, I cannot find a code s{*filter*}t please provide.

> Thanks!

> ------------------------
> Const sDB_Server = "COW"
> Const sDB = "EnterpriseManagement"
> Const sDB_UserID = "userid"
> Const sDB_UserID_Password = "password"
> ------------------------
>  Dim dConnection , dRecordSet, sSQL, sConnect
>  'dConnection ADO.Connection Object, dRecordSet ADO.RecordSet Object, sSQL
> SQL Strings, sConnect Connection String

>  Set dConnection = CreateObject("ADODB.Connection")
>  Set dRecordSet = CreateObject("ADODB.Recordset")

>  sConnect = "Provider=SQLOLEDB;Data Source="& sDB_Server & ";" & "Initial
> Catalog=" & sDB & ";User ID=" & sDB_UserID & ";Password=" &
> sDB_UserID_Password & ";"

>  dConnection.ConnectionString = sConnect
>  dConnection.Open

>  Set dRecordSet = dConnection.Execute(sSQLView)

>  If dRecordSet.EOF <> TRUE Then
>   MsgBox dRecordSet.RecordCount
>  End If
>  dRecordSet.close
>  dConnection.Close



Sun, 17 Jul 2005 05:18:36 GMT  
 SQL View (SIMPLE!!!) from VBSCRIPT
How about setting the string as follows:

    sSQLView = "Select * from MyView"?


Quote:
>     Presently I can query a SQL database by passing a SQL string to the
> RecordSet.Execute object.method.

>     I would like have a recordset returned from a SQL database view?  How
do
> I do this, I cannot find a code s{*filter*}t please provide.

> Thanks!

> ------------------------
> Const sDB_Server = "COW"
> Const sDB = "EnterpriseManagement"
> Const sDB_UserID = "userid"
> Const sDB_UserID_Password = "password"
> ------------------------
>  Dim dConnection , dRecordSet, sSQL, sConnect
>  'dConnection ADO.Connection Object, dRecordSet ADO.RecordSet Object, sSQL
> SQL Strings, sConnect Connection String

>  Set dConnection = CreateObject("ADODB.Connection")
>  Set dRecordSet = CreateObject("ADODB.Recordset")

>  sConnect = "Provider=SQLOLEDB;Data Source="& sDB_Server & ";" & "Initial
> Catalog=" & sDB & ";User ID=" & sDB_UserID & ";Password=" &
> sDB_UserID_Password & ";"

>  dConnection.ConnectionString = sConnect
>  dConnection.Open

>  Set dRecordSet = dConnection.Execute(sSQLView)

>  If dRecordSet.EOF <> TRUE Then
>   MsgBox dRecordSet.RecordCount
>  End If
>  dRecordSet.close
>  dConnection.Close



Sun, 17 Jul 2005 05:21:52 GMT  
 SQL View (SIMPLE!!!) from VBSCRIPT
Joel,

It's the same thing you can execute the sSQLView from the connection object

The only issue here is that you need to set permission to read data from the
view to the users...
Actually, you could also work this with a Store Procedure



Quote:
> How about setting the string as follows:

>     sSQLView = "Select * from MyView"?



> >     Presently I can query a SQL database by passing a SQL string to the
> > RecordSet.Execute object.method.

> >     I would like have a recordset returned from a SQL database view?
How
> do
> > I do this, I cannot find a code s{*filter*}t please provide.

> > Thanks!

> > ------------------------
> > Const sDB_Server = "COW"
> > Const sDB = "EnterpriseManagement"
> > Const sDB_UserID = "userid"
> > Const sDB_UserID_Password = "password"
> > ------------------------
> >  Dim dConnection , dRecordSet, sSQL, sConnect
> >  'dConnection ADO.Connection Object, dRecordSet ADO.RecordSet Object,
sSQL
> > SQL Strings, sConnect Connection String

> >  Set dConnection = CreateObject("ADODB.Connection")
> >  Set dRecordSet = CreateObject("ADODB.Recordset")

> >  sConnect = "Provider=SQLOLEDB;Data Source="& sDB_Server & ";" &
"Initial
> > Catalog=" & sDB & ";User ID=" & sDB_UserID & ";Password=" &
> > sDB_UserID_Password & ";"

> >  dConnection.ConnectionString = sConnect
> >  dConnection.Open

> >  Set dRecordSet = dConnection.Execute(sSQLView)

> >  If dRecordSet.EOF <> TRUE Then
> >   MsgBox dRecordSet.RecordCount
> >  End If
> >  dRecordSet.close
> >  dConnection.Close



Sun, 17 Jul 2005 23:04:35 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. SQL View (SIMPLE!!!) from VBSCRIPT

2. SQL View (SIMPLE!!!) from VBSCRIPT

3. SQL statements in object inspector

4. MDI apps

5. a little thing...but very important for me !!!

6. Using Vbscript and SQL-DMO to connect to SQL Server and run a T-SQL script

7. Simple Viewing Question

8. Postscript for perspective views of simple 3-D objects

9. Extracting SQL Statements from Views - SQL Server

10. Accessing a View on SQL Server from an ASP

11. View data in an SQL database?

12. Retrieve image from SQL 7 view in Web page

 

 
Powered by phpBB® Forum Software