Thread: How To Connect PostgreSQL thru VB without DSN

Hi,

I want to know How To Connect  PostgreSQL thru VB throughDSN-less connection.

I used the following syntax
Private Sub ConnectDB
On Error GoTo EH
Dim c As New ADODB.Connection
c.open "DRIVER={PostgreSQL};SERVER=200.200.200.1 ;port=5432;DATABASE=MyDB;UID=postgres;PWD=postgres;"
Exit Sub
EH:
Msgbox Err.Description,vbcritical,"Error Info"
End Sub

The  IP address of the machine, where PostgreSQL is installed, is 200.200.200.1  which i noted from the Mynetwork places ---> properties ---> Local Area Connection ---> TCP/IP Internet Protocol properties.

The operating system installed is Win-2000

But , the above code gives error as [Microsoft][ODBC Driver Manager] Data Source name not found or default driver not specified.

I guess the value for the SERVER parameter in the above connection string code will be wrong. Please guide me.

--
G.Pragadheeswaran

Your connection string looks fine, assuming that it is all really on one line.

Your error sounds like your Driver is not properly installed.
Troubleshooting:
1. In the ODBC Administrator, check the Drivers tab. Is "PostgreSQL" listed. (This corresponds to the
{PostgreSQL}  in your connection string.
2. In registry check HKLM\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL -- There will be keys elaborating the
location of .dlls. Do those DLLs exist.

What version is your PostgreSQL server?
3. Check, does your server permit remote connections?
In postgresql.conf that TCPIP_SOCKET parameter in version up to 7.4 (I think) and parameter
listen_addreses in version 8+.
4. Does your PostgreSQL server permit the username and password your are trying to supply from your
location? Check pg_hba.conf settings.

Other checks (just for troubleshooting).
Does it work if you use "DSN=myDSNname" type connection string?
Can you connect with pgAdmin?

BTW. The connection string will take a FQDN (fully qualified domain name) -- if you have the means for DSN
translation. That is if you can successfully ping "mycomputer.mydomain.com" instead of 200.200.200.1,
that'll work too.


pragadheeswaran gopalakrishnan wrote:
> Hi,
>
> I want to know How To Connect  PostgreSQL thru VB throughDSN-less
> connection.
>
> I used the following syntax
> Private Sub ConnectDB
> On Error GoTo EH
> Dim c As New ADODB.Connection
> c.open "DRIVER={PostgreSQL};SERVER=200.200.200.1
> ;port=5432;DATABASE=MyDB;UID=postgres;PWD=postgres;"
> Exit Sub
> EH:
> Msgbox Err.Description,vbcritical,"Error Info"
> End Sub
>
> The  IP address of the machine, where PostgreSQL is installed, is
> 200.200.200.1  which i noted from the Mynetwork places ---> properties --->
> Local Area Connection ---> TCP/IP Internet Protocol properties.
>
> The operating system installed is Win-2000
>
> But , the above code gives error as [Microsoft][ODBC Driver Manager] Data
> Source name not found or default driver not specified.
>
> I guess the value for the SERVER parameter in the above connection string
> code will be wrong. Please guide me.
>
> --
> G.Pragadheeswaran

1. One thing at a time. Eliminate the section where you try to put data into the database. Does the
connection work. Do you get to the msgbox "Connection Message"?
2. Your pg_hba.conf host sections -- I hope you realize those are the addresses of your clients, so your
VB work station would have to have an I.P. of 200.200.200.something.
3. A normal PG problem would throw an error, which would show up as a message box, but VB would not crash
and close -- but your application will if it doesn't catch and handle errors. VB crashing indicates a more
gross error -- bad  VB installation, missing References, code that's just wrong, etc.
4. The rsFriends seems to be missing a declaration and instantiation.
eg.
Dim rsFriends as ADODB.Recordset
Set rsFriends = new ADODB.Recordset
(Dim x as new Object -- is considered a somewhat sloppier form,
because it does not match the Set x= Nothing)
But as my first words indicate, don't worry about INSERTS or SELECTS until you are sure Connect will work.
5. Once you get the connect with the DSN, work on the DSN-less connect with the "Driver=..." string.



pragadheeswaran gopalakrishnan wrote:
>>4. Does your PostgreSQL server permit the username and password your are
>>trying to supply from your
>>location? Check pg_hba.conf settings.
>>
>>
>>
>
> Hi! Campbell, Thanks for replying,
>
> I will furnish some details for your kind notice.
>
> I am using VB to connect PostgreSQL database. It gets connected thru DSN
> based code.
>
> The following is a segment of my pg_hba.conf file
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> .
> .
> .
>
>
> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
>
> # IPv4 local connections:
> host    all         all         127.0.0.1/32          md5
> host    all         all         200.200.200.1         255.255.255.0
> md5
> # IPv6 local connections:
> host     all     all     ::1/128     md5
> #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> When i run the code thru VB which is based on DSN leads to the following
> error
> "VB6.exe has generated errors and will be closed by Windows. You will need
> to restart
> the program."
>
> and VB itself gets closed after this error message
>
> my OS is WIN-2K
> my postgreSQL version is 8.1
>
>
> The code, which i used to connect pgsql is the following
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Dim cCon As New ADODB.Connection, rsFriends As New ADODB.Recordset
> Private Sub Form_Load()
>     ConnectDB
> End Sub
> Private Sub ConnectDB()
> On Error GoTo EH
>
>
>     cCon.ConnectionString = "DSN=pgsdsn;" & _
>                              "UID=steve;" & _
>                              "PWD=king;" & _
>                              "Database=mydb"
>
>     cCon.Open
>     MsgBox "O.K.", vbInformation, "Connection Message"
>     With rsFriends
>         .Open "SELECT * FROM Friends", cCon, adOpenKeyset,
> adLockPessimistic    'Here only the error is generated
>         .AddNew
>             .Fields![FriNam] = InputBox("Enter the Friend Name")
>             .Fields![SrlNum] = Val(InputBox("Enter the Serial Number"))
>             .Fields![DOB] = InputBox("Enter the Date of Birth")
>             .Fields![PhNo] = InputBox("Enter the Phone No:")
>         .Update
>     End With
>     If rsFriends.State = adStateOpen Then rsFriends.Close
>     Set rsFriends = Nothing
>     MsgBox "O.K.", vbInformation, "Recordset Message"
>     Exit Sub
>     Exit Sub
> EH:
>     MsgBox Err.Description, vbCritical, "Error Message"
> End Sub
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
>
>
> The following, is the Error details written in LOG file under the LOG folder
> of POSTGRESQL
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 2006-03-20 12:15:31 LOG:  unexpected EOF on client connection
> 2006-03-20 12:15:37 LOG:  could not receive data from client: No connection
> could be made because the target machine actively refused it.
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
> Many Thanks in advance.

Hi,

I want to know How To Connect  PostgreSQL thru VB throughDSN-less connection.

I used the following syntax
Private Sub ConnectDB
On Error GoTo EH
Dim c As New ADODB.Connection
c.open "DRIVER={PostgreSQL};SERVER=200.200.200.1 ;port=5432;DATABASE=MyDB;UID=postgres;PWD=postgres;"
Exit Sub
EH:
Msgbox Err.Description,vbcritical,"Error Info"
End Sub

The  IP address of the machine, where PostgreSQL is installed, is 200.200.200.1  which i noted from the Mynetwork places ---> properties ---> Local Area Connection ---> TCP/IP Internet Protocol properties.

The operating system installed is Win-2000

But , the above code gives error as [Microsoft][ODBC Driver Manager] Data Source name not found or default driver not specified.

I guess the value for the SERVER parameter in the above connection string code will be wrong. Please guide me.

--
G.Pragadheeswaran