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
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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