asked on

Connection to oracle database not working in vb6 app?

Experts this problem is puzzling me. I have taken over an old vb app that uses the connection string below:
conn = "Provider=MSDAORA.1;Password=apps;User ID=apps;Data Source=BOBCAT;Persist Security Info=True" I had it working then all of a sudden it stopped working. I tried switching connection strings to this:conn = "driver={Oracle in OraHome90};server=xxxxxx;uid=xxxx;pwd=xxxx;DBQ=xxxxxxxx;MTS=T, and the application opens up but when I try to query the database I keep getting an unsupported parameter error. I would like to use the first connection string because that's what the programmer before me used and it worked fine. When I took over the application and put it on my system, I cannot get it to run consistently, any ideas?

Private Sub Main_Form_Load()
With Main
.Calendar1.Visible = False
.Combo1.ListIndex = 0
.Option1.Value = True
.Option5.Value = True
.Option6.Value = True
.Text1.Text = ""
.Text2.Text = ""
conn = "Provider=MSDAORA;Password=xxxs;User ID=xxxx;Data Source=xxxxxxxxx;Persist Security Info=True"
'conn = "driver={Oracle in OraHome90};server=xxxxxx;uid=xxxx;pwd=xxxx;DBQ=xxxxxxxx;MTS=T;"

Oracle_Ado_Logon
.Label5.Caption = "BOBCAT"
Order_Open_Dealer_Header
End With
End Sub

Public Sub Oracle_Ado_Logon()
On Error GoTo Errors
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = conn
.Properties("Prompt") = adPromptNever
.CursorLocation = adUseClient
.Open
Exit Sub
End With
Errors:
MsgBox "No Connection To The Database", vbCritical, "Database Connection Error"
End
End Sub

ASKER CERTIFIED SOLUTION

leonstryker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tentavarious

ASKER

Ok, how do I change this procedure to work with your connection string?

Public Sub Oracle_Ado_Logon()
On Error GoTo Errors
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = conn
.Properties("Prompt") = adPromptNever
.CursorLocation = adUseClient
.Open
Exit Sub
End With
Errors:
MsgBox "No Connection To The Database", vbCritical, "Database Connection Error"
End
End Sub

leonstryker

This code would not need to be changed. Only the connection string itself:

conn = "Provider=MSDAORA;Password=xxxs;User ID=xxxx;Data Source=xxxxxxxxx;Persist Security Info=True"
'conn = "driver={Oracle in OraHome90};server=xxxxxx;uid=xxxx;pwd=xxxx;DBQ=xxxxxxxx;MTS=T;"

To:
conn = "Provider=OraOLEDB.Oracle;" & _
"Data Source=xxxxxxxxx;" & _
"User Id=xxxx;" & _
"Password=xxxs"

Leon

tentavarious

ASKER

Ok, I used your connection string and I get an error with a lot of weird characters, looks lilke assembly language. The connection opens fine but when I run one query I get the error.

leonstryker

Whats the error message?

Leon

tentavarious

ASKER

Runtime error '-21474467259' Then underneath that a whole bunch of weird characters.
07h05Y, basically computer characters like when you try to open up an exe file in notepad.

leonstryker

It does not give you any description of the error?

Try doing Debug.Print Err.Description

Also, what is the query you are trying to run?

Leon

tentavarious

ASKER

Trying to run the same queries that I have in my other question.

tentavarious

ASKER

If possible, I would like to keep same connection string, because its the connection string that is currently running on our production server and it works fine there. I would just like to know more about what is going on and why it only works periodically on my system.

leonstryker

>I had it working then all of a sudden it stopped working.

Something changed. The question is what? Was ther eany software installed?

tentavarious

ASKER

Nope, not since I had it working, I closed down visual basic then reopened and the connection didnt work. After about 10 minutes of trying it started working again.