Visual F# 100 Examples: Example Number 16(Search a Record Value)

Problem: Make a Windows Forms Application that will allow the user to search a record value from a database file.

view plainprint?

1. // Learn more about F# at http://fsharp.net  
2. //specifies the memory location of the class files  
3. //that will be needed in our application  
4. open System.Collections.Generic  
5. open System  
6. open System.Windows.Forms  
7. open System.ComponentModel  
8. open System.Data  
9. open System.Drawing  
10. open System.Data.OleDb  
11. //creates a font  
12. let ffont=new Font("Verdana", 9.75F,FontStyle.Regular, GraphicsUnit.Point)    
13. //creates a connection object  
14. let oleconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;  
15.   Data Source=C:\Documents and Settings\Station03\My Documents\dbEmployee.mdb")  
16.  //creates an OleDbDataAdapter  
17. let dataadpter = new System.Data.OleDb.OleDbDataAdapter("Select * from tblEmployee", oleconn)  
18. //generates a dataset  
19. let dataset11 = new DataSet()  
20. //fills the dataset with recod values  
21. dataadpter.Fill(dataset11,"tblEmployee")|>ignore  
22. //creates a form  
23. let dataform = new Form(Text="Search a Record Value",AutoScaleDimensions=new System.Drawing.SizeF(60.0F, 13.0F),ClientSize=new System.Drawing.Size(400, 360),StartPosition=FormStartPosition.CenterScreen)  
24. //creates our controls  
25. let exitbutton=new Button(Text="Exit", Location=new System.Drawing.Point(300, 320))   
26. let searchbutton=new Button(Text="Search", Location=new System.Drawing.Point(220, 320))    
27. let label1=new Label(Text="Enter the employee number:",Location=new System.Drawing.Point(0, 10),AutoSize=true)  
28. let label2=new Label(Text="Empno:",Location=new System.Drawing.Point(0, 50),AutoSize=true)  
29. let label3=new Label(Text="Firstname:",Location=new System.Drawing.Point(0,100),AutoSize=true)  
30. let label4=new Label(Text="Lastname:",Location=new System.Drawing.Point(0,150),AutoSize=true)  
31. let empnotext=new TextBox(Location=new System.Drawing.Point(200,10))  
32. let emplabel=new Label(Location=new System.Drawing.Point(100,50),BorderStyle=BorderStyle.FixedSingle)  
33. let fnamelabel=new Label(Location=new System.Drawing.Point(100,100),BorderStyle=BorderStyle.FixedSingle)  
34. let lnamelabel=new Label(Location=new System.Drawing.Point(100,150),BorderStyle=BorderStyle.FixedSingle)  
35. //creates a datagrid  
36. let datagrid = new DataGridView(ColumnHeadersHeightSizeMode=DataGridViewColumnHeadersHeightSizeMode.AutoSize,Size=new System.Drawing.Size(300, 120),Location=new System.Drawing.Point(10, 180))  
37. //creates a grid control colums  
38. let chrempnocol=new DataGridViewTextBoxColumn()  
39. let chrfnamecol=new DataGridViewTextBoxColumn()  
40. let chrlnamecol=new DataGridViewTextBoxColumn()  
41. //adds the columns into our datagrid  
42. datagrid.Columns.Add(chrempnocol)|>ignore  
43. datagrid.Columns.Add(chrfnamecol)|>ignore  
44. datagrid.Columns.Add(chrlnamecol)|>ignore  
45. datagrid.DataSource <- dataset11.Tables.["tblEmployee"]  
46. //assingns the font to our form  
47. dataform.Font<-ffont  
48. //links our fieldname to each grid  
49. //and change its header text  
50. chrempnocol.DataPropertyName<-"chrempno"  
51. chrempnocol.HeaderText<-"Employee No."  
52. chrfnamecol.DataPropertyName<-"chrfname"  
53. chrfnamecol.HeaderText<-"First Name"  
54. chrlnamecol.DataPropertyName<-"chrlname"  
55. chrlnamecol.HeaderText<-"Last Name"  
56. //add the datagrid to our form  
57. dataform.Controls.Add(datagrid)  
58. //adds the controls to our form  
59. dataform.Controls.Add(exitbutton)  
60. dataform.Controls.Add(searchbutton)  
61. dataform.Controls.Add(label1)  
62. dataform.Controls.Add(label2)  
63. dataform.Controls.Add(label3)  
64. dataform.Controls.Add(label4)  
65. dataform.Controls.Add(empnotext)  
66. dataform.Controls.Add(emplabel)  
67. dataform.Controls.Add(fnamelabel)  
68. dataform.Controls.Add(lnamelabel)  
69. //binds the fieldnames to our label  
70. emplabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(0).Item(0))  
71. fnamelabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(0).Item(1))  
72. lnamelabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(0).Item(2))  
73. searchbutton.Click.Add(fun search->    
74.                     //handles the row index number                 
75.                     let mutable introws=0  
76.                     //determines if the record has been found or not  
77.                     let mutable blnfound=false     
78.                     //handles the total number of records                  
79.                     let mutable inttotrec=Convert.ToInt32(dataset11.Tables.["tblEmployee"].Rows.Count)  
80.                     //handles the data inputted by the user  
81.                     let strtext=Convert.ToString(empnotext.Text)  
82.                     //while no match is found and the end of the file has not been reached  
83.                     while((blnfound=false) && (introws<=inttotrec-1)) do  
84.                          let strempnum=Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(introws).Item(0))  
85.                          //compare the data inputted in the textbox to the employee number in our table  
86.                          //if they are equal, display the match record  
87.                          if strtext.ToUpper()=strempnum.ToUpper() then  
88.                             blnfound<-true  
89.                             emplabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(introws).Item(0))  
90.                             fnamelabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(introws).Item(1))  
91.                             lnamelabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(introws).Item(2))   
92.                          //compare to the next record while no match is found  
93.                          introws<-introws + 1      
94.                     //if no match is found, display this        
95.                     if blnfound=false then  
96.                         MessageBox.Show("Record not found.","Search a Record Value",MessageBoxButtons.OK,MessageBoxIcon.Information)|>ignore)                         
97. //when the exit button is clicked  
98. exitbutton.Click.Add(fun exit->  
99. //close the form and dataconnection  
100.                     dataform.Close()  
101.                     oleconn.Close())   
102. //executes our application  
103. Application.Run(dataform)  

