odbc connection

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;por t=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.

Re: odbc connection

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" wrote in message
news:DD108826-6C86-4AD2-8D91-598AA2DFF1FE@microsoft.com...
> 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;por t=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.
>
>

Re: odbc connection

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.

"Norman Yuan" wrote:

> 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" wrote in message
> news:DD108826-6C86-4AD2-8D91-598AA2DFF1FE@microsoft.com...
> > 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;por t=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.
> >
> >
>
>

Re: odbc connection

"segue" wrote in message
news:884E9757-B1AF-4E11-AA82-E37C2FBE1A70@microsoft.com...

>> "segue" wrote in message
>> news:DD108826-6C86-4AD2-8D91-598AA2DFF1FE@microsoft.com...
>> > 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?
>>
>> 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

> 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


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

Re: odbc connection

I agree with Mark.

"segue" wrote in message
news:884E9757-B1AF-4E11-AA82-E37C2FBE1A70@microsoft.com...
>
>
> 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.
>
> "Norman Yuan" wrote:
>
>> 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" wrote in message
>> news:DD108826-6C86-4AD2-8D91-598AA2DFF1FE@microsoft.com...
>> > 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;por t=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.
>> >
>> >
>>
>>