none
What is the best way to go for getting Data from either Datagridview or an SQL Table RRS feed

  • Question

  • Hi Good People.

    I have created a form with 3 textboxes and 2 DateTimePickers 

    textbox1 I have called; TxtDateFrom, With DateTimePicker1 Called DtpFrom set behind TxtDateFrom.

    textbox2 I have called TxtDateTo with DateTimePicker2 Called DtpTo Set behind TxtDateTo

    What is the best way to go to search a Database or DataGridView and return a Value.

    Basically Is what I trying to achieve is when I select a Date From, Using " DtpFrom " then select a second Date To Using "   "DtpTo". 

    And Once I have selected those two Dates and click a button Is what I want is to search from all the monies that has been taken between the dates selected, And then put the total Value of Values  into a textbox. How do I go about this.

    Code I started with: 

    If SQL. RecordCount <1 Then Exit Sub
    
    SQL.RunQuery ("Select * From JobsComplete " )
     

    Can one of you good people show or tell me the way to go, about getting a total Value from 1 column using 2 set of dates. The problem is I only have one column with a Date.

    Kind Regards

    Gary



    Gary Simpson


    • Edited by Gary Simpson Thursday, June 18, 2020 7:03 PM To Add Picture
    Thursday, June 18, 2020 6:56 PM

Answers

  • Hi

    Here is an example that may be helpful.

    ' DT DGV ROWFILTER DATE SORT 
    
    ' Form1 with DataGridView1 and
    ' DateTimePicker1, DateTimerPicker2,
    ' Button1 and Button2, Labels 1 & 2
    Option Strict On
    Option Explicit On
    Public Class Form1
      Dim dt As New DataTable
      Dim view As New DataView(dt)
      Dim rand As New Random
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' some dummy data
        With dt
          .Columns.Add("ID")
          .Columns.Add("Date")
          .Columns.Add("Value", GetType(Double))
          For i As Integer = 1 To 1000
            .Rows.Add(i, Now.AddDays(rand.Next(-20, 20)).ToString("dd MMM yyyy"), (rand.NextDouble * 999))
          Next
        End With
        With DataGridView1
          .DataSource = view
          With .Columns("Value").DefaultCellStyle
            .Format = "0.00 "
            .Alignment = DataGridViewContentAlignment.MiddleRight
          End With
        End With
          Button2.PerformClick()
      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' Filter between two 
        ' DateTimePickers using
        ' dt columnn 'Date'
        view.RowFilter = "Date >= #" & DateTimePicker1.Value.ToString("dd MMM yyyy") & "# and Date <= #" & DateTimePicker2.Value.ToString("dd MMM yyyy") & "#"
        DataGridView1.Sort(DataGridView1.Columns(1), ComponentModel.ListSortDirection.Ascending)
        Label1.Text = (DataGridView1.RowCount - 1).ToString & " Rows found"
        Label2.Text = FindTotal.ToString("0.00")
      End Sub
      Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' clear Filter
        view.RowFilter = Nothing
        Label1.Text = (DataGridView1.RowCount - 1).ToString & " Rows found"
        Label2.Text = FindTotal.ToString("0.00")
      End Sub
      Function FindTotal() As Double
        Dim tot As Double = 0
        For i As Integer = 0 To DataGridView1.RowCount - 1
          tot += Convert.ToDouble(DataGridView1.Rows(i).Cells("Value").Value)
        Next
        Return tot
      End Function
    End Class


    Regards Les, Livingston, Scotland

    • Marked as answer by Gary Simpson Friday, June 19, 2020 8:38 PM
    Thursday, June 18, 2020 8:47 PM
  • Hello,

    Assuming you want to read from the database table to perform a search rather than filtering current data and using SQL-Server, the following will do a date between date that are inclusive which is why in the parameters there is a tad manipulation of dates where the dates come from two DateTimePickers.

    The first step is to test in (in this case SSMS)

    DECLARE @StartingDate DATETIME= '20170101 00:00:00.000';
    DECLARE @EndingDate DATETIME= '20170104 23:59:59.997';
    SELECT *
    FROM DateTimeDatabase.dbo.Events
    WHERE dbo.Events.StartDate BETWEEN @StartingDate AND @EndingDate;

    Confirm you get the right results then in the form

    DataGridView1.DataSource = DataOperations.
        GetData(DateTimePicker1.Value, DateTimePicker2.Value)

    Data class with a static/shared method. Since StartDate for the first parameter has a time we take only the date and the default is beginning of day while the second parameter we mess with to the end of day.

    Imports System.Data.SqlClient
    Public Class DataOperations
        Public Shared Function GetData(startDate As DateTime, endDate As DateTime) As DataTable
            Dim dt As New DataTable
            Dim selectStatement =
                    "SELECT * FROM DateTimeDatabase.dbo.Events " &
                    "WHERE dbo.Events.StartDate BETWEEN @StartingDate AND @EndingDate;"
    
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=DateTimeDatabase;Integrated Security=True")
                Using cmd As New SqlCommand(selectStatement, connection)
                    cmd.Parameters.AddWithValue("@StartingDate", startDate.ToString("MM/dd/yyyy"))
    
                    Dim xEndDate = New DateTime(
                        endDate.Year,
                        endDate.Month,
                        endDate.Day,
                        23,
                        59,
                        59,
                        997)
    
                    cmd.Parameters.AddWithValue("@EndingDate", xEndDate)
    
                    Try
                        connection.Open()
                        dt.Load(cmd.ExecuteReader())
                    Catch ex As Exception
                        ' decide what to do
                    End Try
                End Using
            End Using
    
            Return dt
    		
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Gary Simpson Friday, June 19, 2020 8:38 PM
    Thursday, June 18, 2020 9:34 PM
    Moderator

