How can I connect to Oracle from VB using ODBC and ADO control ?

[Mano]

How can I connect to Oracle from VB using ODBC and ADO control of version 6 ? I am getting the following error when I tried doing,<br>
<br>
"Unable to bind to field or datamemeber:"Field""<br>
<br>
Can you please suggest solution for this issue <br>
<br>
<br>
Thanks<br>
Manohar

 

[databarn]

Manohar,<br>
<br>
What version of Oracle? And, do you have Oracle ODBC drivers installed for that version. Oracle uses a two-step configuration, one step of which is an ODBC driver and the other step of which invokes Oracle's 'TNSNames' thingy. The ODBC driver calls the Oracle driver to get to the Oracle instance. <br>
<br>
I've been told that there is a 'direct' driver for this, but I've not seen it, and I'm doing a lot of ODBC work with Oracle tables, flabours 7.x and 8.x.<br>
<br>
Make a good day . . .<br>
. . . barn<br>

 

[Mano]

Hi Barn,<br>
<br>
Thank you for your reply.<br>
<br>
I am using Oracle 8. I tried connecting using <br>
<br>
1 ODBC driver provided by Oracle 8<br>
2 ODBC driver provided by Microsoft for Oracle<br>
<br>
But both have failed. Can you please mail me the step by step procedure you follow to connect to Oracle database.<br>
<br>
I have followed the following procedure.<br>
1. drag and drop the ADO control over the form.<br>
2. Drag and drop a text box over the form<br>
3. provided connection string to ADO control where in it asks to provide DSN name when click on ODBC. If not already there it asks to create a new one. Then go to the authentication tab and provided user id and password for oracle database. then finally provide the table name from which we need access.<br>
4. click on the text box and provided and data access source as ado control name which we have created and provided the data field ( a column from the table chosen for ADO control)<br>
5. finally run the program. It ends up giving the error<br>
"Unable to bind to field or datamemeber:"filed name""<br>
<br>
I think I am not giving complete details to the program. But I don't know where am I going wrong.<br>
<br>
Thanks<br>
Manohar

 

[databarn]

