A Generic Record Lookup Form in Visual Basic .NET

 <  Day Day Up  >  

The preceding Visual Basic .NET example included column sorting, which is built into the DataGrid control. In FoxPro, adding column sorting turned out to be a little involved, but the data was easy to get. In Visual Basic .NET, you have to think about data retrieval efficiency.

For small numbers of rows, DataView s are okay. But unlike Visual FoxPro, where opening a table of a few hundred thousands of records has no performance implications to speak of, returning a few hundred thousand records into a grid in order to filter the records is not in the cards in the ADO.NET world. Instead, you have to get your data sparingly. That's how we'll design the lookup form.

Create a form with a grid, three command buttons , a text box, and a label, as shown in Figure 9.7.

Figure 9.7. A generic search form in Visual Basic .NET.
graphics/09fig07.jpg

The code in this form creates a SELECT statement ending in a WHERE clause that matches any string beginning with the letter(s) typed in by the user :

 

 Cmd = "SELECT " + FieldList + " FROM " + TableName     + " WHERE " + SearchField     + " LIKE '" + TextBox1.Text + "%'" 

The resulting command might look like this:

 

 SELECT CustomerID, CompanyName, ContactName FROM CUSTOMERS   WHERE CompanyName LIKE 'A%' 

The code to load the table is trivial, as shown in Listing 9.6.

Listing 9.6. The Code to Build a SELECT String
 Dim cn As New SqlClient.SqlConnection cn.ConnectionString = ConnStr cn.Open() Dim cmd As New SqlClient.SqlCommand cmd.Connection = cn cmd.CommandText = "SELECT * FROM " + TableName _                 + " WHERE " + SearchField + " LIKE '" _                 + MyText1.Text.ToUpper.Trim + "%'" Dim da As New SqlClient.SqlDataAdapter da.SelectCommand = cmd ds.Clear() da.Fill(ds, TableName) dv = ds.Tables(0).DefaultView DataGrid1.DataSource = dv 

This will return only the records that match the filter expression. If you want to match a substring anywhere within the target string, you can include a percent sign at the end of the string " LIKE '" , that is, " LIKE '%" .

However, users who have been spoiled by using FoxPro may still not be prepared for the consequences of issuing a command like this that returns 25,000 records from SQL, never mind returning records from a Web server. So you can avoid dangerous liaisons by returning a count of the records to be returned, and if it passes some threshold (stored in an integer property called MaxRecords ), warn the user (see Listing 9.7).

Listing 9.7. Visual Basic .NET Code to Count Records and Warn the User If the Records Exceed Some Limit
 cmd.CommandText = "SELECT Count(*) FROM " + TableName _     + " WHERE " + SearchField + " LIKE '" + MyText1.Text.ToUpper.Trim + "%'" Dim HowMany As Integer HowMany = cmd.ExecuteScalar If HowMany > MaxRecords Then    Dim s as string = "This query will return " ;     + HowMany.ToString + " records. Proceed?"    If MsgBox(s, _             MsgBoxStyle.Exclamation, Application.ProductName) _             = MsgBoxResult.No Then       Return    End If End If 

The same thing is possible in FoxPro, but not needed for DBFs. For SQL Server, it's easy:

 

 ConnStr = "Driver={SQL Server};server=(local);database=Northwind;UID=sa;PWD=;" handle = SQLSTRINGCONNECT( ConnStr) SQLEXEC( Handle, "SELECT COUNT(*) AS RecCount FROM Customers" ) 

The command returns a cursor named SQLRESULT . If you don't use an AS clause to rename the result of the COUNT(*) expression, the column is named Exp . So the result of the SQLEXEC call in the preceding code line is SQLResult.Reccount , and

 

 SQLEXEC( Handle, "SELECT COUNT(*) FROM Customers" ) 

returns SQLResult.Exp . So you can write the generic code like that shown in Listing 9.8 (again, assume that the form has a property called MaxRecords that's been set to the maximum number of records you want to return without the user's approval).

Listing 9.8. Visual FoxPro SQL Code to Count Records and Warn the User If the Records Exceed Some Limit
 WITH THISFORM FilterExpr = " WHERE " + SearchField + " LIKE '" + .Textbox1.Value + "%'" Cmd = "SELECT COUNT(*) FROM " + .TableName + " WHERE " + FilterExpr SQLExec ( Handle, Cmd ) IF SQLResult.Exp > .MaxRecords    S = "This will return " + TRANSFORM(Exp) + " records; continue?"    IF MessageBox ( s, 292 ) <> 6       RETURN    ENDIF ENDIF Cmd = "SELECT " + FieldList + " FROM " + TableName + FilterExpr SQLExec ( Handle, Cmd ) .Grid1.RecordSource = "SQLResult" ENDWITH 

 <  Day Day Up  >  


Visual Fox Pro to Visual Basic.NET
Visual FoxPro to Visual Basic .NET
ISBN: 0672326493
EAN: 2147483647
Year: 2004
Pages: 130
Authors: Les Pinter

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net