Solved

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

Posted on 2011-02-24
3
2,516 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
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Anil Golamari earned 500 total points
Comment Utility
0
 

Author Closing Comment

by:genehayes
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now