odbc connection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

segue - 25 Jan 2008 18:59 GMT

I've been trying to connect to a postgressql database using oledb.

1. added a system dsn in the odbc datasource administrator
2. connected to the database using visual studio
3. used the connection string created by visual studio in the database
properties
4. maybe an odbc syntax would work?

Provider=SQLOLEDB;database=internet;server=mycompany.com;port=5432;uid=myuser;sslmode=disable;readonly=0;protocol=7.4;"

code fails at:
oleConnection = New OleDbConnection(connectionstr)
oleConnection.Open()

I get the error:
[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does not exist or access denied.

Suggestions appreciated - thanks.

Reply to this Message

Norman Yuan - 25 Jan 2008 20:17 GMT

I think your connectionstring is wrong. "Provider=SQLOLEDB;" is for SQL
Server, not for PostGresSQL. Depending what Data Provider you have installed
to the computer, the ConnectionString would be different, for examle, it
could be like this, if you are to use OleDb namespce:

Provider=PostgreSQL OLE DB Provider; Data Source=xxxxx;location=xxxxx...

this link would be helpful

http://www.connectionstrings.com/?carrier=postgressql

HTH

> I've been trying to connect to a postgressql database using oledb.
>
[quoted text clipped - 15 lines]
>
> Suggestions appreciated - thanks.

Reply to this Message

segue - 25 Jan 2008 23:21 GMT

Thanks. Problem solved following:
dim strpg as string = "Dsn=PostgreSQL;Provider=SQLOLEDB;"
Dim connection As OdbcConnection = New OdbcConnection(strpg)
connection.Open()
Dim fullCommandText As String = "select * from table1"
Dim select_command As New OdbcCommand(fullCommandText, connection)
etc.

> I think your connectionstring is wrong. "Provider=SQLOLEDB;" is for SQL
> Server, not for PostGresSQL. Depending what Data Provider you have installed
[quoted text clipped - 28 lines]
> >
> > Suggestions appreciated - thanks.

Reply to this Message

Mark Rae [MVP] - 26 Jan 2008 00:27 GMT

>> > I've been trying to connect to a postgressql database using oledb.
>> >
[quoted text clipped - 15 lines]
>>
>> http://www.connectionstrings.com/?carrier=postgressql

> Thanks. Problem solved following:
> dim strpg as string = "Dsn=PostgreSQL;Provider=SQLOLEDB;"
[quoted text clipped - 4 lines]
> connection)
> etc.

Yes, but you've resorted to using ODBC when there's a perfectly serviceable
OleDb provider available - that is (almost always) the wrong solution...

Because you've specified a DSN, the Provider element is simply being
ignored.

As you've been advised, Provider=SQLOLEDB is for SQL Server, not Postgre.
For Postgre, the Provider is PostgreSQL OLE DB Provider:

In fact, there's a native .NET data provider for Postgre which I would
strongly advise you to use instead:
http://www.connectionstrings.com/?carrier=postgresql

Signature

Mark Rae
ASP.NET MVP
http://www.markrae.net

Reply to this Message

segue - 07 Mar 2008 22:30 GMT

OK thank you for the heads up this solved another problem as well.

> >> > I've been trying to connect to a postgressql database using oledb.
> >> >
[quoted text clipped - 37 lines]
> strongly advise you to use instead:
> http://www.connectionstrings.com/?carrier=postgresql

Reply to this Message

Norman Yuan - 26 Jan 2008 15:04 GMT

I agree with Mark.

> Thanks. Problem solved following:
> dim strpg as string = "Dsn=PostgreSQL;Provider=SQLOLEDB;"
[quoted text clipped - 39 lines]
>> >
>> > Suggestions appreciated - thanks.

Reply to this Message

odbc connection

Free Magazines