Dim adoConnectionX As New ADODB.Connection adoConnectionX.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=Scott;Password=tiger;Data Source=OraSevice" 'adoConnectionX.Open "Provider=MSDAORA.1;Persist Security Info=True;User ID=Scott;Password=tiger;Data Source=OraSevice" adoConnectionX.Execute SQL
Option Explicit Dim Cn As rdoConnection Dim En As rdoEnvironment Dim CPw As rdoQuery Dim Rs As rdoResultset Dim Conn As String Dim QSQL As String Dim Response As String Dim Prompt As String Private Sub cmdCheck_Click() QSQL = "Select Item_Number, Depot_Number From rdooracle Where " _ & "item_number =" & txtInput.Text Set Rs = Cn.OpenResultset(QSQL, rdOpenStatic, , rdExecDirect) Prompt = "Item_Number = " & Rs(0) & ". Depot_Number = " _ & Rs(1) & "." Response = MsgBox(Prompt, , "Query Results") Rs.Close End Sub Private Sub cmdSend_Click() CPw(0) = Val(txtInput.Text) CPw.Execute Prompt = "Return value from stored procedure is " & CPw(1) & "." Response = MsgBox(Prompt, , "Stored Procedure Result") End Sub Private Sub Form_Load() Conn = "UID=;PWD=;driver={Microsoft ODBC Driver for Oracle};" _ & "CONNECTSTRING=MyOracle;" Set En = rdoEnvironments(0) Set Cn = En.OpenConnection("", rdDriverPrompt, False, Conn) QSQL = "{call rdoinsert(?,?)}" Set CPw = Cn.CreateQuery("", QSQL) End Sub Private Sub Form_Unload(Cancel As Integer) En.Close End Sub
rdo: Dim cn As New rdoConnection Dim rs As rdoResultset Dim q As rdoQuery Dim strConnect As String strConnect = "dsn=orac;UID=pss;PWD=pss" cn.Connect = strConnect cn.CursorDriver = rdUseClientBatch cn.EstablishConnection 'uncomment below line after mixed table created ' cn.Execute "DROP TABLE mixed" cn.Execute "CREATE TABLE mixed(""Col1"" varchar(20))" Set q = cn.CreateQuery("bob", "SELECT * FROM mixed") Set rs = q.OpenResultset(rdOpenKeyset, rdConcurLock) 'rs.rdoColumns(0).SourceColumn = """Col1""" rs.AddNew rs(0) = "alfka" rs.Update rs.BatchUpdate rs.Requery Debug.Print rs(0).Value ado2.5: Create a new Visual Basic Standard EXE project. Form1 is created by default. Set a Reference to Microsoft ActiveX Objects 2.5. Paste the following code into the Form1 code window: Private Sub Form_Load() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset On Error Resume Nextcn.CursorLocation = adUseClient'ODBC 'cn.Open cn.Open "Provider=MSDASQL;Driver={Microsoft ODBC for Oracle};Server=YourOracleAlias;Uid=demo;pwd=demo"'OLEDB cn.Open "Provider=MSDAORA;Data Source=YourOracleAlias;User ID=demo;Password=demo"cn.Execute "Drop table oratest" cn.Execute "CREATE TABLE ORATEST(Col1 VARCHAR2(10) NULL, Col2 VARCHAR2(10) NULL)" cn.Execute "CREATE TABLE DUAL(Col1 VARCHAR2(10) NULL)" rs.Open "select col1, col2 from oratest where col1 in (Select 1 from dual union select 2 from dual)", _ cn, adOpenStatic, adLockBatchOptimisticMsgBox rs.Fields.CountEnd Sub Make the necessary connection string changes, and then run the project.Results: The message box displays a "1." Stop the project, and uncomment the connect line following "'ODBC," and comment the line following "'OLEDB."
adoConnectionX.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=Scott;Password=tiger;Data Source=OraSevice"
'adoConnectionX.Open "Provider=MSDAORA.1;Persist Security Info=True;User ID=Scott;Password=tiger;Data Source=OraSevice"
adoConnectionX.Execute SQL
Option Explicit
Dim Cn As rdoConnection
Dim En As rdoEnvironment
Dim CPw As rdoQuery
Dim Rs As rdoResultset
Dim Conn As String
Dim QSQL As String
Dim Response As String
Dim Prompt As String Private Sub cmdCheck_Click() QSQL = "Select Item_Number, Depot_Number From rdooracle Where " _
& "item_number =" & txtInput.Text
Set Rs = Cn.OpenResultset(QSQL, rdOpenStatic, , rdExecDirect) Prompt = "Item_Number = " & Rs(0) & ". Depot_Number = " _
& Rs(1) & "." Response = MsgBox(Prompt, , "Query Results") Rs.Close End Sub Private Sub cmdSend_Click() CPw(0) = Val(txtInput.Text)
CPw.Execute Prompt = "Return value from stored procedure is " & CPw(1) & "."
Response = MsgBox(Prompt, , "Stored Procedure Result") End Sub Private Sub Form_Load() Conn = "UID=;PWD=;driver={Microsoft ODBC Driver for Oracle};" _
& "CONNECTSTRING=MyOracle;" Set En = rdoEnvironments(0)
Set Cn = En.OpenConnection("", rdDriverPrompt, False, Conn)
QSQL = "{call rdoinsert(?,?)}"
Set CPw = Cn.CreateQuery("", QSQL) End Sub Private Sub Form_Unload(Cancel As Integer) En.Close End Sub
Dim cn As New rdoConnection
Dim rs As rdoResultset
Dim q As rdoQuery
Dim strConnect As String strConnect = "dsn=orac;UID=pss;PWD=pss"
cn.Connect = strConnect
cn.CursorDriver = rdUseClientBatch
cn.EstablishConnection
'uncomment below line after mixed table created
' cn.Execute "DROP TABLE mixed"
cn.Execute "CREATE TABLE mixed(""Col1"" varchar(20))"
Set q = cn.CreateQuery("bob", "SELECT * FROM mixed")
Set rs = q.OpenResultset(rdOpenKeyset, rdConcurLock)
'rs.rdoColumns(0).SourceColumn = """Col1"""
rs.AddNew
rs(0) = "alfka"
rs.Update
rs.BatchUpdate
rs.Requery
Debug.Print rs(0).Value
ado2.5:
Create a new Visual Basic Standard EXE project. Form1 is created by default.
Set a Reference to Microsoft ActiveX Objects 2.5.
Paste the following code into the Form1 code window:
Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
On Error Resume Nextcn.CursorLocation = adUseClient'ODBC
'cn.Open cn.Open "Provider=MSDASQL;Driver={Microsoft ODBC for Oracle};Server=YourOracleAlias;Uid=demo;pwd=demo"'OLEDB
cn.Open "Provider=MSDAORA;Data Source=YourOracleAlias;User ID=demo;Password=demo"cn.Execute "Drop table oratest"
cn.Execute "CREATE TABLE ORATEST(Col1 VARCHAR2(10) NULL, Col2 VARCHAR2(10) NULL)"
cn.Execute "CREATE TABLE DUAL(Col1 VARCHAR2(10) NULL)"
rs.Open "select col1, col2 from oratest where col1 in (Select 1 from dual union select 2 from dual)", _
cn, adOpenStatic, adLockBatchOptimisticMsgBox rs.Fields.CountEnd Sub
Make the necessary connection string changes, and then run the project.Results: The message box displays a "1."
Stop the project, and uncomment the connect line following "'ODBC," and comment the line following "'OLEDB."
可能我没有把问题讲清楚,现在详细说一下。
1.我在数据库中创建了一个join view,目的是处理主从表
2。现在我想对join view进行insert delete update操作,可是遇到了很多问题,关于key-perserved table, no-key-perserved table。我不明白是怎么回事,现在无法对view处理。
3.有没有谁有这方面的例子,用VB处理主从表的例子,