|
I am using vb express and MsAccess as database.
I am trying to write a query for the calculation of Stock.
My tables are as under:
Data in PurchaseTable
PId PDate ItemId Description Price Quantity Amount
1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00
2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00
3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00
4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00
Data in Sales Table is as under:
SId SDate ItemId Description Price Quantity Amount
1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40
2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10
3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70
4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70
I want a query for stockcalculation to show data as under:
PDate,ItemId, QuantityPurchased, QuantitySold, Balance(QuantityPurchased - QuantitySold)
Please advise how I can get the above information through a query on the above two tables to be displayed in a Listview.
|
|
|
|
|
You are in the wrong forum, but try this link: http://www.w3schools.com/sql/default.asp[^]
There you will find a free tutorial that will teach you how to do this.
My advice is free, and you may get what you paid for.
|
|
|
|
|
General Database would have been a better forum.
sazd1 wrote: I want a query for stockcalculation
What have you tried?
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
thanks for your reply I tried something like this:
Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description"
But it did not give the desired results. it is not making the correct calculations.
|
|
|
|
|
To do this you have to create another table with following fields for store remaining stock.
ItemID Description Price Quantity
When you make a transaction update the Stock table. When generating the listview ignore PDate field. You can get QuantityPurchased and QuantitySold fields from Purchase table and Sales table. You can get Balance field from Stock table
|
|
|
|
|
I tried something like this to have balance of stock at any date or interval between two dates
Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description"
If con.State = ConnectionState.Closed Then con.Open()
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@START", OleDb.OleDbType.Date).Value = TextBox1.Text
cmd.Parameters.AddWithValue("@END", OleDb.OleDbType.Date).Value = TextBox2.Text
Dim dr As OleDb.OleDbDataReader
If con.State = ConnectionState.Closed Then con.Open()
dr = cmd.ExecuteReader
If Not dr.HasRows Then
MessageBox.Show("No Records Found for Date: " & TextBox1.Text)
Else
MessageBox.Show("Record found for Date: " & TextBox1.Text)
ListView1.Items.Clear()
ListView1.ForeColor = Color.DarkRed
ListView1.GridLines = True
While dr.Read
Dim ls As New ListViewItem(dr.Item("ItemId").ToString())
ls.SubItems.Add(dr.Item("Description").ToString())
ls.SubItems.Add(dr.Item("QuantityPurchased").ToStr ing())
ls.SubItems.Add(dr.Item("QuantitySold").ToString() )
ls.SubItems.Add(dr.Item("Balance").ToString())
ListView1.Items.Add(ls)
End While
End If
But i could not get the desired results. Please advise what i am doing wrong with this.
The problem i am having is as under:
Data in PurchaseTable
PId PDate ItemId Description Price Quantity Amount
1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00
2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00
3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00
4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00
Data in Sales Table is as under:
SId SDate ItemId Description Price Quantity Amount
1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40
2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10
3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70
4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70
The result of query with date range of 28-08-2009 to 29-08-2009 and also with the date range of 28-08-2009 to 31-08-2009 is displayed as under:
Coca Cola Normal 30 6 24
Coca Cola Zero 30 10 20
whereas actually for date range of 28-08-2009 to 29-08-2009 the result should be as under:
Coca Cola Normal 15 0 15
Coca Cola Zero 15 0 15
And with the date range of 28-08-2009 to 31-08-2009 the result should be as under:
Coca Cola Normal 15 3 12
Coca Cola Zero 15 5 10
Pleae advise what i am doing wrong with the query.
Thanks again for your guidance.
|
|
|
|
|
Hello Everyone
I need some help i have been googleing this all night i think it is pretty simple to do just cant find any info
all i am trying to do is
have a button click on a set time
example
have my button1 click at 5:34 PM every day
how can i do that ??
Thanks in advance
Bob
|
|
|
|
|
Assuming that the program is running at the time, then my suggestion would be to have a timer that does a PerformClick on the button at the appropriate.
The timer would need to be checking every minute to see if the time has been reached. You would also need to disable it for the period that the process under the button is running so that it does not (potentially) cause any problems with firing again inside the time. Setting the interval to 65000 should stop that but better to disable the time during the execution of the target code.
|
|
|
|
|
It's a shame that the .NET Timer class doesn't let you schedule an event for a specified time. It seems like such an obvious thing and it's been in other platforms like Java for years, so you wonder why they've never added it to .NET. It can't be difficult to do from a technical viewpoint.
The previous post is a perfectly good solution but you could also have a look at Quartz.NET[^] for something more sophisticated or this article[^] if you prefer a "roll your own" solution.
If you are building an application that needs any sort of scheduling service beyond the basics, I would recommend something like this, but I suspect that might be overkill for your requirements.
|
|
|
|
|
Hi Bob,
Besides what the other two already told you, I strongly recommend you to not fire the button click event with the timer tick event.
Instead, it is much better practice, to move the code from the button's click event to its own Sub, and then call this sub both in the button's click event and in the timer's tick event.
So instead of this:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MsgBox("Jiihuu!!!")
End Sub
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Button1.PerformClick()
End Sub
it's better to build your app like this:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MyMessageBox()
End Sub
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
MyMessageBox()
End Sub
Private Sub MyMessageBox()
MsgBox("Jiihuu!!!")
End Sub
This will make future development a lot easier, for example if at some point you want the code to execute in different manners:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MyMessageBox(" Executed by Button1")
End Sub
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
MyMessageBox(" Executed by Timer1 at " & Now.ToShortTimeString)
End Sub
Private Sub MyMessageBox(Byval ExtraMessage As String)
MsgBox("Jiihuu!!!" & ExtraMessage)
End Sub
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hello Johan
Thanks for your help this looks good but whare would i put the spacifique time to fier my button ??
and my app will be running 24 7
Thanks
|
|
|
|
|
Hello Bob,
So take your case: you want the code to be executed once a day at a specific time (was it 5:34 pm ?).
Let's take my example code again:
Dim TheButtonWasClickedToday As Boolean = False
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MyMessageBox(" Executed by Button1")
End Sub
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
If TheButtonWasClickedToday = False Then
If Now.Hour = 17 Then
Select Case Now.Minute
Case 34
MyMessageBox(" Executed exactly on time by Timer1 at " & Now.ToShortTimeString)
TheButtonWasClickedToday = True
Case Is > 40 'a 6 minute safety catch'
TheButtonWasClickedToday = False
Case Is > 34
If TheButtonWasClickedToday = False Then
MyMessageBox(" Executed too late by Timer1 at " & Now.ToShortTimeString)
TheButtonWasClickedToday = True
End If
End Select
End If
End If
End Sub
Private Sub MyMessageBox(Byval ExtraMessage As String)
MsgBox("Jiihuu!!!" & ExtraMessage)
End Sub
For it to work you would have to set the timer's interval to 1 minute (60000 milliseconds).
The problem with the timer approach, is that you are dependent on the interval. If for any reason (for example the pc freezes) the tick event is a few seconds late, your code will not be run until the next day.
So I have added the TheButtonWasClickedToday boolean to check if the code was run. In this case I have given it 6 minutes time, but you can make that smaller or larger.
Also, I am not entirely sure if checking for the hour will work with 17, as you apparently use the am/pm system. You may have to investigate that.
As for Ashfield's answer to you, you say your app will be running 24/7, but is that just to make sure that the code will be executed on time, or because the rest of the app will be doing things the rest of the time?
My advice is free, and you may get what you paid for.
|
|
|
|
|
Thanks i will try that
and the app it doing thing 24 7 reson for this timer is i want to log some data into a excell file
so every hour it will record data to a set cell
|
|
|
|
|
Hello johan
Thanks for the help it is exacly what i wanted to do your the best
I got this to work but it only runs the first set 22:23 and it do notintg for 22:24
I have to have it run once ever hour somting difrent so i would need 24 lines of code
Thanks
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Timer1.Interval = 60000
TextBox1.Text = Format(Now, "M")
If Now.Hour = 22 Then
Select Case Now.Minute
Case 23
MyMessageBox(" Executed exactly on time by Timer1 at " & Now.ToShortTimeString)
End Select
End If
If Now.Hour = 22 Then
Select Case Now.Minute
Case 24
MyMessageBox(" Executed exactly on time by Timer1 at " & Now.ToShortTimeString)
End Select
End If
End Sub
I got this to work but it only runs the first set 22:23 and it do notintg for 22:24
Thanks agine
Bob
modified on Thursday, September 3, 2009 10:27 PM
|
|
|
|
|
Hi Bob,
You need to move Timer1.Interval = 60000 to where you start the timer.
So for example:
Private Sub Form_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Timer1.Enabled = True
Timer1.Interval = 60000 'we should get a tick every 1 minute'
'if you get a tick more often, you need to increase this number'
'if you get a tick less often, you need to decrease this number'
Timer1.Start
End Sub
For your code to run 24 times (once an hour) you need to turn the If and Select Case statements around:
Select Case Now.Hour
Case 0
If Now.Minute = 23 Then MyMessageBox(" Executed exactly on time by Timer1 at " & Now.ToShortTimeString)
Case 1
If Now.Minute = 23 Then MyMessageBox(" Executed exactly on time by Timer1 at " & Now.ToShortTimeString)
Case 2
If Now.Minute = 23 Then MyMessageBox(" Executed exactly on time by Timer1 at " & Now.ToShortTimeString)
'Etc.'
End Select
Just write a case for each hour, and change the minutes that are checked, according to your needs. So yes, you will need to write 24 lines of code 
Although I must agree with Ashfield, that what you are (apparently) trying do, really would work much more reliably with a console application.
If you have never written one, don't worry, it is pretty much the same as the application you have written now, and you wouldn't need to use a timer.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hay Thanks Thare
i will try this and see what hapens
2 thing why did you put the mins at 23 ??
and i have to put end if after each msg box right??
Thanks
Oh i just think i found somting a little easyer that might work but the msgbox comes up 9 times how can i have it just come up once ?? and have it come up once agine the next day ??
If TimeBox.Text = "10:44:01 AM" Then
MsgBox("Timer test", MsgBoxStyle.OkOnly + vbCritical, "Error")
End If
Thanks agine you are grate help
i wish every one was like you
modified on Friday, September 4, 2009 10:52 AM
|
|
|
|
|
Hi Bob,
Bob Beaubien wrote: why did you put the mins at 23 ?
I understood from your question that you need your application to run once an hour but at different minutes. So for example at 0:10, 1:23, 2:15, etc. Ofcourse I don't know what those minutes are, so I just put 23 as an example. That is why I said that you may have to change these minutes to fit your needs.
Perhaps I misunderstood what it is exactly that you are trying to achieve.
Bob Beaubien wrote: i have to put end if after each msg box right?
Actually no. The If statement allows for several different syntaxes:
If i = 1 Then MsgBox("Papa fume une pipe")
If i = 1 Then MsgBox("Papa fume une pipe") Else MsgBox("Mama fume le pipe de papa")
If i = 1 Then
MsgBox("Papa fume une pipe")
Else : MsgBox("Mama fume le pipe de papa")
End If
If i = 1 Then
MsgBox("Papa fume une pipe")
Else
MsgBox("Mama fume le pipe de papa")
End If
Just go ahead and give it a try.
Bob Beaubien wrote: If TimeBox.Text = "10:44:01 AM" Then
MsgBox("Timer test", MsgBoxStyle.OkOnly + vbCritical, "Error")
End If
No, no, no, bad, wrong, please don't !!! You are right, this might work, but it is much more likely to not work half the time. If you use your application on a computer that does not have the exact same regional settings, it won't work. If the tick event fires every millisecond or 100th or 10th of a second this code will do what you described: it will fire 999, 99, or 9 times in a row. If you set the interval to 1 second it will more than likely not fire at all, many times. Do yourself a big favor, and delete that code, and forget you ever came up with the idea.
Please stick to the idea I gave you. It will work on any machine, and it will do what it is supposed to do, not just maybe!
In other words, your idea may seem simple, but it is unsafe coding. It is bad, because if you teach yourself to code like that, it will come back and bite you in the ass, many, many, many times in the future.
My advice is free, and you may get what you paid for.
|
|
|
|
|
The time, depending on whether it is always fixed or may change can either be in the tick event (if the value is never going to change), or as a user setting (if the user can change it), or as some other module level variable which is set on form load etc etc.
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Dim tmeFireAt as Date = "01:30"
If TimeValue(Now) = tmeFireAt Then
'Or whatever checking you feel you need to see if the time has been reached Hour() Minute() Second() etc etc
Timer1.Enabled = False
'
'do stuff
'
Timer1.Enabled = True
End If
End Sub
Private tmeFireAt as Date = "01:30"
'
'
'
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
If TimeValue(Now) = m_tmeFireAt Then
'Or whatever checking you feel you need to see if the time has been reached Hour() Minute() Second() etc etc
Timer1.Enabled = False
'
'do stuff
'
Timer1.Enabled = True
End If
End Sub
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
If TimeValue(Now) = My.Settings.FiringTime Then
'Or whatever checking you feel you need to see if the time has been reached Hour() Minute() Second() etc etc
Timer1.Enabled = False
'
'do stuff
'
Timer1.Enabled = True
End If
End Sub
|
|
|
|
|
Hello Johan Hakkesteegt
I tryed to pm you but i can figuer how i can do that but anyways
i just wated to thanks you and everyone else that help me out
and having lost of patients i am not the best at vb but i am lerning
more and more every day
with your help i got what i wanted to do working perfect Thanks
I will have to get you a beer 
Thanks
Bob
|
|
|
|
|
Firstly, are you sure your app will be running at that precise time? If not, why not just create a console app and schedule it with the windows scheduler - much more reliable I would have thought.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
MY app will be running 24 7
|
|
|
|
|
In which case, just set the timer to the difference between the current time and the required time (remembering to use the date as well - I forgot once  )
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi all, how can i insert excel worksheet in vb.net form that can be applied excel formula in the worksheet.
Thanks in advance..
Regards
|
|
|
|
|
Hello Thare
I am new at this but i am also working with excel and found this site that was very usfull hope it is usfull for you too
http://vb.net-informations.com/excel-2007/vb.net_excel_2007_create_file.htm
look at the bottom of the page thare is all kinds of Related Contents
|
|
|
|
|
I am assuming what you are saying is that you want to disply an Excel worksheet in a form. You could link to Excel using Excel as a COM server (objExcel as Excel.Application) or you could get an excellent control like Spreadsheet Gear which makes what I am assuming you want to do very easy.
If you search the message boards here with 'using excel in vb' you will get 31 pages of results. You might find something useful there!
Alternatively you might find this useful: ExcelSpreadsheet.aspx
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
