| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
What's the SP? From the error message, it sounds as though it's supposed to
be returning a recordset. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "csidb" <(E-Mail Removed)> wrote in message news  4F0DC5E-0368-42D0-8A91-(E-Mail Removed)...>I rewrote the code using ADO to call the Stored Procedure. The SP just adds > the first 2 params and puts the result in the 3rd param. When I run this > code I get the error code "3065: Cannot execute a select query". I don't > know where this is coming from. Any ideas??? Thank you! > -- > Option Compare Database > > Private Sub Command0_Click() > 'MsgBox "In Sub Command0_Click." > Me.Text5 = GetTotal() > > End Sub > Function GetTotal() As Long > > Dim objConn As ADODB.Connection > Dim objCmd As New ADODB.Command > > Dim prm1 As ADODB.Parameter > Dim prm2 As ADODB.Parameter > Dim prm3 As ADODB.Parameter > > > Dim px_number1 As Integer > Dim px_number2 As Integer > Dim xx_result As Integer > > MsgBox "In GetTotal." > px_number1 = 1 > px_number2 = 2 > x_result = 0 > > On Error GoTo Err_Execute > > Set objConn = CreateObject("ADODB.Connection") > objConn.ConnectionString = "DRIVER={Oracle in > XE};SERVER=CONV;UID=apps;PWD=apps;DBQ=CONV;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;" > objConn.Open > > 'Define the three parameters for the Stored procedure call > Set prm1 = New ADODB.Parameter > prm1.Type = adInteger > prm1.Direction = adParamInput > prm1.Value = px_number1 > Set prm2 = New ADODB.Parameter > prm2.Type = adInteger > prm2.Direction = adParamInput > prm2.Value = px_number2 > Set prm3 = New ADODB.Parameter > prm3.Type = adInteger > prm3.Direction = adParamInputOutput > prm3.Value = x_result > > ' Build the objCmd Command Object > With objCmd > .ActiveConnection = CurrentProject.Connection > 'Set other properties > .CommandText = "XXCUS_TEST.PRODUCT" > .CommandType = adCmdStoredProc 'Type for stored > procedure > > '.Parameters.Refresh > .Parameters.Append prm1 > .Parameters.Append prm2 > .Parameters.Append prm3 > End With > > objCmd.Execute > > GetTotal = 1 > Exit Function > > Err_Execute: > MsgBox Errors.Count > For Each MyError In DBEngine.Errors > With MyError > MsgBox .Number & " " & .Description > End With > Next MyError > GetTotal = -1 > End Function > > Private Sub Command8_Click() > > End Sub > ------------------------------------------------------------------------ > > "Douglas J. Steele" wrote: > >> Another way to execute the stored procedure would be to use ADO. >> >> Since your SP isn't returning a recordset, you'd typically use the ADO >> Command object to run it. >> >> (Sorry, I don't have sample code handy) >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no e-mails, please!) >> >> >> "csidb" <(E-Mail Removed)> wrote in message >> news:028AAF72-58DA-4D28-A2DA-(E-Mail Removed)... >> > Thanks for the tip. I get error "3146:ODBC -- call failed". >> > >> > I'm confident that my CONNECT string is correct because I can link to >> > the >> > Oracle tables. I chgd the SQL to a simple "SELECT COUNT(*) FROM >> > ABM.ABM_APPL_WINS" (which is an ORACLE table) and it went through with >> > no >> > errors. >> > >> > I have a bad feeling about the "stored procedure". I'll question the >> > Oracle developers again, but do you know of another way of executing a >> > stored >> > procedure? >> > >> > Thanks again - >> > >> > >> > "Douglas J. Steele" wrote: >> > >> >> Try >> >> >> >> LSProc.Execute, dbFailOnError >> >> >> >> and change your error routine to >> >> >> >> MsgBox Err.Number & ": " & Err.Description >> >> >> >> The error might tell you something. >> >> >> >> -- >> >> Doug Steele, Microsoft Access MVP >> >> http://I.Am/DougSteele >> >> (no e-mails, please!) >> >> >> >> >> >> "csidb" <(E-Mail Removed)> wrote in message >> >> news:A7EE993A-58D5-4570-8BF8-(E-Mail Removed)... >> >> > We have a new Oracle DB with stored procedure: >> >> > >> >> > xxcus_test.product (p_number1 in NUMBER, >> >> > p_number2 in NUMBER, >> >> > x_result out number) >> >> > >> >> > I wrote the following VB code to call it, but it fails on "Execute". >> >> > Can >> >> > anyone see what is wrong? >> >> > ----------------------------------------------------------- >> >> > Option Compare Database >> >> > >> >> > Private Sub Command0_Click() >> >> > Me.Text5 = GetTotal() >> >> > End Sub >> >> > >> >> > Function GetTotal() As Long >> >> > >> >> > Dim db As Database >> >> > Dim LSProc As QueryDef >> >> > Dim LSQL As String >> >> > Dim p_number1 As Integer >> >> > Dim p_number2 As Integer >> >> > Dim x_result As Integer >> >> > >> >> > p_number1 = 1 >> >> > p_number2 = 2 >> >> > x_result = 0 >> >> > >> >> > On Error GoTo Err_Execute >> >> > >> >> > Set db = CodeDb() >> >> > Set LSProc = db.CreateQueryDef("") >> >> > >> >> > LSProc.Connect = "ODBC;DRIVER={Oracle in >> >> > XE};SERVER=CONV;UID=apps;PWD=apps;DBQ=CONV;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;" >> >> > >> >> > LSProc.SQL = "BEGIN xxcus_test.product(" & p_number1 & "," & >> >> > p_number2 >> >> > & >> >> > "," & x_result & "); END;" >> >> > >> >> > LSProc.ReturnsRecords = False >> >> > LSProc.ODBCTimeout = 100 >> >> > >> >> > LSProc.Execute >> >> > Set LSProc = Nothing >> >> > >> >> > GetTotal = x_result >> >> > Exit Function >> >> > >> >> > Err_Execute: >> >> > MsgBox "The call to the Oracle stored procedure failed." >> >> > GetTotal = -1 >> >> > End Function >> >> > --------------------------------------------- >> >> > Thank you!! >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Call Oracle Stored Procedure from Access | Jeremy Ellison | Microsoft Access | 1 | 22nd Mar 2008 09:05 PM |
| call a STORED PROCEDURE (oracle) from C# | RicercatoreSbadato | Microsoft C# .NET | 3 | 19th Jun 2006 08:43 AM |
| Call Stored Procedure ORACLE from Access ADODB | Marc Rondeau | Microsoft Access VBA Modules | 0 | 16th Mar 2006 11:08 PM |
| Using MS Access to call an Oracle Stored Procedure and capture DBMS_OUTPUT | Adonus | Microsoft Access Macros | 1 | 14th Dec 2005 11:55 PM |
| Call Oracle Stored Procedure | Frank | Microsoft Access VBA Modules | 1 | 25th Sep 2004 10:59 PM |
