asked on

ODBC connection to PostgreSQL not working with VB.Net in VS 2008

I am trying to connect to a PostgreSQL database using a vb.net program.

I can connect to the database with other programs using the ODBC connection I set up (excel) and I can connect and see the tabels, etc in the "Server Explorer" in Visual Studio.

When I try to execute my program, I get the error: ...[ODBC Driver Manager] Data source name not found and no default driver specified

I have tried drag and drop of a table into a dataset, created a binding source and setting its data source to the table which auto creates the table adapter. This gives the above error when executing.

I have also tried the following code:

        Dim conn As New Data.Odbc.OdbcConnection
        conn.ConnectionString = "dsn={PostgreSQL30};driver={PostgreSQL ANSI};"
        conn.Open()

Open in new window

I get the error at the conn.Open().

I have tried multiple versions of the connection string in many different ways. I've found and tried all of the other question/answers relating to this here in Experts and other sites, but all get the same error.

I've attached a couple of screen shots showing my ODBC connection settings, which also works when tested using the "Test" button.

I will happily try any suggestions anyone posts!

Thanks!!

ASKER CERTIFIED SOLUTION

Anil Golamari

membership

This solution is only available to members.

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

Start Free Trial

genehayes

ASKER

While the link given did solve my problem, it wasn't a solution as much as a hint towards an alternative that needed to be tracked down.

I would have spelled it out in the answer itself instead of providing a link to someone elses suggestion on another site.

Still, this did send me down the correct path and it did solve my problem so I do appreciate the answer!

genehayes

ASKER

I meant to give the full solution that Lucky85 led me to...

He suggested adding a .dll to my vb.net program called Npgsql that can be found at:
http://pgfoundry.org/frs/?group_id=1000140

Once I added the Npgsql.dll reference to my project, my code now looks like this:

Dim myConnection As New Npgsql.NpgsqlConnection
        myConnection.ConnectionString = "Server=xxx.xxx.xxx.xxx;Port=5432;Database=myDatabase;User Id=myUser;Password=myPasswd;"
        myConnection.Open()
        Dim cmd As New Npgsql.NpgsqlCommand
        cmd.CommandType = CommandType.Text
        cmd.Connection = myConnection
        cmd.CommandText = "SELECT * FROM public.ph_device"
        Dim da As New Npgsql.NpgsqlDataAdapter
        da.SelectCommand = cmd
        da.Fill(DsMercury.ph_device) 'Fills a predefined dataset
        DataGridView1.DataSource = DsMercury.ph_device 'Predefined datagrid viewer on my form
        myConnection.Close()

Open in new window

Thanks again Lucky!!