Passing values from a form to a sql server stored procedure

  • (5 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

64 Replies - 6765 Views - Last Post: 18 January 2011 - 09:55 AM Rate Topic: -----

#1 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 12:02 PM

I have an application that I'm building which pulls dates from a database. The start value is stored as payPeriodStart date and I add 7 days to get the end date. What I need to know is, is it possible to store those values and pass them to a Stored Procedure? Currently my stored procedures have "static" dates in them ie:

where dateadd (n, Timestamp, '12/31/1899') - ([LoggedIn]/1000)/60/1440+1 Between '10/3/2010' and '10/10/2010'

Here is my code for the form that will be passing the variables payPeriodStartDate and payPeriodEndDate to the stored procedure:

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
        Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _
                 "dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday]" & _
                  "from dbo.payroll" & _
                  " where payrollran = 'no'"
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
 
        oCmd = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxxx")
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = ssql
                oDr = .ExecuteReader()
            End With
            If oDr.Read Then
                payPeriodStartDate = oDr.GetDateTime(1)
                payPeriodEndDate = payPeriodStartDate.AddDays(7)
                Dim ButtonDialogResult As DialogResult
                ButtonDialogResult = MessageBox.Show("      The Next Payroll Start Date is: " & payPeriodStartDate.ToString() & System.Environment.NewLine & "            Through End Date: " & payPeriodEndDate.ToString())
                If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
 
                    exceptionsButton.Enabled = True
                    startpayrollButton.Enabled = False
 
                End If
            End If
            oDr.Close()
            oCmd.Connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
 


and lastly since I'm calling those variables payPeriodStartDate and payPeriodEndDate, I assume that my sql query will have to change as follows:

where dateadd (n, Timestamp, '12/31/1899') - ([LoggedIn]/1000)/60/1440+1 Between payPeriodStartDate and payPeriodEndDate

Is that correct?

Thanks

Doug

Is This A Good Question/Topic? 0
  • +

Replies To: Passing values from a form to a sql server stored procedure

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8907
  • View blog
  • Posts: 33,389
  • Joined: 12-June 08

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 12:17 PM

Sure.. using the SQLCommand you can add parameters with a value.

http://msdn.microsof...parameters.aspx

make command object
make a connection
open connection
assign connection to command object
set command's text to your procedure name.
using the command's parameter add specify your procedures incoming variable names and their values.

execute as a nonquery!

Edit:
Basically this:
http://msdn.microsof...(v=VS.100).aspx

This post has been edited by modi123_1: 12 January 2011 - 12:19 PM

Was This Post Helpful? 0
  • +
  • -

#3 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 12:56 PM

Modi,

Thank you for your response. Since I have the variables in my form on lines 19 and 20:

19 payPeriodStartDate = oDr.GetDateTime(1)
20 payPeriodEndDate = payPeriodStartDate.AddDays(7)

would i be able to pass those values to the SQL SP?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8907
  • View blog
  • Posts: 33,389
  • Joined: 12-June 08

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 01:00 PM

As long as your procedure's input variables are 'date time' you shouldn't have a problem passing in VB.NET object types of datetime.
Was This Post Helpful? 0
  • +
  • -

#5 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 01:09 PM

Modi,

Last question, and I know that this really isn't for this forum but would I then just have to modify my SQL SP's to have the value of payPeriodStartDate and payPeriodEndDate rather than the static datetime's I currently have in my query?
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8907
  • View blog
  • Posts: 33,389
  • Joined: 12-June 08

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 01:25 PM

Basically just follow the logic below.. throw them up into the parameter area... make sure to have the @ sign...


CREATE PROCEDURE dbo.pr_Foo_Insert
	-- Add the parameters for the stored procedure here
	@lValue INT
	,@dtMyDate1 DATETIME
	,@dtMyDate2 DATETIME
AS
BEGIN
SET NOCOUNT ON;
	
INSERT INTO [dbo].[my_table]
           (lValue,date1,date2)
     VALUES
           (@lValue, @dtMyDate1, @dtMyDate2)

END
GO




 Dim myConn As New SqlConnection("<connection string>")
    Dim myCommand As New SqlClient.SqlCommand()

        Try
            myConn.Open()

            myCommand.Connection = myConn
            myCommand.Parameters.AddWithValue("@lValue", _to)
            myCommand.Parameters.AddWithValue("@dtMyDate1", _period1)
            myCommand.Parameters.AddWithValue("@dtMyDate2 ", _period2)

            myCommand.CommandText = "dbo.pr_Foo_Insert"

            myCommand.ExecuteNonQuery()
            
            Catch ex As Exception
            MsgBox(ex.Message)
            End Try

Was This Post Helpful? 0
  • +
  • -

#7 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 02:08 PM

Modi,

Thank you for the code reference, I'll surely use that. My last question though is because in my SQL query currently I have the values of what VB.net will be passing to it as dates and not a variable, do I need to change my sql query to reflect that as well? In other words, right now all of my queries have dates, but when I get this to work, it won't be a date but a variable.

My query currently has this:

SELECT [EmployeeNumber],[Name],[Dateonly],[Minutes],1 AS Cat
FROM Scratchpad2
where dateonly between '10/3/2010' and '10/10/2010'
UNION ALL

and what I'm thinking it should be will be

SELECT [EmployeeNumber],[Name],[Dateonly],[Minutes],1 AS Cat
FROM Scratchpad2
where dateonly between payPeriodStartDate and payPeriodEndDate
UNION ALL
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8907
  • View blog
  • Posts: 33,389
  • Joined: 12-June 08

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 02:23 PM

Okay.. I am seeing what you are getting at. Yes, if you select statement is in a stored procedure you would probably be best to change those too... make them input variables (don't forget the @).

Oh and depending on how you want your 'to' and 'from' dates to work it would be best to send the 'to' in as <date variable>.addday(1).addsecond(-1) so you get the 11.59pm of the 'to' day... versus 12.00am of that day.
Was This Post Helpful? 0
  • +
  • -

#9 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 02:30 PM

Modi,

I'm not quite sure what you're saying about the 'to' and 'from' dates. In sql they're simply date time, and I'm pretty much pulling for a static range everytime I pull the data. For example in all of my queries I'm pulling just payPeriodStartDate and payPeriodEndDate as if it were 10/1/2010 and 10/8/2010. Does that make sense?
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8907
  • View blog
  • Posts: 33,389
  • Joined: 12-June 08

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 02:35 PM

Okay.. I was using 'to' and 'from' as in the intent of your variables. Sorry I should have used 'start' and 'end' as variable names. I was just pointing out if your date time only has a date attached then the time is assumed to be 12:00.00am of that day.

Example.. if you have the dates 10/01/2010 (start) and 10/07/2010 (stop)

By that alone sql will be inclusive of all of 10/01 and stop at 10/07 12:00.00am. If you wanted to include ALL of 10/07 then you need to make sure the time is a second right before midnight of that day... aka 11:59.59pm.

This post has been edited by modi123_1: 12 January 2011 - 02:36 PM

Was This Post Helpful? 0
  • +
  • -

#11 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 02:46 PM

So then this:
<date variable>.addday(1).addsecond(-1)
would go into the code correct? and I assume that it would go in the Create Procedure portion.
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8907
  • View blog
  • Posts: 33,389
  • Joined: 12-June 08

Re: Passing values from a form to a sql server stored procedure

Posted 12 January 2011 - 02:49 PM

No.. that would go on your VB side..


myCommand.Parameters.AddWithValue("@dtMyDate1", _period1.AddDays(1).AddSeconds(-1))

This post has been edited by modi123_1: 12 January 2011 - 02:49 PM

Was This Post Helpful? 0
  • +
  • -

#13 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Passing values from a form to a sql server stored procedure

Posted 13 January 2011 - 08:26 AM

Modi,

Thank you for your help, I'll be trying this out over the next few days and will write back if I have any issues.
Was This Post Helpful? 0
  • +
  • -

#14 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Passing values from a form to a sql server stored procedure

Posted 13 January 2011 - 09:17 AM

Modi,

For the Create Procedure section, do I need to make a Sub with that? I'm still learning Visual Basic and just want to make sure that I'm writing the best code that I can.

Thank you

Doug
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8907
  • View blog
  • Posts: 33,389
  • Joined: 12-June 08

Re: Passing values from a form to a sql server stored procedure

Posted 13 January 2011 - 09:51 AM

That create procedure part is for your SQL... you said you were making a procedure on the database to call from you code, right? Not that you were running a sql statement from your code?
Was This Post Helpful? 0
  • +
  • -

  • (5 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »