separating new and old records based by date in database

[dualshock03]

I want to know how will i be able to show in my 3 textboxes the number of total records, number of Old records and number of New records from the database.. The problem is all the corresponding records are only Inserted in a one Table ("tblpersonaldata")

actually i already solved on showing the total number of records, so far i cant code on how to show for the old and new records..

i want to show the number of new and old records based by the date they were created,, my "tblpersonaldata" table in the database has a "datecreated" column which what i want to...

try check my attached image...

 

[JohnH]

Add two scalar queries (option "SELECT which returns a single value") to your table adapter similar to these:

SELECT COUNT(*) FROM Table1
SELECT COUNT(*) FROM Table1 WHERE logdate < @date

The first one is the default suggested by IDE actually for this type query. Name them for example CountScalar and CountOldScalar.

Then this example uses a DateTimePicker (to choose limit for old/new), a button (to call db), and a Label (to display results). Button click example code:

Dim count As Integer = CInt(Me.Table1TableAdapter.CountScalar)
Dim oldcount As Integer = CInt(Me.Table1TableAdapter.CountOldScalar(Me.DateTimePicker1.Value))
Dim newcount As Integer = count - oldcount
Me.Label1.Text = String.Format("There are {0} rows, {1} old and {2} new.", count, oldcount, newcount)

Run and select a date in picker and press button.

 

[dualshock03]

Hey JohnH i have my own version on this code, kindly check if its correct for you.. I use DataView instead.. "Dim DataRecord, datarecord2 As New DataView"

Private Sub btn_VCR_generate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_VCR_generate.Click
        con1.Open()
        Try

            comDate.CommandText = " SELECT * from tblpersonaldata left join criminal_records ON tblpersonaldata.accussed_No = criminal_records.accussed_No where dateofbirth BETWEEN '" & dfromvalue.Value & "' AND  '" & dtovalue.Value & "'"
            comDate.Connection = con1
            SearchAdap.SelectCommand = comDate
            readate = comDate.ExecuteReader

            While readate.Read
                txtsusId.Text = (readate.GetValue(readate.GetOrdinal("accussed_No")))
                txtfulname.Text = (readate.GetValue(readate.GetOrdinal("accusedname")))
                txtage.Text = (readate.GetValue(readate.GetOrdinal("agespan")))
                txtlocation.Text = (readate.GetValue(readate.GetOrdinal("addressstatus")))
                txtdatecase.Text = (readate.GetValue(readate.GetOrdinal("Date_issued")))
                LIST_ORDER.Text = (readate.GetValue(readate.GetOrdinal("ORD")))
                retrieve_Image()
            End While
            'closing datareader
            readate.Close()


            dtaSet.Clear()
            dtaSet2.Clear()

            dgrid_view.AutoGenerateColumns = False
            dteAdap = New MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * from tblpersonaldata ", con1)
            dteAdap2 = New MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * from tblpersonaldata  where dateofbirth BETWEEN '" & dfromvalue.Value & "' AND  '" & dtovalue.Value & "'", con1)

            dteAdap.Fill(dtaSet, "tblpersonaldata")
            dteAdap2.Fill(dtaSet2, "tblpersonaldata")

            DataRecord.Table = dtaSet.Tables("tblpersonaldata")
            datarecord2.Table = dtaSet2.Tables("tblpersonaldata")

            totalcnt = DataRecord.Count
            Dim oldcount As Int32 = datarecord2.Count
            Dim newcount As Int32 = totalcnt - oldcount


            dgrid_view.DataSource = dtaSet.Tables("tblpersonaldata")
           [B] lbltotal.Text = totalcnt
            lbl_old.Text = oldcount
            lbl_new.Text = newcount[/B]



        Catch SQL_err As MySql.Data.MySqlClient.MySqlException
            MsgBox("Error searching ha database" & SQL_err.Message)
        End Try

        con1.Close()

 

[JohnH]

For code like that you need to use parameters for the command object. Why are you downloading the whole database when you only need to to know a single count value? You can also for MySql set up data access mostly in IDE without writing all that code, but using the integrated Sql in VS will probably be easier for you on learning.

 

[dualshock03]

I have a confusional problem regarding my date range now

In my MYSQL database, i had a column name called "dateofbirth" with a DATE
datatype insuring a "yyyy-mm-dd" values in its data...

From my Windows application, i have the two datetimepickers with a short format each, the 1st datetimepicker(dtpfrom) is for my starting date while the second (dtpto) is for the end date for filter search..

however by the time i changed the format of my datetimepickers to custom format ("yyyy-MM-dd"), the date range code function doesnt work anymore.. and when i try to change the datatype in my database for "dateofbirth" column to VARCHAR/TEXT, it seems the code work, but having problems and a little work..

How can i filter the results properly through date range? is it going to be by year? or month? i want to separate the old records to new by means of years for example.. if its from 1950-2008, it would be posted as OLD records while the 2009 dates would be the new records.. HOW? THNKSS in advance...

 

[JohnH]

Using custom display format in DTP doesn't change the DTP.Value, it's still a Date object. Using query parameters and Date columns in DB this is also still no problem, since you report it as problem you are likely not using query parameters correctly.