All replies

  • Hi Gary,
    not sure but it looks like you search for something like between.
    in your case: 
    Select * From JobsComplete where [Date Paid] between DateFrom and DateTo.

    HTH
    Regards Alexander
    Thursday, June 18, 2020 8:30 PM
  • Hi

    Here is an example that may be helpful.

    ' DT DGV ROWFILTER DATE SORT 
    
    ' Form1 with DataGridView1 and
    ' DateTimePicker1, DateTimerPicker2,
    ' Button1 and Button2, Labels 1 & 2
    Option Strict On
    Option Explicit On
    Public Class Form1
      Dim dt As New DataTable
      Dim view As New DataView(dt)
      Dim rand As New Random
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' some dummy data
        With dt
          .Columns.Add("ID")
          .Columns.Add("Date")
          .Columns.Add("Value", GetType(Double))
          For i As Integer = 1 To 1000
            .Rows.Add(i, Now.AddDays(rand.Next(-20, 20)).ToString("dd MMM yyyy"), (rand.NextDouble * 999))
          Next
        End With
        With DataGridView1
          .DataSource = view
          With .Columns("Value").DefaultCellStyle
            .Format = "0.00 "
            .Alignment = DataGridViewContentAlignment.MiddleRight
          End With
        End With
          Button2.PerformClick()
      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' Filter between two 
        ' DateTimePickers using
        ' dt columnn 'Date'
        view.RowFilter = "Date >= #" & DateTimePicker1.Value.ToString("dd MMM yyyy") & "# and Date <= #" & DateTimePicker2.Value.ToString("dd MMM yyyy") & "#"
        DataGridView1.Sort(DataGridView1.Columns(1), ComponentModel.ListSortDirection.Ascending)
        Label1.Text = (DataGridView1.RowCount - 1).ToString & " Rows found"
        Label2.Text = FindTotal.ToString("0.00")
      End Sub
      Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' clear Filter
        view.RowFilter = Nothing
        Label1.Text = (DataGridView1.RowCount - 1).ToString & " Rows found"
        Label2.Text = FindTotal.ToString("0.00")
      End Sub
      Function FindTotal() As Double
        Dim tot As Double = 0
        For i As Integer = 0 To DataGridView1.RowCount - 1
          tot += Convert.ToDouble(DataGridView1.Rows(i).Cells("Value").Value)
        Next
        Return tot
      End Function
    End Class


    Regards Les, Livingston, Scotland

    • Marked as answer by Gary Simpson Friday, June 19, 2020 8:38 PM
    Thursday, June 18, 2020 8:47 PM
  • Hello,

    Assuming you want to read from the database table to perform a search rather than filtering current data and using SQL-Server, the following will do a date between date that are inclusive which is why in the parameters there is a tad manipulation of dates where the dates come from two DateTimePickers.

    The first step is to test in (in this case SSMS)

    DECLARE @StartingDate DATETIME= '20170101 00:00:00.000';
    DECLARE @EndingDate DATETIME= '20170104 23:59:59.997';
    SELECT *
    FROM DateTimeDatabase.dbo.Events
    WHERE dbo.Events.StartDate BETWEEN @StartingDate AND @EndingDate;

    Confirm you get the right results then in the form

    DataGridView1.DataSource = DataOperations.
        GetData(DateTimePicker1.Value, DateTimePicker2.Value)

    Data class with a static/shared method. Since StartDate for the first parameter has a time we take only the date and the default is beginning of day while the second parameter we mess with to the end of day.

    Imports System.Data.SqlClient
    Public Class DataOperations
        Public Shared Function GetData(startDate As DateTime, endDate As DateTime) As DataTable
            Dim dt As New DataTable
            Dim selectStatement =
                    "SELECT * FROM DateTimeDatabase.dbo.Events " &
                    "WHERE dbo.Events.StartDate BETWEEN @StartingDate AND @EndingDate;"
    
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=DateTimeDatabase;Integrated Security=True")
                Using cmd As New SqlCommand(selectStatement, connection)
                    cmd.Parameters.AddWithValue("@StartingDate", startDate.ToString("MM/dd/yyyy"))
    
                    Dim xEndDate = New DateTime(
                        endDate.Year,
                        endDate.Month,
                        endDate.Day,
                        23,
                        59,
                        59,
                        997)
    
                    cmd.Parameters.AddWithValue("@EndingDate", xEndDate)
    
                    Try
                        connection.Open()
                        dt.Load(cmd.ExecuteReader())
                    Catch ex As Exception
                        ' decide what to do
                    End Try
                End Using
            End Using
    
            Return dt
    		
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Gary Simpson Friday, June 19, 2020 8:38 PM
    Thursday, June 18, 2020 9:34 PM
    Moderator
  • Hi Alexander

    Thank you for getting back to me, I have tried your code out, But it getting from your code to entering the value of all the columns (Amount Paid) values into a Label or textbox from the selected Dates. 

    But thank you very much for your imput

    Best Regards

    Gary


    Gary Simpson

    Thursday, June 18, 2020 10:25 PM
  • Hi Les

    Thank you for getting back to me...

    This is exactly what I need, But I will have to play around with your code to accommodate my DataGridView/DataSource.

    Thanks Again Les 

    Best Regards 

    Gary


    Gary Simpson

    Thursday, June 18, 2020 10:35 PM
  • Hi

    I would assume you have the DataTable as the DataSource (or a BindingSource [better]).

    The only real difference is that you populate the DataTable from your DataBase where I populate it manually for the example. I doesn't matter where the data comes from in these simpler cases.


    Regards Les, Livingston, Scotland

    Thursday, June 18, 2020 10:48 PM
  • Hi Karen,

    Thank you for getting back to me. I tried the first part of your code to test in the SQL Server Management Studio. But I got Errors. I manipulated your code. But still got errors.

    So I best not continue as there are Errors.

    Best Regards

    Gary 


    Gary Simpson

    Thursday, June 18, 2020 11:10 PM
  • Hi Karen,

    I manipulated your code Again and changed the DateTime to just Date. And I renamed the table. And now it works fine

    Thank you Karen

    Regards Gary


    Gary Simpson


    Thursday, June 18, 2020 11:25 PM
  • Do you have a table named Events under the database JobsPaid?

    If I alter my sample as follows

    Line 1 = the database

    Line 5 = the table, is your table name Events? if so you need to refresh Intellisense under the menu Edit -> Intellisense -> Refresh Cache

    And note I don't use dbo.Events

    My first example and second example are slight variations but give the exact same results.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, June 18, 2020 11:25 PM
    Moderator
  • Hi Les

    How do I go about Populating the DataTable from My Database. The code I have is throwing me an Error saying It cannot find the column " DatePaid " I have been trying to incorporate your code into my form with no success The Code I have at the Moment ….

    Option Strict On
    Option Explicit On
    
    Public Class frmJobsPaid
    
        Private SQL As New SQLControl
    
        Dim dt As New DataTable
        Dim view As New DataView(dt)
    
        Private Sub JobsPaid_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'GarageDBDataSet.JobsPaid' table. You can move, or remove it, as needed.
            Me.JobsPaidTableAdapter.Fill(Me.GarageDBDataSet.JobsPaid)
    
            'Check if Data Rows Exist if not disable textboxes and DateTimePickers 
            SQL.RunQuery("SELECT * FROM JobsPaid")
            If SQL.RecordCount < 1 Then
                txtFilterName.Enabled = False
                txtFilterReg.Enabled = False
                DtpFrom.Enabled = False
                DtpTo.Enabled = False
    
            End If
    
            'Make Radio Button (RbDate) checked state to be checked 
            RbDates.Checked = True
            If LbTotalMonies.Text = "" Then
                LbTotalMonies.Text = "0.00"
                LbTotalMonies.Text = FormatCurrency(LbTotalMonies.Text)
            End If
            If TxtTotalMonies.Text = "" Then
                TxtTotalMonies.Text = "0.00"
                TxtTotalMonies.Text = FormatCurrency(TxtTotalMonies.Text)
            End If
    
        End Sub
    
        Function FindTotal() As Double
            Dim tot As Double = 0
            For i As Integer = 0 To dgvJobsPaid.RowCount - 1
                tot += Convert.ToDouble(dgvJobsPaid.Rows(i).Cells("Value").Value)
            Next
            Return tot
        End Function
    
        Private Sub CmdSearchDate_Click(sender As Object, e As EventArgs) Handles CmdSearchDate.Click
    
            ' Filter between two 
            ' DateTimePickers using DateTimePicker1 = DtpFrom, DateTimerPicker2 = DtpTo,
            ' dt columnn 'Date'
            view.RowFilter = "DatePaid >= #" & DtpFrom.Value.ToString("dd MMM yyyy") & "# and DatePaid <= #" & DtpTo.Value.ToString("dd MMM yyyy") & "#"
            dgvJobsPaid.Sort(dgvJobsPaid.Columns(14), ComponentModel.ListSortDirection.Ascending)
            Label4.Text = (dgvJobsPaid.RowCount - 1).ToString & " Rows found"
            LbTotalMonies.Text = FindTotal.ToString("0.00")
            Label2.Text = FormatCurrency(Label2.Text)
        End Sub

    I have tried different combinations of your code.

    Regards 

    Gary


    Gary Simpson

    Friday, June 19, 2020 1:18 PM
  • Hi

    Other knowledgable people are the ones to sort out SQL issues. I know nothing of such things.

    However, I don't see that your DGV has been given a datasource?


    Regards Les, Livingston, Scotland

    Friday, June 19, 2020 2:19 PM
  • Hi Karen

    Can you help? So far I have written code a different way to your code. Code shown Below...

     Private Sub CmdSearchDate_Click(sender As Object, e As EventArgs) Handles CmdSearchDate.Click
    
            'dgvJobsPaid.DataSource = SQL.SQLDS.Tables().GetData(DtpFrom.Value, DtpTo.Value)
    
            SQL.AddParam("@StartDate", DtpFrom.Value.ToString("dd/MM/yyyy"))
            SQL.AddParam("@EndDate", DtpTo.Value.ToString("dd/MM/yyyy"))
            SQL.AddParam("@AmountPaid", dgvJobsPaid.Columns(14))
    
            SQL.ExecQuery("SELECT * FROM JobsPaid WHERE DatePaid BETWEEN @StartDate AND @EndDate ")
    
            'For Each r As DataRow In SQL.SQLDT(0).GetColumns(14)
    
            For Each C As DataColumn In SQL.SQLDT.Rows(0).Field("AmountPaid")
    
    
            Next
     

    After I have executed the query, I want to get the values of one column (AmountPaid) into a textbox or Label, and for each row within the Dates Selected Add All the columns together Basically it is a Total of the column in the selected rows. I hope you understand 

    Kind Regards 

    Gary


    Gary Simpson

    Friday, June 19, 2020 3:47 PM
  • Hi Gary, 

    I'm glad you found with the help of Karen a solution. 

    However, I'm always triggered by questions as "What is the best"

    What would you think somebody would answer on this question.

    What is the best cutlery to eat with when you ask this to somebody from:

    1. the USA;
    2. Japan;
    3. Europe;
    4. India.

    As answer, the best does not exist it is completely depending from your knowledge and preferences.  

    Therefore give an impression about that.


    Success
    Cor

    Friday, June 19, 2020 7:12 PM
  • Hi Karen 

    I have ended up with this code...

     SQL.AddParam("@StartDate", GetDateFromString(DtpFrom.Text))
            SQL.AddParam("@EndDate", GetDateFromString(DtpTo.Text))
            SQL.AddParam("@AmountPaid", dgvJobsPaid.Columns(14).ToString)
    
            SQL.ExecQuery("SELECT Sum (AmountPaid) As AccountSum " &
                         "FROM JobsPaid " &
                         "WHERE DatePaid BETWEEN @StartDate AND @EndDate; ")
    
            If SQL.RecordCount > 0 Then
                TxtTotalMonies.Text = SQL.SQLDT.Rows(0)("AccountSum").ToString()
            End If
            If TxtTotalMonies.Text = "" Then
                TxtTotalMonies.Text = "0.00"
            End If
            TxtTotalMonies.Text = FormatCurrency(TxtTotalMonies.Text)
    

    Thank you for your input.

    Best Regards 

    Gary


    Gary Simpson

    Friday, June 19, 2020 8:37 PM
  • Hi Cor,

    Maybe I should have worded my question Differently, And Maybe left out best, I cannot change the qusetion now. Maybe I should really think about About putting the question heading in a better way before Posting.

    Cor I have found away around the code myself. To give me the result I wanted. Here it is...

     
     SQL.AddParam("@StartDate", GetDateFromString(DtpFrom.Text))
            SQL.AddParam("@EndDate", GetDateFromString(DtpTo.Text))
            SQL.AddParam("@AmountPaid", dgvJobsPaid.Columns(14).ToString)
    
            SQL.ExecQuery("SELECT Sum (AmountPaid) As AccountSum " &
                         "FROM JobsPaid " &
                         "WHERE DatePaid BETWEEN @StartDate AND @EndDate; ")
    
            If SQL.RecordCount > 0 Then
                TxtTotalMonies.Text = SQL.SQLDT.Rows(0)("AccountSum").ToString()
            End If
            If TxtTotalMonies.Text = "" Then
                TxtTotalMonies.Text = "0.00"
            End If
            TxtTotalMonies.Text = FormatCurrency(TxtTotalMonies.Text)
    

    Best Regards

    Gary


    Gary Simpson

    Friday, June 19, 2020 8:46 PM