Welcome Guest!
 VB.NET Helper
 Previous Message All Messages Next Message 
VB .NET Helper newsletter  Rod Stephens
 Feb 22, 2011 16:46 PST 

If you've been waiting for an easy way to move from VB 6 to VB .NET,
wait no longer! My latest book, "Stephens' Visual Basic Programming
24-Hour Trainer" is finally available! Here are the URLs for Amazon's
site and my site:

    
http://www.amazon.com/Stephens-Visual-Programming-24-Hour-Trainer/dp/0470943351/

    http://www.vb-helper.com/24hourvb.html

Be warned that this is a book for beginners who have never programmed
before. If you have experience in some other language (and you probably
have experinec with VB 6 or VB .NET or you wouldn't be on this list),
then you'll find this book very easy.

This book won't tell you everything you need to know to become an expert
but it will get you started quickly and painlessly.

To get people started, I'm giving away several copies of the book. If
you want a copy and are willing to (1) pay for postage and (2) post a
review, send me your name and postal address. I'll probably have the
drawing on March 7.

Have a great week and thanks for subscribing!

Rod
RodSte-@vb-helper.com

Twitter feeds:
    VBHelper
    CSharpHelper
----------
==========

    VB.NET Contents:
1. New HowTo: Use combo boxes and text boxes to let the user pick search
criteria for a database query in Visual Basic .NET
2. New HowTo: Flush click events to prevent the user from clicking a
button while its code is still running in Visual Basic .NET
==========
++++++++++
      <VB.NET>
++++++++++
==========
1. New HowTo: Use combo boxes and text boxes to let the user pick search
criteria for a database query in Visual Basic .NET
http://www.vb-helper.com/howto_net_ado_user_filter.html
http://www.vb-helper.com/HowTo/howto_net_ado_user_filter.zip

The top of this program holds three columns of controls. The left column
contains combo boxes holding the names of the fields in a database
table. The middle column holds operators such as =, <, and >=. The right
column holds text boxes. The user can use these controls to determine
how the program queries the database. For example, if the user selects
the Title field from the first combo box, the >= operator from the
second, and enters R in the first text box, the program searches for
records where the Title field has value >= R.

The following code executes when the form loads. The Form1_Load event
handler calls the PrepareForm method, which gets the form ready for
work.

' The connection object.
Private Conn As OleDbConnection

' The table's column names.
Private ColumnNames As New List(Of String)()
Private TableName As String = ""

' The query controls.
Private CboField(), CboOperator() As ComboBox
Private TxtValue() As TextBox
Private DataTypes As New List(Of Type)()

' Make a list of the table's fields.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
    ' Compose the database file name.
    ' This assumes it's in the executable's directory.
    Dim db_name As String = Application.StartupPath & "\Books.accdb"

    ' Prepare the form for use.
    PrepareForm(db_name, "BookInfo")
End Sub

' Make a list of the table's field names and prepare the first ComboBox.
Private Sub PrepareForm(ByVal db_name As String, ByVal table_name As
String)
    TableName = table_name

    ' Make the connection object.
    Conn = New OleDbConnection( _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & db_name & ";" & _
        "Mode=Share Deny None")

    ' Get the fields in the BookInfo table.
    ' Make a command object to represent the command.
    Dim cmd As New OleDbCommand()
    cmd.Connection = Conn
    cmd.CommandText = "SELECT TOP 1 * FROM " & table_name

    ' Open the connection and execute the command.
    Try
        ' Open the connection.
        Conn.Open()

        ' Execute the query. The reader gives access to the results.
        Dim reader As OleDbDataReader = cmd.ExecuteReader()

        ' Get field information.
        Dim schema As DataTable = reader.GetSchemaTable()
        For Each schema_row As DataRow In schema.Rows
            ColumnNames.Add(schema_row.Field(Of String)("ColumnName"))
            DataTypes.Add(schema_row.Field(Of Type)("DataType"))
            '@ Console.WriteLine(schema_row.Field(Of
Type)("DataType").ToString())
        Next schema_row

        ' Initialize the field name ComboBoxes.
        CboField = New ComboBox() {cboField0, cboField1, cboField2,
cboField3}
        CboOperator = New ComboBox() {cboOperator0, cboOperator1,
cboOperator2, cboOperator3}
        TxtValue = New TextBox() {txtValue0, txtValue1, txtValue2,
txtValue3}
        For i As Integer = 0 To CboField.Length - 1
            CboField(i).Items.Add("")           ' Allow a blank field
