Tuesday, April 21, 2009
Functions and Procedures in VFP 101
When working in Visual FoxPro, functions are procedures are almost the same thing. Look at the following code:
FUNCTION DoSomething
LPARAMETERS Param1, Param2
? "Param 1= " + Param1
? "Param 2= " + Param2
PROCEDURE DoSomethingElse
LPARAMETERS Param1, Param2
? "Param 1= " + Param1
? "Param 2= " + Param2
The function and procedure are actually identical. The difference to determine whether you have a function or procedure is not how they are defined, but how they are called. You can even call them the same way.
DO DoSomething WITH "Hello", "Procedure"
DoSomething("Hello", "Procedure")
You'll notice that DoSomething was defined as a procedure, but we can call it as either a procedure or a function. We can do the same thing with DoSomethingElse.
The difference is how the parameters are passed. When called as a procedure, the parameters are passed by reference. When called as a function, the parameters are passed by value. You can change how they?re passed in a function with SET UDFPARMS TO REFERENCE but you can?t change how parameters are passed when calling the routine as a procedure.
So, which should you use? I think you should always call the routine as a function because the routine cannot accidently change the value of the parameter. This is the kind of nasty side effect that can be difficult to track down.
Labels: VFP
Friday, October 17, 2008
Winforms, ActiveX, WPF, and VFP
Do you need to create an ActiveX control for you VFP app? Are you adding functionality to your VFP app though .Net? Are you migrating your VFP app to .Net, but can't move the entire app at one time? The Forms Interop Toolkit was developed to assist you in all these scenarios. I've just published a new paper, Integrating .Net Winforms with Visual FoxPro that show how to use the Toolkit to do all this. As an added bonus, the paper also shows how to COM-enable a WPF control and use it on a VFP form.
Labels: .Net, ActiveX, VFP, Winforms, WPF
Thursday, August 28, 2008
Don't FILTER Data in a Grid
I've seen several postings on the forums lately where people ask about how to FILTER data displayed in a grid. This is a very bad idea as the scroll bars and thumb don't work correctly. Look at this form, that uses a filter.
The underlying table has over 200,000 rows. I appyled SET FILTER TO Lname = "SMITH". The result is not good. Notice the postion of the thumb. When I pull the thumb to the top of the scroll bar, it jumps back to the postition shown in the screen shot because that's where the current record is based on the entire table. This is very confusing to a user.
Now look at what happens when you use a query or a view.
In this form, I set up a local view and set the grid to use the view as the data source. I then requeried the data using a View Parameter. Notice where that the thumb is located at the top of the scrollbar when the record pointer is on the first record. This is because the data for the grid only contains rows where Lname = "SMITH" rather than the entire table.
Writing applications this way may require a retraining of the users as they won't have all the data at the same time, but will always need to query the data they want. My experience shows that users prefer this type of application. YMMV.
An added bonus is that you're preparing yourself to work with SQL Server as it only uses sets of data. You always need to query for the data you want and get back only that data rather than everything.
Labels: VFP
Wednesday, June 25, 2008
Documenting a SQL Server Database
I recently had a need to document the schema of a SQL Server database. The requirement was to have one worksheet per table and match the formatting done on similar project. The database I actually had to document contains over 300 tables. I worked up some Visual FoxPro code to do this automatically.
* Document SQL tables into Excel
LOCAL oXl AS Excel.Application
LOCAL oSheet AS EXCEL.Sheets
LOCAL oActiveSheet AS EXCEL.Worksheet
oXl = CREATEOBJECT("Excel.Application")
oXl.Visible = .T.
oWkb = oXl.Workbooks.Add()
oActiveSheet = NULL
lnHandle = SQLSTRINGCONNECT("Driver=SQL Native Client;Server=.;database=Northwind;Trusted_Connection=yes")
SQLEXEC(lnHandle, "Exec sp_tables", "cTables")
SELECT cTables
INDEX ON Table_Type TAG TableType DESCENDING
SCAN FOR Table_Type = "TABLE"
oSheet = oXl.Sheets.Add()
loActiveSheet = oXl.ActiveSheet
SQLEXEC(lnHandle, "Exec sp_columns '" + ALLTRIM(cTables.Table_Name)
+ "'", "cColumns")
* Add the top two rows with table information after adding the column stuff
* so that the columns will autosize properly
XLAddColumn(loActiveSheet, "cColumns")
loActiveSheet = XLAddTable(loActiveSheet, cTables.Table_Name)
ENDSCAN
SQLDISCONNECT(lnHandle)
oSheet = NULL
oWkb = NULL
oXl = NULL
*********************************
FUNCTION XLAddTable(toActiveSheet, tcTableName)
LOCAL lcTable
LOCAL toActiveSheet AS EXCEL.Worksheet
lcTable = ALLTRIM(tcTableName)
* Handle the sheet title (table name)
toActiveSheet.Name = lcTable
toActiveSheet.Range("A1") = ALLTRIM(tcTableName) + " Table"
toActiveSheet.Range("A1").Font.Name = "Arial"
toActiveSheet.Range("A1").Font.Size = 14
toActiveSheet.Range("A1", "F1").Interior.Color = 12632256
toActiveSheet.Rows(1).RowHeight = 24.75
toActiveSheet.Range("A1:F1").Borders(7).Color = RGB(0, 0, 0)
toActiveSheet.Range("A1:F1").Borders(8).Color = RGB(0, 0, 0)
toActiveSheet.Range("A1:F1").Borders(9).Color = RGB(0, 0, 0)
toActiveSheet.Range("A1:F1").Borders(10).Color = RGB(0, 0, 0)
* Column Titles
toActiveSheet.Range("A2") = "Column Name"
toActiveSheet.Range("A2").Borders(7).Color = RGB(0, 0, 0)
toActiveSheet.Range("A2").Borders(8).Color = RGB(0, 0, 0)
toActiveSheet.Range("A2").Borders(9).Color = RGB(0, 0, 0)
toActiveSheet.Range("A2").Borders(10).Color = RGB(0, 0, 0)
* Data type
toActiveSheet.Range("B2") = "Data Type"
toActiveSheet.Range("B2").Borders(7).Color = RGB(0, 0, 0)
toActiveSheet.Range("B2").Borders(8).Color = RGB(0, 0, 0)
toActiveSheet.Range("B2").Borders(9).Color = RGB(0, 0, 0)
toActiveSheet.Range("B2").Borders(10).Color = RGB(0, 0, 0)
* Precision
toActiveSheet.Range("C2") = "Precision"
toActiveSheet.Range("C2").Borders(7).Color = RGB(0, 0, 0)
toActiveSheet.Range("C2").Borders(8).Color = RGB(0, 0, 0)
toActiveSheet.Range("C2").Borders(9).Color = RGB(0, 0, 0)
toActiveSheet.Range("C2").Borders(10).Color = RGB(0, 0, 0)
* Length
toActiveSheet.Range("D2") = "Length"
toActiveSheet.Range("D2").Borders(7).Color = RGB(0, 0, 0)
toActiveSheet.Range("D2").Borders(8).Color = RGB(0, 0, 0)
toActiveSheet.Range("D2").Borders(9).Color = RGB(0, 0, 0)
toActiveSheet.Range("D2").Borders(10).Color = RGB(0, 0, 0)
* Scale
toActiveSheet.Range("E2") = "Scale"
toActiveSheet.Range("E2").Borders(7).Color = RGB(0, 0, 0)
toActiveSheet.Range("E2").Borders(8).Color = RGB(0, 0, 0)
toActiveSheet.Range("E2").Borders(9).Color = RGB(0, 0, 0)
toActiveSheet.Range("E2").Borders(10).Color = RGB(0, 0, 0)
* Comments
toActiveSheet.Range("F2") = "Comments"
toActiveSheet.Range("F2").Borders(7).Color = RGB(0, 0, 0)
toActiveSheet.Range("F2").Borders(8).Color = RGB(0, 0, 0)
toActiveSheet.Range("F2").Borders(9).Color = RGB(0, 0, 0)
toActiveSheet.Range("F2").Borders(10).Color = RGB(0, 0, 0)
* Format the cells
toActiveSheet.Range("A2", "F2").Font.Bold = .T.
toActiveSheet.Range("A2:F2").VerticalAlignment = 2
toActiveSheet.Range("A2", "F2").Interior.Color = 8454143
toActiveSheet.Rows(2).RowHeight = 26.25
toActiveSheet.Columns("B").Autofit()
toActiveSheet.Columns("C").Autofit()
toActiveSheet.Columns("D").Autofit()
toActiveSheet.Columns("E").Autofit()
toActiveSheet.Columns("F").Autofit()
RETURN loActiveSheet
ENDFUNC
*********************************
FUNCTION XLAddColumn(toSheet, tcColCursor)
LOCAL lnWorkArea
LOCAL toSheet AS EXCEL.Worksheet
lnWorkArea = SELECT()
tcCell = ""
SELECT (tcColCursor)
SCAN
* Column name
tcCell = "A" + ALLTRIM(STR(RECNO() + 2))
toSheet.Range(tcCell) = ALLTRIM(Column_Name)
toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
* Data Type
tcCell = "B" + ALLTRIM(STR(RECNO() + 2))
toSheet.Range(tcCell) = ALLTRIM(Type_Name)
toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
* Precision
tcCell = "C" + ALLTRIM(STR(RECNO() + 2))
toSheet.Range(tcCell) = Precision
toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
* Length
tcCell = "D" + ALLTRIM(STR(RECNO() + 2))
toSheet.Range(tcCell) = Length
toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
* Scale
tcCell = "E" + ALLTRIM(STR(RECNO() + 2))
toSheet.Range(tcCell) = Scale
toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
* Comments
tcCell = "F" + ALLTRIM(STR(RECNO() + 2))
toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
ENDSCAN
toSheet.Columns("A").Autofit()
SELECT (lnWorkArea)
ENDFUNC
Saturday, April 26, 2008
New VFP Help File Available
A new VFP 9.0 SP2 help file is now available for download from the VFP home page. One issue that people are running into is that pages don't display or links aren't available in the file. If this happens to you, open Windows Explorer, right-click on the file and select Properties. Then click on Unblock.
Labels: VFP
Subscribe to Posts [Atom]
