|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 21st, 2003, 03:43 PM
|
|||
|
|||
|
using dynamic sql to get recordset from oracle
I need an asp code sample on how to execute a dynamic sql to get a recordset using Oracle as the data source. I am able to connect to the Oracle db using the connection string "Provider=MSDAORA;Data Source=<server name>;User ID=<user id>;Password=<password>;Database=<db name>;". I just need to use the correct syntax to build my SQL statement dynamically to get a recordset.
|
|
#2
November 21st, 2003, 03:53 PM
|
||||
|
||||
|
These sites might help some. Haven't specifically used Oracle myself, but I do connection strings all the time...
Oracle connection strings General connection string help If you need to make it dynamic, that is easy enough, whatever the correct syntax is... Code:
oConn.Open "Provider=" & yourProviderVariable & ";" & _
"Data Source=" & yourDataSourceVariable & ";" & _
"User Id=" & yourUserIDVariable & ";" & _
"Password=" & yourPasswordVariable & ";"
Hope this helps some. Sorry I don't know Oracle specifically...
|
|
#3
November 21st, 2003, 07:26 PM
|
|||
|
|||
|
Sorry for the confusion, but what I actually need is how to create a dynamic SQL that will be executed by a command object after a connection to an Oracle database has been made.
***************************************** Const CONNECT = "'Provider = MSDAORA; User ID=myuser; Password=mypwd;Data Source=myserver;DATABASE=mydb;" Dim cn, rs, cmd, SQL set cn = Server.CreateObject( "ADODB.Connection" ) set cmd = server.CreateObject ("ADODB.Command") set rs = server.CreateObject ( "ADODB.Recordset" ) cn.Open CONNECT SQL = "SELECT * FROM MyTable" with cmd set .ActiveConnection = cn .CommandText = SQL .CommandType = 1 'adCmdText end with set rs = cmd.execute **************************************** What is the correct syntax for the variable SQL? I tried running this piece of code and it gave me an error "Operation is not allowed when the object is closed." I was able to successfully connect to the Oracle db, but not in executing the dynamic SQL in the command object.
|
|
#4
November 21st, 2003, 11:01 PM
|
|||
|
|||
|
http://www.w3schools.com/ado/ado_ref_command.asp has information on the ADO Command object
also, since you've already got a recordset open you can just do set rs = cn.execute(SQL) to have the results returned
|
|
#5
November 22nd, 2003, 09:08 AM
|
||||
|
||||
|
I have never used the "Command" variable type. I just do something like the following:
Code:
Dim cn, rs, queryString
cn = Server.CreateObject("ADODB.Connection")
rs = Server.CreateObject("ADODB.Recordset")
cn.Open "Provider = MSDAORA; User ID=myuser; " & _
"Password=mypwd;Data Source=myserver;DATABASE=mydb"
queryString = "SELECT * FROM MyTable WHERE " &
"param1 = " & mVar & " AND " & _
"param2 = " & myOtherVar
rs.Open queryString, cn
Hope this helps somewhat. Chris
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > using dynamic sql to get recordset from oracle |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
