April 18, 2005 at 7:09 pm
I have a form with two text boxes, txtPaymentDate and txtNumberPayments.
I want the user to be able to enter the date they want their payment to be made in the txtPaymentDate box and the number of payments in the txtNumberPayments box. I would then like to set it up where the user clicks the "Schedule Payments" button and my stored procedure will fire as many times as indicated in the txtNumbmerPayments box. I would also like it to add 1 month to the date each time the stored procedure is fired.
Would I set up my asp.net (vb) page like this?
Dim GlobalConnection As New SqlConnection(strGlobal)
Dim cmdSelect As New SqlCommand("Payments", GlobalConnection)
cmdSelect.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.Add("@Payments", txtNumberPayments.Text)
cmdSelect.Parameters.Add("@PaymentDate", txtPaymentDate.Text)
GlobalConnection.Open()
ClientInfo = cmdSelect.ExecuteReader()
Not really sure where to begin. Any help is greatly appreciated.
April 18, 2005 at 10:01 pm
you can use date add function to add one monthe to the date
DATEADD(m, 1, pubdate)
My Blog:
April 20, 2005 at 6:28 am
You likely would be better off writing the stored procedure to accept the two parameters, and having the sproc to create each record. Use a For statement and loop txtNumberPayments times. You can use the DateAdd function to add 1 month each time through the loop.
Mark
April 20, 2005 at 8:34 am
Thank you Mark. After doing a bit more research I have come to the same conclusion. I am new to this, so it will be a challenge to get this to work right.
April 20, 2005 at 10:52 am
As you are new to this, here is a tip that may help you. Take a look at how to debug stored procedures using Query Analyzer. Write and create the sproc, right click on the sproc in the QA Object Browser (the tree on the left side), then select "Debug". Enter parameters and you can then step through your procedure to see how it is working.
Mark
April 21, 2005 at 10:36 am
Here is the code I am working on to see if I can get the loop to work. Based on the information I have read this should work, but it just runs the stored procedure hundreds of times. With the way I have it set up, it should just run the stored procedure 12 times?
Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click
Dim Enter As Integer
Dim Number As Integer = 12
Dim strGlobal As String = ConfigurationSettings.AppSettings("Global")
Dim GlobalConnection As New SqlConnection(strGlobal)
GlobalConnection.Open()
Do While Number > 0
Number = 12
Dim cmdEnter As New SqlCommand("PaymentSchedule", GlobalConnection)
cmdEnter.CommandType = CommandType.StoredProcedure
cmdEnter.Parameters.Add("@UserID", 208466)
cmdEnter.Parameters.Add("@SPaymentAmount", Double.Parse(txtSpaymentAmount.Text))
cmdEnter.Parameters.Add("@SPaymentDate", txtPaymentDate.Text)
Enter = cmdEnter.ExecuteNonQuery()
Loop
End Sub
April 21, 2005 at 10:40 am
...
Do While Number > 0
Number = 12
Dim cmdEnter As New SqlCommand("PaymentSchedule", GlobalConnection)
cmdEnter.CommandType = CommandType.StoredProcedure
cmdEnter.Parameters.Add("@UserID", 208466)
cmdEnter.Parameters.Add("@SPaymentAmount", Double.Parse(txtSpaymentAmount.Text))
cmdEnter.Parameters.Add("@SPaymentDate", txtPaymentDate.Text)
Enter = cmdEnter.ExecuteNonQuery()
Loop
...
Use indentation so that you can see better:
Number = 12
Do While Number > 0
Dim cmdEnter As New SqlCommand("PaymentSchedule", GlobalConnection)
cmdEnter.CommandType = CommandType.StoredProcedure
cmdEnter.Parameters.Add("@UserID", 208466)
cmdEnter.Parameters.Add("@SPaymentAmount", Double.Parse(txtSpaymentAmount.Text))
cmdEnter.Parameters.Add("@SPaymentDate", txtPaymentDate.Text)
Enter = cmdEnter.ExecuteNonQuery()
Number = Number - 1
Loop
* Noel
April 21, 2005 at 2:14 pm
Everything works great except for my dateadd, it adds one to the value entered in the text box. I need it to enter the value from the text box in the first record and then add one to that value and then add one to that value.
I have tried storing the value from the textbox in a variable but that didn't work either. Any suggestions on where I am missing a step?
April 21, 2005 at 8:42 pm
As suggested earlier, try doing the whole thing in a stored procedure, rather than calling the stored procedure 12 times. Then you can easily track what is happening to your variable as you go.
But to be honest, you should be able to do in a single statement. If you are doing something like:
create procedure up_SchedulePayments(@PaymentDate datetime, @NumPayments int, @PayerID int)
as
begin
insert into tPayments (payerid, paymentdate)
select @PayerID, dateadd(month,num.number,@PaymentDate)
from dbo.numbers num (NOLOCK)
where num.number < @NumPayments
end
In this scenario, I'm assuming you have a table called 'numbers' which has a column called 'number', populated with integers from 0 up to as high as you think you might need. Alternatively, use a function that returns a table of integers, and use:
insert into tPayments (payerid, paymentdate)
select @PayerID, dateadd(month,num.number,@PaymentDate)
from dbo.uf_numbers(0,@NumPayments) num (NOLOCK)
Hope this helps... It'll be easier with SQL2005 when you can use 'RowNumber' for this type of thing. Then you just need to refer to a table that has at least as many rows as you need (or be prepared to do enough outer joins to make it work).
How do other people handle the scenario of wanting a list of numbers? Use a table? A function? I know I typically use a table for 1000, and then do an outer join on itself if I need a million, etc... but it's ugly to do that, and limited in use. A function lets me go as high as I need, but it's less efficient, since I need to build it each time.
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
April 22, 2005 at 9:38 am
Thank you so much Mark, Noel, and Rob! You have helped me solve my problem and helped me understand how it works.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply