Display Data in DatagridView with condition

Question

I have Database named DataOberge and Table named TableOberge and field ( Id,FirstName,Phone,DateOut,HourOut,DateIN,HourIN ).field DateOut and DateIN are of type Date.field HourOut and HourIN are of type Time. How to display in datagridview2 the people who arrive today's Date and Time its depends on the field DateIN and HourIN. This is all my code :

 Imports System.Data.SqlClient
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

Dim InfoCommand As SqlCommand Dim StrCon As SqlConnection = Nothing Try StrCon = New SqlConnection("data source=ABIDINE; initial catalog= DataOberge;User ID=sa;Password=123456789;") Using DepCom As SqlCommand = New SqlCommand("Select * From TableOberge", StrCon) StrCon.Open() Using DepAdap As SqlDataAdapter = New SqlDataAdapter(DepCom) Dim DepDT As DataTable = New DataTable DepAdap.Fill(DepDT) DataGridView1.DataSource = DepDT Dim CurrentBs As BindingSource = New BindingSource() CurrentBs.DataSource = DepDT DataGridView2.DataSource = CurrentBs 'CurrentBs.Filter = String.Format("[DateIN] = #{0}# AND [HourIN] >= #{1}#", DateTime.Now.Date, DateTime.Now.Hour) CurrentBs.Filter = String.Format("[DateIN] = #{0}# AND [HourIN] >= #{1}#", DateTime.Now.Date, DateTime.Now.Hour) End Using StrCon.Close() End Using Catch ex As Exception Console.WriteLine(ex.Message) Finally If StrCon IsNot Nothing Then If StrCon.State = ConnectionState.Open Then StrCon.Close() End If StrCon.Dispose() End If End Try End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim StrCon As New SqlConnection
    StrCon = New SqlConnection("data source=ABIDINE; initial catalog= DataOberge;User ID=sa;Password=123456789;")
    Using Command As New SqlCommand With {.Connection = StrCon}
        With Command
            .CommandText = "INSERT INTO [TableOberge] ([ID], [FIRSTNAME], [PHONE], [ADRESSE], [DATE_OUT], [HOUR_OUT], [DATE_IN], [HOUR_IN]) VALUES (@ID, @FIRSTNAME, @PHONE, @ADRESSE, @DATE_OUT, @HOUR_OUT, @DATE_IN, @HOUR_IN)"
            .Parameters.Add("@ID", SqlDbType.Int).Value = TextBox1.Text
            .Parameters.Add("@FIRSTNAME", SqlDbType.NVarChar).Value = TextBox2.Text
            .Parameters.Add("@PHONE", SqlDbType.NVarChar).Value = TextBox3.Text
            .Parameters.Add("@ADRESSE", SqlDbType.NVarChar).Value = TextBox4.Text
            .Parameters.Add("@DATE_OUT", SqlDbType.Date).Value = TextBox5.Text
            .Parameters.Add("@HOUR_OUT", SqlDbType.Time).Value = TextBox6.Text
            .Parameters.Add("@DATE_IN", SqlDbType.Date).Value = TextBox7.Text
            .Parameters.Add("@HOUR_IN", SqlDbType.Time).Value = TextBox8.Text
        End With
        If StrCon.State = ConnectionState.Closed Then StrCon.Open()
        If Command.ExecuteNonQuery() = 1 Then
            MsgBox("SUCCED ADD", MsgBoxStyle.MsgBoxRtlReading, "SUCCES")
        Else
            MsgBox("ERROR FATAL", MsgBoxStyle.MsgBoxRtlReading, "ERROR")
        End If
        StrCon.Close()
    End Using
End Sub

End Class

David David · Accepted Answer · 2018-05-06T18:08:57

You would specify a WHERE clause in your SQL command that populates the data in DataGridView2. It would probably be best to add a BindingSource to your Form and bind it to the DataTable you populated, this is so you can set the filter. Try something like this:

'Declare the connection object
Dim StrCon As SqlConnection = Nothing

'Wrap code in Try/Catch
Try
    'Set the connection object to a new instance
    'TODO: Change "My Connection String Here" with a valid connection string
    StrCon = New SqlConnection("My Connection String Here")

    'Create a new instance of the command object
    Using DepCom As SqlCommand = New SqlCommand("Select * From TableOberge", StrCon)

        'Open the connection
        StrCon.Open()

        'Create a new instance of the data adapter object
        Using DepAdap As SqlDataAdapter = New SqlDataAdapter(DepCom)
            'Create a new instance of a DataTable
            Dim DepDT As DataTable = New DataTable

            'Use the DataAdapter to fill the data from the SqlCommand into the DataTable
            DepAdap.Fill(DepDT)

            'Set the DataSource of the DataGridView to the filled DataTable
            DataGridView1.DataSource = DepDT

            'Create a new instance of a BindingSource
            Dim CurrentBs As BindingSource = New BindingSource()

            'Setup the properties of the BindingSource
            CurrentBs.DataSource = DepDT

            'Bind the BindingSource to the DataGridView
            DataGridView2.DataSource = CurrentBs

            'Filter the data in the BindingSource to today's date and anything on or after the current hour
            CurrentBs.Filter = $"[DateIN] = #{DateTime.Now.Date}# AND [HourIN] >= #{DateTime.Now.Hour}#"
        End Using

        'Close the connection
        StrCon.Close()
        End Using
Catch ex As Exception
    'Display the error
    Console.WriteLine(ex.Message)
Finally
    'Check if the connection object was initialized
    If StrCon IsNot Nothing Then
        If StrCon.State = ConnectionState.Open Then
            'Close the connection if it was left open(exception thrown)
            StrCon.Close()
        End If

        'Dispose of the connection object
        StrCon.Dispose()
    End If
End Try

Alternatively, if you didn't want to use the BindingSource, then just create a new SqlCommand and SqlDataAdapter and then do the same thing only for your DataGridView2 using the appropriate SQL query.

UPDATE

In my example I use String Interpolation, but I found that the OP is using a version of Visual Studios that does not support it. Because of this, change the following:

'From:
CurrentBs.Filter = $"[DateIN] = #{DateTime.Now.Date}# AND [HourIN] >= #{DateTime.Now.Hour}#"

'To:
CurrentBs.Filter = String.Format("[DateIN] = #{0}# AND [HourIN] >= #{1}#", DateTime.Now.Date, DateTime.Now.Hour)

Display Data in DatagridView with condition

1 Answers