Solved

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

Posted on 2011-02-24
3
2,727 Views
Last Modified: 2012-05-11
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.
Screen shot of ODBC System DSN
 Screen shot of ODBC Driver
I will happily try any suggestions anyone posts!

Thanks!!
0
Comment
Question by:genehayes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Anil Golamari earned 500 total points
ID: 34973050
0
 

Author Closing Comment

by:genehayes
ID: 34973276
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!
0
 

Author Comment

by:genehayes
ID: 34973873
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!!
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question