choice.
            For Each field_name As String In ColumnNames
                CboField(i).Items.Add(field_name)
            Next field_name
            CboField(i).SelectedIndex = 0       ' Select the blank
choice.
            CboOperator(i).SelectedIndex = 0    ' Select the blank
choice.
        Next i
    Catch ex As Exception
        MessageBox.Show("Error reading " & table_name & " column names."
& vbCrLf & ex.Message)
    Finally
        ' Close the connection whether we succeed or fail.
        Conn.Close()
    End Try
End Sub

PrepareForm creates a database connection object. It then selects a
record from the indicated database table so it can get information about
that table. It saves the table's column names in the ColumnNames list
and it saves the column data types in the DataTypes list. It then uses
the ColumnNames list to initialize the left column of combo boxes that
display the column names.

When the user makes selections and clicks Query, the following code
executes.

' Build and execute the appropriate query.
Private Sub btnQuery_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnQuery.Click
    Dim where_clause As String = ""
    For i As Integer = 0 To CboField.Length - 1
        ' See if the field and operator are non-blank.
        If ((CboField(i).SelectedIndex <= 0) OrElse _
            (CboOperator(i).SelectedIndex <= 0)) _
        Then
            ' Don't use this row. Clear it to prevent confusion.
            CboField(i).SelectedIndex = 0
            CboOperator(i).SelectedIndex = 0
            TxtValue(i).Clear()
        Else
            ' See what delimiter we need for this type of field.
            Dim delimiter As String = ""
            Dim value As String = TxtValue(i).Text
            Dim column_num As Integer = CboField(i).SelectedIndex - 1
            If (DataTypes(column_num) Is GetType(System.String)) Then
                delimiter = "'"
                value = value.Replace("'", "''")
            ElseIf (DataTypes(column_num) Is GetType(System.DateTime))
Then
                ' Use # for Access, ' for SQL Server.
                delimiter = "#"
            End If

            ' Add the constraint to the WHERE clause.
            where_clause &= " AND " & _
                CboField(i).SelectedItem.ToString() & " " & _
                CboOperator(i).SelectedItem.ToString() & " " & _
                delimiter & value & delimiter
        End If ' if field and operator are selected.
    Next i ' For i As Integer = 0 To CboField.Length - 1

    ' If where_clause is non-blank, remove the initial " AND ".
    If (where_clause.Length > 0) Then where_clause =
where_clause.Substring(5)

    ' Compose the query.
    Dim query As String = "SELECT * FROM " & TableName
    If (where_clause.Length > 0) Then query &= " WHERE " & where_clause
    '@ Console.WriteLine("Query: " & query)

    ' Create a DataAdapter to load the data.
    Dim data_adapter As New OleDbDataAdapter(query, Conn)

    ' Create a DataTable.
    Dim data_table As New DataTable()
    Try
        data_adapter.Fill(data_table)
    Catch ex As Exception
        MessageBox.Show("Error executing query " & query & vbCrLf &
ex.Message)
    End Try

    ' Bind the DataGridView to the DataTable.
    dgvBookInfo.DataSource = data_table
End Sub

This code loops through the combo boxes. If the user has selected a
field name and a corresponding operator, the code adds a condition to
the WHERE clause it is building. If the user leaves a field name or
operator blank, the program blanks the other corresponding controls so
it doesn't look like they may be contributing to the final WHERE clause.

When it builds each piece of the WHERE clause, the code uses delimiters
for string and date values. It uses a single quote ' for strings, and it
uses a # for dates. (Access databases require # delimiters for dates.
SQL Server databases require ' delimiters for dates.)

After is has built the WHERE clause, the program composes a final query
and executes it. It uses a data adapter to load the results into a
DataTable and sets the form's DataGridView control's DataSource property
to the DataTable so the user can see the result.
==========
2. New HowTo: Flush click events to prevent the user from clicking a
button while its code is still running in Visual Basic .NET
http://www.vb-helper.com/howto_net_flush_clicks.html
http://www.vb-helper.com/HowTo/howto_net_flush_clicks.zip

If a button starts a long task, you probably don't want the user to be
able to click the button again (or perhaps not anything on the
application) until the task finishes. The following code shows a
straightforward attempt to prevent the user from clicking the button
while its code is still executing.

