odbc connection

[segue]

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.

 

[Norman Yuan]

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

 

[segue]

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
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.

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.

 

[Mark Rae [MVP]]

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.

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

 

[Norman Yuan]

I agree with Mark.

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
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.

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.

 

[segue]

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