Trying to connect via ODBC - "The network path was not found"

Question

I'm trying to update a database (Oracle via ODBC) in ASP:

<%@ Page LANGUAGE="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<%


Dim objConn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ODBCNAME").ConnectionString)

'example query
Dim strSQL As String = "update foo set bar='BAZ'" 
objConn.Open()
Dim objCmd As New SqlCommand(strSQL, objConn)

Try
    objCmd.ExecuteNonQuery()
    Response.Write("Record updated")
Catch e As Exception
    Response.Write(e.ToString)
End Try

%>

Where web.config has:

<connectionStrings>
   <add name="ODBCNAME" connectionString="server=ExampleServerName;" providerName="System.Data.Odbc" />
</connectionStrings>

When I execute this I get the error on the objCOnn.Open() line:

System.ComponentModel.Win32Exception: The network path was not found.

The ODBC data source is created, testing and working with other stand alone applications (closed source), and I can test the connection via the ODBC Settings, which works.

How can I connect to this database from my code? Or how can I debug this problem?

Answer 1

Oracle can be a tricky beast.

First, use OdbcConnection instead of SqlConnection if you are using ODBC.

See docs from Microsoft

Sample code adapted from above MSDN link:

Private Sub InsertRow(ByVal connectionString As String)

    Dim queryString As String = _
        "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')"
    Dim command As New OdbcCommand(queryString)

    Using connection As New OdbcConnection(connectionString)
        command.Connection = connection
        connection.Open()
        command.ExecuteNonQuery()

        ' The connection is automatically closed at 
        ' the end of the Using block.
    End Using
End Sub

Dim connectionString as String = <your connection details here>
InsertRow(connectionString)

Next, I recommend you use the Oracle Data Provider for .NET (ODP.NET, AKA Oracle.DataAccess.dll) which is provided with the Oracle Client (though not always installed by default; you may need to do custom install), performs better than ODBC, and is able to utilize Oracle-specific functionality. That being said, if you want to use ODBC (and there are of course some good reasons to keep it generic like this), read on:

In addition to other suggestions, make sure that the architectures match. This only applies to you if you are on a 64-bit box.

If 64-bit OS and 64-bit app you must use 64-bit ODBC to define the DSN C:\Windows\System32\odcad32.exe

If 64-bit OS and 32-bit app you must use 32-bit ODBC to define the DSN C:\Windows\SysWOW64\odbcad32.exe

(Yeah, the names are REALLY confusing! SysWOW64 basically means emulating 32-bit in a 64-bit enviro. The other problem is Microsoft named the thing odbcad32 in the first place.. perhaps odbcad would be better, but they probably had to distinguish between the 32-bit and 16-bit version at the time.. just a guess)

If 32-bit OS then the location of odbcad32.exe is the default location (same as 64-bit on 64-bit) and is in your PATH by default.

If your app will run in 32-bit and 64-bit then you must define two DSNs, one for 32 and one for 64.

Alternately you can set the architecture of your project in the project settings. By default it may be Any CPU which (I think) means to prefer the native.. so if building on 64-bit OS you get 64-bit exe unless you change it to x86 or something. In Visual Studio 2015 you can also use the prefer 32-bit checkbox.

And the same goes for the Oracle client: you must use matching architecture.