Solved

I need some help trying to design a form so that a user can pick 2 dates and find out the number of pieces received between those 2 dates.

Posted on 2006-06-12
5
200 Views
Last Modified: 2010-04-23
I have a database that a form will access for info. As the inventory is input and updated it stores the date in a database column with the datatype datetime using the GetDate() function. I would like to have a form that the user can query the database, using the datetime picker so that a user can obtain a particualr amount of pieces delivered between the 2 dates the user chooses. I'm in a bit of a quandry with this.

Thanks
0
Comment
Question by:grouchyfd
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 16891710
Assuming a SQL database and the existence of two datetimepickers (I'll refer to them as dtpStart and dtpEnd)

private ds as new dataset
private da as new sqldataadapter

...

'In form_load or other procedure
with da
   .selectcommand = new sqlcommand
   with .selectcommand
      .connection = mySQLConnection
      .commandtext = "SELECT cola, colb, colc FROM myTable WHERE coldate between @start and @end"
      .parameters.add("@start", sqldbtype.datetime)
      .parameters.add("@end", sqldbtype.datetime)
   end with
 
   .tablemappings.add("Table", "myData")
end with

...
'whenever you want to load the data
da.selectcommand.parameters("@start").value = dtpstart.value
da.selectcommand.parametes("@end").value = dtpend.value
try
   da.fill(ds)
catch ex as exception
   'handle exception
end try

'you now have a table in the dataset named "myData" with the filter records you need
0
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16891733
heres an alternative using oledb, can easy be changed to sql:

        Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='mydb.mdb'")
        Dim sq As String
        sq &= "SELECT Sum([quantity]) AS cnt "
        sq &= "FROM(inventory) "
        sq &= "WHERE (([date]>=#" & Format(Me.DateTimePicker1.Value, "d") & "# "
        sq &= "And [date]<=#" & Format(Me.DateTimePicker2.Value, "d") & "#));"

        Dim cm As New OleDb.OleDbCommand(sq)
        Dim result

        cm.CommandType = CommandType.Text
        cm.Connection = cn

        cn.Open()
        result = cm.ExecuteScalar()
        cn.Close()

        MsgBox(result.ToString)
0
 

Author Comment

by:grouchyfd
ID: 16894388
I'll have to work on these a little today. The VB method might be the one I need since I'm working on VB windows project. But I need to put 2 columns in my Database? I guess I need to have a way to capture both dates in my form. I would imagine it might be better if the user manullay enters the dates through a datetime picker as opposed to have the date put in the database automatically using the GetDAte() method. I'm having a little bit of a time with the database. Do I need to make another table just for the dates related (using ProductID as the PK-FK) to my Inventory table?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16894504
You haven't posted any details about your DB schema but I assume that if you are dealing with products and dates you have some sort of transaction table.  If you can post the schema (fields/datatypes) for the table(s) you wish to query I could give you a more precise answer.
0
 

Author Comment

by:grouchyfd
ID: 16898867
I haven't really created this particular tabel yet because I'm trying to work it out on paper first, but I was thinking something like this:

ProductID int 4
ProductName varchar
Stock int
PAR int
Received int
Date Received datetime (Default of GetDate())
Unfortunately, the row for a particular product is what my form shows and each time that product is received throughout the month, it updates the date recieved column so I only have one date to work with. Once I create the proper database, I can make the form for it. Is it possible to have a column where the date is always Jan 1 of a year and then automatically changes to the next year using the system clock? Then I could have 2 seperate dates I could query, the first of the year and the date that I choose.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now