| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> REPOST: Re: Anybody use VB as a front end to Oracle?
I hate to admit this, but I don't know how to tell for sure. The app was
written for Oracle 7 by another programmer. I think I'm fair in both VB &
Oracle, but I'm pretty new at the 2 together. According to the notes, the
connection used ADO.
I double-checked the Oracle OLE DB documentation & it looks like everything's right, but the samples in the documentation use the 'command' syntax which this project doesn't. Maybe that's the problem?
Project references:
Visual Basic for Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
Microsoft Data Binding Collection
Microsoft Data Formatting Object Library
OLE Automation
Microsoft ActiveX Data Objects Recordset 2.1 Library
Microsoft ActiveX Data Objects 2.1 Library
The first 3 are pretty standard, and the next 2 as far as I know just deal with data controls that are used later in the program. I don't think they should affect the area where I'm having problem, which is the initial 'security' check. The Oracle database interfaces with a legacy application that uses different user IDs so we have a 'security' table that sets up the legacy user ID based on the Oracle user ID. If I can just get the app to select the legacy user ID from that table then I think/hope I can solve the rest of the problems on my own. Like I said, the user ID I'm using has access to that table-I copied the select statement from the immediate pane into SQL*Plus using the same user ID & it works fine.
Project code is as follows:
Public cn As ADODB.Connection 'connection to db
Public rs As ADODB.Recordset
Public strProvider As String 'Provider string
' Oracle 7 client
strProvider = "MSDAORA"
On Error GoTo NewProvider
With cn
.Provider = strProvider
.ConnectionString = "User ID =" & sUsername & _
";Password=" & sPassword & _
";Data Source=DB"
.Open
rs.Open "SELECT sec_legacy_id FROM db.security_tbl " & _
"WHERE sec_user_id = user", cn
Debug.Print rs!sec_user_legacy_id
NewProvider:
'Oracle 8i Client
strProvider = "ORAOLEDB.Oracle"
With cn
.Provider = strProvider
.Open
result is ? (that's the character ? that the debug.print statement returns, not an indication that I don't know what the result is. In SQL*Plus the same SELECT returns the legacy user ID character string that matches the Oracle user ID I'm logged in with.)
By the way, is there any better way to check which client is installed than trying the 'lesser' and seeing if it generates an error? I do need to use both clients because we're not upgrading everybody at once. (Fortunately the people who actually use this haven't been upgraded yet-we caught the problem when I tried to perform some maintenance on the app after my client had been upgraded so I could upgrade the server. Now both the server & the client upgrades are on hold until we're sure they won't break the app.)
Thanks.
Jim Kennedy wrote:
> How are you connecting? (ODBC, ADO, Oracle's OLE objects)
> Maybe your method of connection isn't compatible with the Oracle 8 client.
> It changed since Oracle 7.
> Jim
> "Calvin Crumrine" <Calvin_Crumrine_at_dced.state.ak.us> wrote in message
> news:3C2BBAC0.4F577901_at_dced.state.ak.us...
> > We're having trouble with our VB front end since we upgraded from the
> > Oracle 7 client to Oracle 8i. First it wouldn't connect any more, then
> > we got it so it appeared to connect but it won't fetch any data. The
> > data's there & accessible-I can get it in SQL*Plus using the same logon
> > ID & Select statement, but it doesn't seem to come thru in the VB app.
> > No error messages, just no data.
> >
> > Anybody think they can help? I can post details if it would be useful.
> > Thanks.
> >
autocancel Received on Mon Dec 31 2001 - 12:34:16 CST
 |
 |