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
REDEFINE xBROWSE oLBX ;
RECORDSET oRsLabor ;
COLUMNS "TECH NAME", ;
"ACTUAL RATE", ;
"FLAT RATE", ;
"DESCRIPTION", ;
"TOTAL" ;
COLSIZES 90,80,80,249,55 ;
HEADERS "Tech", ;
"Actual hh:mm", ;
"Flat hh:mm", ;
"Description", ;
"Total" ;
ID 111 of oDlg ;
AUTOCOLS LINES CELL FASTEDIT
AEval( oLbx:aCols, { |o| o:nEditType := EDIT_GET } )
// tech
oLbx:aCols[1]:nEditType := EDIT_LISTBOX
oLbx:aCols[1]:aEditListTxt := aTech
oLbx:aCols[1]:bOnPostEdit := {|o,v| _GetPullDown( v,oLbx,oRsLabor,aEmp ) }
// actual rate
oLbx:aCols[2]:bOnPostEdit := {|o,v| _ActualChk( v, oRsLabor, oTotal, @nTotal, nShopRate, oLbx),;
_ReCalc( oRsLabor,oTotal,@nTotal,oLbx )}
// flat rate
oLbx:aCols[3]:bOnPostEdit := {|o,v| _FlatChk( v, oRsLabor, oTotal, @nTotal, nShopRate, oLbx),;
_ReCalc( oRsLabor,oTotal,@nTotal,oLbx )}
// Description
oLbx:aCols[4]:nEditType := EDIT_GET
oLbx:aCols[4]:bOnPreEdit := { || If(oRsLabor:Fields("Description"):Value = " ", ,( __Keyboard( Chr( VK_HOME )))) }
// total
oLbx:aCols[5]:nEditType := EDIT_GET //If( oRsLabor:Fields("Flat Rate"):Value = 0,(MsgInfo("Edit None"),EDIT_NONE),EDIT_GET)
oLbx:aCols[5]:bOnPostEdit := {|o,v|_CheckTotal(v, oRsLabor, oLbx,oBtn1,oBtn2,oBtn3 ),;
_ReCalc( oRsLabor,oTotal,@nTotal,oLbx )}
// add a new record
oLbx:bPastEof = {|| _AddNewRow( oRsLabor,nRepairNumber,oLbx,"Y",nKey,oBtn1,oBtn2,oBtn3 )
I just wanted to understand the best way to make a SQL connection, perform SQL statement then close SQL connection. Is that the best way to handle? Or can I make one SQL connection then perform one...several...many SQL statement(s) before closing SQL connection?
//------------------
Static FUNC DELDETL( oLbx,oRsCHk )
LOCAL SAYING
IF xADMIN = 'Y'
ELSE
SAYING := "SORRY ... you do not have ADMIN Rights"
MsgInfo( SAYING )
RETURN(.F.)
ENDIF
IF oRsChk:EOF
SAYING := "SORRY ... before you can Delete a record ... "
SAYING += "you have to Add one first"
MsgInfo( SAYING )
RETURN(.F.)
ENDIF
IF MsgYesNo( "Are you SURE you want to DELETE this?" )
Try
oRsChk:Delete()
Catch
MsgInfo( "ChkCode Delete Failed" )
Return(.f.)
End Try
TRY
oRsChk:MoveNext()
CATCH
END TRY
If oRsChk:eof .and. .not. oRsChk:bof
TRY
oRsChk:MoveFirst()
CATCH
END TRY
Endif
ENDIF
oLbx:ReFresh()
RETURN(NIL)
oCn := CREATEOBJECT( "ADODB.Connection" )
TRY
oCn:Open( xCONNECT )
CATCH oErr
Saying := "Could not open a Connection to Database "+xSource+chr(10)
Saying += "to Delete from Table"+chr(10)
MsgInfo( Saying )
RETURN(.F.)
END TRY
cSql := "DELETE from [Users] where [UserId] = '"+cUserId+"'"
Try
oCn:Execute( cSQL )
Catch
MsgInfo( "Delete from Table USERS Failed" )
Return(.f.)
End try
oCn:CLose()
oCn := NIL
psudo code
cSql := "Select ... Left Join ..... Left join .... "
Create your recordset oRs ... in order to delete a specific row in a complex join .. you will need to use a connection to delete that specific row in that specific table .. as above. Then you can do a simple oRs:ReQuery() or better yet, close the recordset oRs:CLose and then just re-open the oRs with that same Sql statement without re-defining the oRs Variable .. just re-open the query.
xDatabase := "A" // access
*xDatabase := "S" // sql server
If xDatabase = "A"
xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cDEFA+"\Travel.mdb"
xPASSWORD := "password"
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
Else
xPROVIDER := "SQLOLEDB"
xSOURCE := "RICKLIPKIN-PC\SQLEXPRESS"
xCATALOG := "TRAVEL"
xUSERID := "lipkinrm"
xPASSWORD := "richard"
xConnect := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
Endif
oRsVendor := TOleAuto():New( "ADODB.Recordset" )
oRsVendor:CursorType := 1 // opendkeyset
oRsVendor:CursorLocation := 3 // local cache
oRsVendor:LockType := 3 // lockoportunistic
cSQL := "SELECT [UserEid], [UserId], [Password] From Avendor Where [UserId] = '"+cUserId+"'"
TRY
oRsVendor:Open(cSQL,xCONNECT )
CATCH oErr
MsgInfo( "Error in Opening Vendor table" )
RETURN(.F.)
END TRY
Append --> oRecordSet:AddNew()
Close --> oRecordSet:Close()
Commit --> oRecordSet:Update()
Delete --> oRecordSet:Delete()
Deleted() --> oRecordSet:Status == adRecDeleted
EOF() --> oRecordSet:EOF or oRecordSet:AbsolutePosition == -3
Field() --> oRecordSet:Fields( nField - 1 ):Name, :Value, :Type
FCount() --> oRecordSet:Fields:Count
GoTop --> oRecordSet:MoveFirst()
GoBottom --> oRecordSet:MoveLast()
Locate --> oRecordSet:Find( cFor, If( lContinue, 1, 0 ) )
Open --> oRecordSet:Open( cQuery, hConnection )
OrdListClear() --> oRecordSet:Index := ""
RecCount(), LastRec() --> oRecordSet:RecordCount
RecNo() --> oRecordSet:AbsolutePosition
Skip --> oRecordSet:Move( nToSkip )
lucasdebeltran wrote:Hello,
I am interested in ADO too.
Please, can you post a sample that adds, deletes, finds, filters, reports and xBrowses?.
I think it will be very usefull to migrate.
Thank you very much.
RecNo() --> oRecordSet:AbsolutePosition
but it is missing network usage, locks, filters and reports.
SAYING := "SORRY ... you do not have ADMIN Rights"
SAYING := "SORRY ... before you can Delete a record ... "
SAYING += "you have to Add one first"
In many ways ... ADO is much simpler to use than .dbf .. once you create the recordset object .. all you have to do is pass the oRs ( variable ) instead of using 'work areas' to your functions that manipulate the fields and data... also there is no need for record or file locking.
oCustomer:= TCustomer():new()
msgInfo( oCustomer:name )
oCustomer:= TCustomer():new()
msgInfo( oCustomer:name )
// Example use a DBF
Class TCustomer from TDatabase
method New()
endclass
method New() class TCustomer
super():new(,"customer",,.t.)
return self
//---------------------------------------------------------------------------//
// Example using a Recordset
Class TCustomer from TRecordset
method new()
endclass
method new() CLASS TCustomer
super():new( "SELECT * FROM Customers ORDER BY CustomerID", "Microsoft.Jet.OleDB.4.0", "Northwind.mdb" )
return self
Return to FiveWin for Harbour/xHarbour
Users browsing this forum: No registered users and 82 guests