' Wait for 5 seconds.
Private Sub btnWaitNow_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnWaitNow.Click
    ' None of these seem to work.
    'Me.Enabled = False
    'RemoveHandler btnWaitNow.Click, AddressOf btnWaitNow_Click
    btnWaitNow.Enabled = False
    Me.Cursor = Cursors.WaitCursor
    Application.DoEvents()

    lstMessages.Items.Add("Wait Now Start " + DateTime.Now.ToString())
    Refresh()
    System.Threading.Thread.Sleep(5000)
    lstMessages.Items.Add("Wait Now Stop " + DateTime.Now.ToString())

    'Me.Enabled = True
    'AddHandler btnWaitNow.Click, AddressOf btnWaitNow_Click
    btnWaitNow.Enabled = True
    Me.Cursor = Cursors.Default
End Sub

When the event handler starts, it disables the button. It then does its
work and re-enables the button.

Unfortunately this approach doesn't work. Windows very helpfully queues
up any pending mouse events including clicks while your program is busy
and then delivers them when the event handler finishes so you can
receive the second click. (I could have sworn this approach used to
work.)

One way around this is to use a BackgroundWorker or other threading
technique to perform the work on a separate thread. Disable the button
and then start the thread. When the thread finishes, re-enable the
button. This method works and may have other advantages (such as
allowing the user to interact with other parts of the program while the
button's task is still running), but it's a bit roundabout.

Another approach is to use the PeekMessage API function, as shown in the
following code.

<StructLayout(LayoutKind.Sequential)> _
Private Structure NativeMessage
    Public handle As IntPtr
    Public msg As UInteger
    Public wParam As IntPtr
    Public lParam As IntPtr
    Public time As UInteger
    Public p As System.Drawing.Point
End Structure
Private Declare Auto Function PeekMessage Lib "user32.dll" ( _
    ByRef lpMsg As NativeMessage, _
    ByVal hWnd As IntPtr, _
    ByVal wMsgFilterMin As UInteger, _
    ByVal wMsgFilterMax As UInteger, _
    ByVal flags As UInteger _
) As Boolean
Private Const WM_MOUSEFIRST As UInteger = &H200
Private Const WM_MOUSELAST As UInteger = &H20D
Private Const PM_REMOVE As Integer = &H1

' Flush all pending mouse events.
Private Sub FlushMouseMessages()
    Dim msg As NativeMessage
    ' Repeat until PeekMessage returns false.
    While (PeekMessage(msg, IntPtr.Zero, WM_MOUSEFIRST, WM_MOUSELAST,
PM_REMOVE))

    End While
End Sub

This code includes a bunch of declarations for the API function and its
parameters. (You also need to add using statements for the
System.Runtime.InteropServices and System.Security namespaces. Download
the example for the details.)

The FlushMouseMessages method calls PeekMessage telling it to discard
any message between WM_MOUSELAST and PM_REMOVE. It calls PeekMessage
repeatedly until it returns false to indicate that there are no such
messages.

The following button event handler calls FlushMouseMessage so you cannot
click the button while its code is still running.

' Wait for 5 seconds and then flush the buffer.
Private Sub btnWaitAndFlush_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnWaitAndFlush.Click
    Me.Cursor = Cursors.WaitCursor
    lstMessages.Items.Add("Wait and Flush Start " +
DateTime.Now.ToString())
    Refresh()

    System.Threading.Thread.Sleep(5000)

    lstMessages.Items.Add("Wait and Flush Stop " +
DateTime.Now.ToString())
    FlushMouseMessages()
    Me.Cursor = Cursors.Default
End Sub
==========
Archives:
    http://www.topica.com/lists/VBHelper
    http://www.topica.com/lists/VB6Helper
    http://www.topica.com/lists/VBNetHelper

Twitter feeds:
    VBHelper
    CSharpHelper

Post questions at:
    http://www.topica.com/lists/VBHelperQA
	
 Previous Message All Messages Next Message 
  Check It Out!

  Topica Channels
 Best of Topica
 Art & Design
 Books, Movies & TV
 Developers
 Food & Drink
 Health & Fitness
 Internet
 Music
 News & Information
 Personal Finance
 Personal Technology
 Small Business
 Software
 Sports
 Travel & Leisure
 Women & Family

  Start Your Own List!
Email lists are great for debating issues or publishing your views.
Start a List Today!

© 2001 Topica Inc. TFMB
Concerned about privacy? Topica is TrustE certified.
See our Privacy Policy.