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
203 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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