Solved

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

Posted on 2011-02-24
3
2,683 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 Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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