Converting DBFCDX to SQL (ADO)
Posted: Mon May 13, 2013 7:12 pm
Hello Everyone,
I finally made the move from DBFCDX to SQL via ADO and XBROWSE and I am having a few issues but I am sure they are easy to correct. I believe they are simple fundamental data handling differences.
1. I open application by connecting to SQL and listing data for a specific table, this part is ok, however I need to be able to allow user to enter criteria then refresh the already opened XBROWSE to new data
2. I want to connect to SQL get a column value then increase that column value by one, I am using table and column as a counter to handle a receipt number
3. I want to connect to SQL and insert, update or delete data (I got this part) then I need to refresh XBROWSE with new data
If someone could provide a basic layout of the process of connecting to SQL, listing data in XBROWSE, performing a SQL statement, then refreshing the XBROWSE I would truly be appreciative!!
Here are sample code I tried to use.
IF nRecordId>0
TRY
oSqlUpd:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
RETURN (.F.)
END
cSqlUpd := "SELECT * FROM capsonic_receive WHERE id=" + LTRIM(STR(nRecordId,9))
TRY
oSqlUpd:Open( cSqlUpd, xSQL )
CATCH oError
MsgWait( cSqlUpd + " - " + xSQL, "Failed to Connect to the Database" )
RETURN .F.
END
// Init...
cRecvNo := LTRIM(STR(oSqlUpd:Fields( "RECEIVENO" ):Value,14))
dRecvDate := oSqlUpd:Fields( "RECEIVEDATE" ):Value
nRecvQty := oSqlUpd:Fields( "QTYRECEIVED" ):Value
cRecvPart := oSqlUpd:Fields( "PARTNUMBER" ):Value
cRecvLot := oSqlUpd:Fields( "LOTNUMBER" ):Value
cRecvPo := oSqlUpd:Fields( "PURCHASEORDER" ):Value
cRecvPoLine := oSqlUpd:Fields( "PURCHASEORDERLINE" ):Value
nRecvLabels := oSqlUpd:Fields( "LABELS" ):Value
// Close...
oSqlUpd:Close()
oSqlUpd:=NIL
ENDIF
// Delete...
IF cAction=="D"
lPass := MsgNoYes( "Are you sure you want to Cancel Receiving " + cRecvNo + "?", Ptitle )
IF ! lPass
RETURN (.T.)
ENDIF
// Delete...
cSqlUpd := "DELETE FROM capsonic_receive WHERE id="+LTRIM(STR(nRecordId,9))
TRY
oSqlUpd:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
END
TRY
oSqlUpd:Open( cSqlUpd, xSQL )
oSqlUpd:Close()
MsgWait( "Record " + cRecvNo + " Deleted!" )
CATCH oError
MsgWait( cSqlUpd + " - " + xSQL, "Failed to Connect to the Database" )
END
RETURN (.T.)
ENDIF
I finally made the move from DBFCDX to SQL via ADO and XBROWSE and I am having a few issues but I am sure they are easy to correct. I believe they are simple fundamental data handling differences.
1. I open application by connecting to SQL and listing data for a specific table, this part is ok, however I need to be able to allow user to enter criteria then refresh the already opened XBROWSE to new data
2. I want to connect to SQL get a column value then increase that column value by one, I am using table and column as a counter to handle a receipt number
3. I want to connect to SQL and insert, update or delete data (I got this part) then I need to refresh XBROWSE with new data
If someone could provide a basic layout of the process of connecting to SQL, listing data in XBROWSE, performing a SQL statement, then refreshing the XBROWSE I would truly be appreciative!!
Here are sample code I tried to use.
IF nRecordId>0
TRY
oSqlUpd:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
RETURN (.F.)
END
cSqlUpd := "SELECT * FROM capsonic_receive WHERE id=" + LTRIM(STR(nRecordId,9))
TRY
oSqlUpd:Open( cSqlUpd, xSQL )
CATCH oError
MsgWait( cSqlUpd + " - " + xSQL, "Failed to Connect to the Database" )
RETURN .F.
END
// Init...
cRecvNo := LTRIM(STR(oSqlUpd:Fields( "RECEIVENO" ):Value,14))
dRecvDate := oSqlUpd:Fields( "RECEIVEDATE" ):Value
nRecvQty := oSqlUpd:Fields( "QTYRECEIVED" ):Value
cRecvPart := oSqlUpd:Fields( "PARTNUMBER" ):Value
cRecvLot := oSqlUpd:Fields( "LOTNUMBER" ):Value
cRecvPo := oSqlUpd:Fields( "PURCHASEORDER" ):Value
cRecvPoLine := oSqlUpd:Fields( "PURCHASEORDERLINE" ):Value
nRecvLabels := oSqlUpd:Fields( "LABELS" ):Value
// Close...
oSqlUpd:Close()
oSqlUpd:=NIL
ENDIF
// Delete...
IF cAction=="D"
lPass := MsgNoYes( "Are you sure you want to Cancel Receiving " + cRecvNo + "?", Ptitle )
IF ! lPass
RETURN (.T.)
ENDIF
// Delete...
cSqlUpd := "DELETE FROM capsonic_receive WHERE id="+LTRIM(STR(nRecordId,9))
TRY
oSqlUpd:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
END
TRY
oSqlUpd:Open( cSqlUpd, xSQL )
oSqlUpd:Close()
MsgWait( "Record " + cRecvNo + " Deleted!" )
CATCH oError
MsgWait( cSqlUpd + " - " + xSQL, "Failed to Connect to the Database" )
END
RETURN (.T.)
ENDIF