Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-02-24
3
Medium Priority
?
2,924 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 1500 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
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.
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…
Suggested Courses

578 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