How do I return the SQL Server error message to VFP

[mdav2]

I am using VFP9 and SQL Server 2005. When I issue a statement that causes an error (using the SQLEXEC command) all I get back is -1 to indicate a failure. What would be good is if I could get the SQL Server Error message back. Is there any way to do this?

Thanks,

Mark Davies
Warwickshire County Council

 

[cbasoz]

When the return value is < 0 you can use aerror() for the details. ie:

if SQLExec(m.handle, m.lcSQL) < 0
   SQLErrHand()
endif
   
Procedure SQLErrHand()
  local lcError
  lcError = ''
  Aerror(arrCheck)
  For ix=1 To 7
    lcError = m.lcError+Trans( arrCheck [ix])+ Chr(13)
  Endfor
  Messagebox(m.lcError,0,'Error def.')
endproc

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Mark,

While you are testing your application, set the connection's DispWarning property to .T. You can do that either by using DBSETPROP(), or going into the Connection Designer and ticking the Display Warnings box.

With this setting in force, any errors returned from the back end will be treated the same as normal VFP errors. In other words, they will trigger the Cancel / Suspend / Ignore dialogue, or invoke your error handler.

This makes it much easier to deal with routine program errors during development. But you need to reverse the setting for live running. Once you've done that, use AERROR() (along the lines suggested by Cetin) to trap the error and deal with it in some way.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[EzLogic]

I personally use the wwSQL class from West-Wind.. it's so awesome as a wrapper class.

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[mdav2]

Thanks for that I'll look into them.

Mark Davies
Warwickshire County Council

 

[cbasoz]

SQLExec() and dbsetprop()?

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

Probably you meant SQLSetProp().

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Probably you meant SQLSetProp().

You use SQLSETPROP() to change the properties for an active connection, that is, after you've obtained the connection handle.

You use DBSETPROP() to permanently change the properties for the connection object within the database. The end result is the same.

Hope that helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

I know that but couldn't make a correlation between SQLExec() and dbSetProp(). As far as I know setting it DB would be no effect on your SQLExec().

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Cetin,

As far as I know setting it DB would be no effect on your SQLExec().

It would, if you used the connection object (from the DBC) to obtain the connection handle for the SQLEXEC().

So, if MyConnection is the connection object in the DBC, and you used DBSETPROP() to set its DispWarning to .T., and then executed the following code:

lnHandle = SQLCONNECT("MyConnection")
SQLEXEC(lnHandle, "SELECT * FROM SomeTable")

then the warning message would kick in if there was an error.

I agree that you could achieve a similar goal using SQLSETPROP(). The reason I suggested modifying the connection object within the DBC was to avoid code changes between development and live running.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business