Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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.

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
grouchyfd
Asked:
grouchyfd
  • 2
  • 2
1 Solution
 
Brian CroweDatabase AdministratorCommented:
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
 
vb_jonasCommented:
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
 
grouchyfdAuthor Commented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
grouchyfdAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now