Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
210 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 2000 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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month11 days, 2 hours left to enroll

571 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