// Learn more about F# at http://fsharp.net
//specifies the memory location of the class files
//that will be needed in our application
open System.Collections.Generic
open System
open System.Windows.Forms
open System.ComponentModel
open System.Data
open System.Drawing
open System.Data.OleDb
//creates a font
let ffont=new Font("Verdana", 9.75F,FontStyle.Regular, GraphicsUnit.Point)  
//creates a connection object
let oleconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
  Data Source=C:\Documents and Settings\Station03\My Documents\dbEmployee.mdb")
 //creates an OleDbDataAdapter
let dataadpter = new System.Data.OleDb.OleDbDataAdapter("Select * from tblEmployee", oleconn)
//generates a dataset
let dataset11 = new DataSet()
//fills the dataset with recod values
dataadpter.Fill(dataset11,"tblEmployee")|>ignore
//creates a form
let dataform = new Form(Text="Search a Record Value",AutoScaleDimensions=new System.Drawing.SizeF(60.0F, 13.0F),ClientSize=new System.Drawing.Size(400, 360),StartPosition=FormStartPosition.CenterScreen)
//creates our controls
let exitbutton=new Button(Text="Exit", Location=new System.Drawing.Point(300, 320)) 
let searchbutton=new Button(Text="Search", Location=new System.Drawing.Point(220, 320))  
let label1=new Label(Text="Enter the employee number:",Location=new System.Drawing.Point(0, 10),AutoSize=true)
let label2=new Label(Text="Empno:",Location=new System.Drawing.Point(0, 50),AutoSize=true)
let label3=new Label(Text="Firstname:",Location=new System.Drawing.Point(0,100),AutoSize=true)
let label4=new Label(Text="Lastname:",Location=new System.Drawing.Point(0,150),AutoSize=true)
let empnotext=new TextBox(Location=new System.Drawing.Point(200,10))
let emplabel=new Label(Location=new System.Drawing.Point(100,50),BorderStyle=BorderStyle.FixedSingle)
let fnamelabel=new Label(Location=new System.Drawing.Point(100,100),BorderStyle=BorderStyle.FixedSingle)
let lnamelabel=new Label(Location=new System.Drawing.Point(100,150),BorderStyle=BorderStyle.FixedSingle)
//creates a datagrid
let datagrid = new DataGridView(ColumnHeadersHeightSizeMode=DataGridViewColumnHeadersHeightSizeMode.AutoSize,Size=new System.Drawing.Size(300, 120),Location=new System.Drawing.Point(10, 180))
//creates a grid control colums
let chrempnocol=new DataGridViewTextBoxColumn()
let chrfnamecol=new DataGridViewTextBoxColumn()
let chrlnamecol=new DataGridViewTextBoxColumn()
//adds the columns into our datagrid
datagrid.Columns.Add(chrempnocol)|>ignore
datagrid.Columns.Add(chrfnamecol)|>ignore
datagrid.Columns.Add(chrlnamecol)|>ignore
datagrid.DataSource <- dataset11.Tables.["tblEmployee"]
//assingns the font to our form
dataform.Font<-ffont
//links our fieldname to each grid
//and change its header text
chrempnocol.DataPropertyName<-"chrempno"
chrempnocol.HeaderText<-"Employee No."
chrfnamecol.DataPropertyName<-"chrfname"
chrfnamecol.HeaderText<-"First Name"
chrlnamecol.DataPropertyName<-"chrlname"
chrlnamecol.HeaderText<-"Last Name"
//add the datagrid to our form
dataform.Controls.Add(datagrid)
//adds the controls to our form
dataform.Controls.Add(exitbutton)
dataform.Controls.Add(searchbutton)
dataform.Controls.Add(label1)
dataform.Controls.Add(label2)
dataform.Controls.Add(label3)
dataform.Controls.Add(label4)
dataform.Controls.Add(empnotext)
dataform.Controls.Add(emplabel)
dataform.Controls.Add(fnamelabel)
dataform.Controls.Add(lnamelabel)
//binds the fieldnames to our label
emplabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(0).Item(0))
fnamelabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(0).Item(1))
lnamelabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(0).Item(2))
searchbutton.Click.Add(fun search->  
                    //handles the row index number               
                    let mutable introws=0
                    //determines if the record has been found or not
                    let mutable blnfound=false   
                    //handles the total number of records                
                    let mutable inttotrec=Convert.ToInt32(dataset11.Tables.["tblEmployee"].Rows.Count)
                    //handles the data inputted by the user
                    let strtext=Convert.ToString(empnotext.Text)
                    //while no match is found and the end of the file has not been reached
                    while((blnfound=false) && (introws<=inttotrec-1)) do
                         let strempnum=Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(introws).Item(0))
                         //compare the data inputted in the textbox to the employee number in our table
                         //if they are equal, display the match record
                         if strtext.ToUpper()=strempnum.ToUpper() then
                            blnfound<-true
                            emplabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(introws).Item(0))
                            fnamelabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(introws).Item(1))
                            lnamelabel.Text<-Convert.ToString(dataset11.Tables.["tblEmployee"].Rows.Item(introws).Item(2)) 
                         //compare to the next record while no match is found
                         introws<-introws + 1    
                    //if no match is found, display this      
                    if blnfound=false then
                        MessageBox.Show("Record not found.","Search a Record Value",MessageBoxButtons.OK,MessageBoxIcon.Information)|>ignore)                       
//when the exit button is clicked
exitbutton.Click.Add(fun exit->
//close the form and dataconnection
                    dataform.Close()
                    oleconn.Close()) 
//executes our application
Application.Run(dataform)

Since Visual F# is very particular with the code indention, I posted below the screenshot of our code in the while loop section.



Here's what it should look like if you click the run icon: