connection lost. what r the possible reasons?

every now and then my application (foxpro,db2) errors with the message:

Connection handle is invalid.

can anyone point me in the right direction to find the reason why this is happening

thanks in advance

Visual FoxPro1

I'm guessing that either the connection was not established to begin with or it was lost somehow since it was established. What do you do to establish the connection

Do you use SQLCONNECT( ) or SQLSTRINGCONNECT( ) to establish connection If so, do you check whether the connection is established by verifying that the Handle >0. Or do you lose the connection that was once established and worked properly

Something like:

PUBLIC lnConnHandle

LOCAL lcServerName, lcSQLUserID, lcSQLPassword, lcSQLUserID, lcSQLPassword, lcSQLDBName

*--Verify the values of all parameters passed by setting them yourself

lcSQLUSerID = 'MySQLId'

lcUserId = ''

lcConsole = ''

lcSQLPassword = "MyPassword"

lcservername = "Myserver"

lcSQLdbName = "MyDbName"

lcProgName = 'MyProgramName'

lcConnString = "DRIVER={SQL Server};" ;

+ "Server=" + lcServerName + ";" ;

+ IIF(EMPTY(lcSQLUserID) AND EMPTY(lcSQLPassword),"","UID=" +lcSQLUserID+ ";" ;

+ "PWD=" + lcSQLPassword + ";") ;

+ "DATABASE=" + lcSQLDBName + ;

+ IIF(TYPE('lcProgName') = "C",";" ;

+ "APP=" + lcProgName + IIF(TYPE('lcConsole')="C", " " + lcConSole,"") + ;

IIF(TYPE('lcUserId')="C", " " + TRIM(lcUserId),""),"")

lnConnHandle = SQLSTRINGCONNECT(lcConnString)

"Connection handle:"

lnconnhandle

IF lnConnHandle > 0 then

MESSAGEBOX("Connection Successful")

=SQLDISCONNECT(lnConnHandle)

ELSE

=MESSAGEBOX("Connection Failed")

RETURN

ENDIF

The connection is set successfully but somewhere down the line it is lost and i cant understand why. Would a short disruption to the internet connection between client and server cause that

I get this error randomly and i cant reproduce it on demand. And it only happens on the UNLOAD event of screens (try to close screen). How could i catch this error or where should i start investigating it

>>And it only happens on the UNLOAD event of screens (try to close screen). How could i catch this error or where should i start investigating it

Sounds like you may have the connection handle going out of scope - is it stored in a public variable, or a form property ora property of some other object (If it is NOT in a form property or public variable then it will not be available in the Unload (all contained objects are destroyed by the time it fires).

As for trapping it, the only real way to test a connection handle is to run a query that cannot fail and check the result of SQLEXEC():

lnRes = SQLEXEC( nCon, 'SELECT GetDate() AS junk' )
IF lnRes < 1
*** No connection
ELSE
*** Connection is still there
ENDIF

the connection handle at the time of the error is 1...this means its valid...i dont understand why it gives me that error.....

>> the connection handle at the time of the error is 1...this means its valid...i dont understand why it gives me that error.....

All that this tells you is that the last time you established a connection, the handle WAS "1"

If the connection drops, VFP doesn't know that and the handle will still be "1" even if there is no connection. Use the code I showed you to check that the handle actually IS valid.

have u used asqlhandles function before can this function check the connection handle whether is sucess or fail