Home » Infrastructure » Windows » Multiple-step OLE DB operation generated errors. (VBA, Windows 7)
Multiple-step OLE DB operation generated errors. [message #651425] Tue, 17 May 2016 11:01 Go to next message
oteixeira
Messages: 33
Registered: May 2007
Member
Hello to all.
The following code is producing the error:
"-2147217887:Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.No work was done."
I've been googling for this but found no solution.
Can someone please give me an idea how to solve this problem?
Many thanks in advance.
Octavio

Private Sub CommandButton1_Click()
On Error GoTo erro
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ccmd As New ADODB.Command
Dim tourist1 As New ADODB.Parameter 'par 1
Dim data_ini As New ADODB.Parameter 'par 2
Dim data_fin As New ADODB.Parameter 'par 3
Dim iloc As New ADODB.Parameter 'par4
Dim tourist2 As New ADODB.Parameter ' par 5
Dim tourist3 As New ADODB.Parameter 'par 6
If Me.TextBox1.Text = "" Or Me.TextBox2.Text = "" Then
    Exit Sub
End If
If Me.ComboBox1.Text = "" Then
    MsgBox ("Tem de usar uma invoicing location")
    Exit Sub
End If
Dim dbConnectStr As String
    dbConnectStr = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.172.99.39)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRODL)));User ID=APPS;Password=APPS;"
    conn.ConnectionString = dbConnectStr
    conn.Open
    'MsgBox (conn.State)

ccmd.ActiveConnection = conn
ccmd.CommandText = "SELECT ilo.name, c.name,decode('?', 'Y', t.NAME, 'ALL'),f.FLIGHT_NUMBER , to_char(f.flight_date_aux, 'DD-Mon-YYYY'), r.reference," _
& " sum(( Case When nvl(t.age,50) > 14 Then '1' Else '0' End ) * nvl(t.quantity,1))" _
& " ,sum(( Case When nvl(t.age,50) between 2 and 14 Then '1'   Else '0'  End" _
& ") * nvl(t.quantity,1))  ,sum(( Case When nvl(t.age,50) < 2 Then '1'" _
& "              Else '0' End ) * nvl(t.quantity,1))" _
& "  ,sum(nvl(t.quantity,1))" _
& " from   inc_tourists t, inc_reservations r, inc_flights f, inc_customers_v c, inc_invoicing_locations ilo" _
& "  where t.reservation_id = r.reservation_id And r.arrival_flight_id = f.flight_id" _
& "  and    r.customer_id = c.customer_id and r.invoicing_location_id = ilo.invoicing_location_id" _
& "  and    f.flight_date_aux between to_date(?, 'DD-MM-YYYY') and to_date(?, 'DD-MM-YYYY')" _
& "  and    ILO.NAME = 'Cabo Verde - Sal' group by ilo.name, c.name, f.flight_date_aux" _
& " ,      f.FLIGHT_NUMBER,  r.reference, decode('?', 'Y', t.NAME, 'ALL'), to_char(f.flight_date_aux, 'DD-Mon-YYYY')" _
& " order by ilo.name, c.name, f.flight_date_aux, f.FLIGHT_NUMBER, r.reference, decode('?', 'Y', t.NAME, 'ALL')"
Set tourist1 = ccmd.CreateParameter("one", adVariant, adParamInput, 1, TextBox3.Text)
Set data_ini = ccmd.CreateParameter("two", adVariant, adParamInput, 1, TextBox1.Text)
Set data_fin = ccmd.CreateParameter("three", adVariant, adParamInput, 1, TextBox2.Text)
Set iloc = ccmd.CreateParameter("four", adVariant, adParamInput, 1, ComboBox1.Text)
Set tourist2 = ccmd.CreateParameter("five", adVariant, adParamInput, 1, TextBox3.Text)
Set tourist3 = ccmd.CreateParameter("six", adVariant, adParamInput, 1, TextBox3.Text)
ccmd.Parameters.Append tourist1
ccmd.Parameters.Append data_ini
ccmd.Parameters.Append data_fin
ccmd.Parameters.Append iloc
ccmd.Parameters.Append tourist2
ccmd.Parameters.Append tourist3
rst.CursorLocation = adUseClient
Set rst = ccmd.Execute  [b]'error occurs here[/b]
If Err.Number <> 0 Then
erro:     aux = MsgBox(CStr(Err.Number) & ":" & Err.Description, vbOKOnly, "ERRO - " & Err.Source)  '
End If
conn.Close
End Sub
Re: Multiple-step OLE DB operation generated errors. [message #651428 is a reply to message #651425] Tue, 17 May 2016 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not a solution but take care, you posted your previous question in Windows forum when it was a SQL one and you now post this one in SQL forum when it is clearly and OLE DB one (and so Windows).

Please carefully read the different forum purposes at http://www.orafaq.com/forum/ and carefully think what your problem is about.
Thanks.

[Updated on: Tue, 17 May 2016 12:17]

Report message to a moderator

Re: Multiple-step OLE DB operation generated errors. [message #651429 is a reply to message #651428] Tue, 17 May 2016 13:13 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
I don't know VB at all.
It appears the code establishes 6 parameters, but only contains 5 "?"
I have no idea what happens when there is a mis-match?
Re: Multiple-step OLE DB operation generated errors. [message #651447 is a reply to message #651429] Wed, 18 May 2016 03:16 Go to previous messageGo to next message
oteixeira
Messages: 33
Registered: May 2007
Member
Thank you once again. I corrected the problem but the error still remains.
Re: Multiple-step OLE DB operation generated errors. [message #651448 is a reply to message #651428] Wed, 18 May 2016 03:21 Go to previous messageGo to next message
oteixeira
Messages: 33
Registered: May 2007
Member
Thank you and sorry for the inconvenience.
Now that i have corrected the problem but still receiving the error do you think I can re-post my problem in another forum? If yes, which one?
Thank you.
Octavio
Re: Multiple-step OLE DB operation generated errors. [message #651449 is a reply to message #651448] Wed, 18 May 2016 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe in a Microsoft OLE DB forum as I'm not sure your problem is related to Oracle but maybe to OLE DB configuration.
Are you able to execute another SQL statement or have you the same error for all of them?

Re: Multiple-step OLE DB operation generated errors. [message #651450 is a reply to message #651449] Wed, 18 May 2016 03:35 Go to previous messageGo to next message
oteixeira
Messages: 33
Registered: May 2007
Member
Thank you for answering. The SQL statement executes perfectly on Sqlplus...and I'm able to execute another statements without errors.
Re: Multiple-step OLE DB operation generated errors. [message #651451 is a reply to message #651450] Wed, 18 May 2016 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know.
What I'd do in your place is to put in this place a simple SQL command I know it works and see if it works there.
Then increase add bit per bit the wanted SQL until it fails then you will know which part of the SQL causes the failure.
If it does not work with the first simple SQL then the problem does not come from it but from something or somewhere else in the code.

Re: Multiple-step OLE DB operation generated errors. [message #651452 is a reply to message #651451] Wed, 18 May 2016 03:52 Go to previous message
oteixeira
Messages: 33
Registered: May 2007
Member
Thank you for you advice. I will proceed accordingly.
Previous Topic: Getting "Fail to retrieve domain list" in Oracle Administrator Assistant For Windows
Next Topic: Installing example Schemas...Help
Goto Forum:
  


Current Time: Fri Dec 15 00:41:35 CST 2017

Total time taken to generate the page: 0.01689 seconds