Manohar,<br>
<br>
&lt;prescript&gt;<br>
I came back and added this after writing all the stuff below - well, I pasted a lot of it, but same thing &lt;grin!&gt;. I really don't mind if you need to contact me during working hours, assuming that you and I have any in common. I know just how frustrating this can be - it took me six months at work to get a couple of people to explain to me _how_ to set this up instead of having them come by when I was gone and setting it up for me! Getting a good answer on the ODBC/Oracle interface simply is not an easy task - Oracle DBAs say its a MS (ODBC) problem, and ODBC techs say its an Oracle problem - darn few will admit that it's an interface problem that involves several disciplines - particularly if they don't know one of those disciplines very well &lt;grin!&gt;.<br>
&lt;/prescript&gt;<br>
<br>
Don't have the whole bit here right now, but you need to use the Oracle installer and create a TNSNames piece for the ODBC connection. To this end, you must have installed SQL*Net when you installed the Oracle ODBC drivers. Then you can use the SQL Net Easy Configuration utility.<br>
<br>
If you really understand the Oracle stuff and its CLI, you may be able to 'munge' the TNSNames file.<br>
<br>
On this machine, the file is <br>
C:\Aps\OraWin95\Network\Admin\TNSNAMES.ORA<br>
<br>
This is the beginning of the TNSNames file<br>
__________________________________________________<br>
---<br>
#This is a SQL*Net Configuration file generated by SQL*Net Easy Configuration.<br>
#Attention: Do not modify this file yourself.<br>
#If you do, your SQL*Net Easy Configuration may not function properly.<br>
<br>
Example1.world = <br>
(DESCRIPTION = <br>
(ADDRESS_LIST = <br>
(ADDRESS = <br>
(COMMUNITY = tcp.world)<br>
(PROTOCOL = TCP)<br>
(Host = Production1)<br>
(Port = 1521)<br>
)<br>
)<br>
(CONNECT_DATA = (SID = SID1)<br>
)<br>
)<br>
_____________________________________________<br>
<br>
This next is a real connection to an Oracle instance that I use at work. Pardon me that the server information is obfuscated - corporate rules, donchano - but everything else is verbatim.<br>
<br>
_______________________________________<br>
FMBC.world = <br>
(DESCRIPTION = <br>
(ADDRESS_LIST = <br>
(ADDRESS = <br>
(COMMUNITY = tcp.world)<br>
(PROTOCOL = TCP)<br>
(Host = servername.companyname.com)<br>
(Port = 1521)<br>
)<br>
(ADDRESS = <br>
(COMMUNITY = tcp.world)<br>
(PROTOCOL = TCP)<br>
(Host = servername.companyname.com)<br>
(Port = 1526)<br>
)<br>
)<br>
(CONNECT_DATA = (SID = fmba)<br>
)<br>
)<br>
___________________________________________<br>
<br>
The port numbers are consistent throughout the file, so I assume they are standard Oracle configuration. They are not version dependent, at least not since version 7.0, 'cause I've used the same connection from 7.0, 7.1, 7.3, to 8.0. Don't remember a 7.2, bet there may have been one. Biggest problem I've had to date has been when the Oracle Listener was the wrong version or was down. (As near as I can tell, the 'listener' is a server-side component that waits for Oracle SQL statements.)<br>
<br>
The ODBC side of this is simplistic in the extreme (is that possible? or just redundant? &lt;grin!&gt. The only critical item is that the SQL*Net Connect String in the ODBC configuration _must_ be the same as the TNSNames name (the FMBC.world above - FMBC is the SQL*Net Connect String).<br>
<br>
If this doesn't seem clear - which is likely - ask again and I'll try to make more sense. If you're really in a bind, you can get my e-mail from my profile. Mail me at work if need be. My working hours are 0600-1700 CDT (US), which is -6 GMT (or Zulu).<br>
<br>
Make a good day . . .<br>
. . . barn<br>
<br>

 

[rpbenito]

hey i don't know if this will help...but i had a similar problem. What you need to do is go to oracles site and download the oracle odbc drivers....Then you go to oracle installer and install the new odbc drivers from oracle....you may need to get an update on your oracle installer...if the installer gives an error of unable to parse file, this is the case....next go into the windows control panel and go to ODBC data sources and configure your connection for the system..not the user. After completing and testing the connection go into your VB code and try the following for your connect string...from what I'm told each oracle server has it's own &quot;quirks&quot; so try this and if this fails go to the oracle site again and look at the specs for the odbc connections there...let me know if this helps...here is the connection string for the connect function and other functions that involve connect strings

&quot;ODBC Database&quot;,&quot;ODBC;DSN=*****;DBQ=****;UID=****;PWD=*****;DATABASE=*****&quot;

The dbq is the same as the dsn

 

[BMooreDE]

Okay I am still lost. I have a subroutine that writes data to the database, but it does not seem to be working. I'll paste the code here and maybe someone can tell me what I am doing wrong.
Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset

Set myConnection = New ADODB.Connection
Set myRecordset = New ADODB.Recordset

myConnection.ConnectionString = &quot;DSN=ODBCdrivername; UID=userid; PWD=userpwd&quot;

'-Open the connection --
myConnection.Open
MsgBox &quot;In ODBC call&quot;

'Determine if we conected.
If myConnection.State = adStateOpen Then
myRecordset.Open &quot;insert into copylog values (&quot;&quot;&quot; & Release & &quot;&quot;&quot;,&quot;&quot;&quot; & Issue _
& &quot;&quot;&quot;,&quot;&quot;&quot; & FileNm & &quot;&quot;&quot;,&quot;&quot;&quot; & Trgt & &quot;&quot;&quot&quot;, _
myConnection, adOpenDynamic, adLockOptimistic, adCmdTable
Else
MsgBox &quot;The connection could not be made.&quot;
myConnection.Close
Exit Sub
End If

'-just to be sure --
myRecordset.MoveFirst

On Error GoTo transError

'-here is the top of the transaction sandwich --
myConnection.BeginTrans

While Not myRecordset.EOF
mcounter = mcounter + 1
myRecordset!Title = myRecordset!Title & &quot;&quot; 'so we don't really change it
myRecordset.Update
myRecordset.MoveNext
Wend

'-if we got here ok, then everything is written at once
myConnection.CommitTrans
myRecordset.Close
myConnection.Close

Exit Sub

transError:
myConnection.RollBack
myRecordset.Close
myConnection.Close
MsgBox Err.Description
End Sub