Passing values from a VB app to a stored procedure

[dougancil]

This may not be the place to ask this, if not, you can move this post to the SQL forum but 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'

and I'd like those values to be

Between payPeriodStartD ate and payPeriodEndDat e.

Any help would be greatly appreciated.

Thank you

[Joseph Martell]

Yes, it is possible to create a stored procedure that takes parameters from a VB app.

Your stored procedure needs to be defined to take the parameters that you need. Also, your code to execute the stored procedure needs to set the parameters.

If you are using SQL Server, the correct syntax to create a stored procedure can be found here. I am not well versed in creating stored procedures though, so that part of this question will be better resolved in the SQL forum.

As far as passing a parameter to a stored procedure in VB, the code looks something like this:

        Dim payPeriodStartDate As Date = Date.Now.Date
        Dim payPeriodEndDate As Date = payPeriodStartDate.AddDays(14)

        Dim sqlConn As New SqlConnection("")
        Dim sqlCmd As New SqlCommand("StoredProcedureName")
        Dim param As SqlParameter

        sqlCmd.Connection = sqlConn
        sqlCmd.CommandType = CommandType.StoredProcedure

        param = sqlCmd.CreateParameter()
        param.ParameterName = "@payPeriodStartDate"
        param.Value = payPeriodStartDate
        param.SqlDbType = SqlDbType.Date
        sqlCmd.Parameters.Add(param)

        param = sqlCmd.CreateParameter()
        param.ParameterName = "@payPeriodEndDate"
        param.Value = payPeriodEndDate
        param.SqlDbType = SqlDbType.Date
        sqlCmd.Parameters.Add(param)

        Dim dataReader As SqlDataReader = Nothing

        Try
            dataReader = sqlCmd.ExecuteReader()
            'access your data
        Catch ex As Exception
            'whatever your exception handling is
        End Try

Forgive me if this isn't 100% accurate. Its been a while sense I wrote this code in a production environment. I just quickly threw this together. The idea is correct though. Notice that the SqlCommand text is the stored procedure name and the SqlCommand object actually has a property that is set to explicitly state what type of command you are executing.

[dougancil]

Joseph,

Thank you for your response. I was looking through my app and discovered that I already had parameters set for the values that I need to pass to my sql stored procedure. My code looks like this:

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

    End Sub

So I have two questions in regards to this. Should I declare the values outside of this sub? Secondly, since I'm calling the parameters payPeriodStartD ate and payPeriodEndDat e, I'm assuming that in my sql SP, I'll just need to write my query like this:

where dateadd (n, Timestamp, '12/31/1899') - ([LoggedIn]/1000)/60/1440+1 Between payPeriodStartD ate and payPeriodEndDat e

Is that correct?

Thanks

Doug

[Joseph Martell]

I'm not sure what you mean by "should I declare the values outside of my sub". Could you elaborate?

Secondly, the a stored procedure is something that is contained completely on the SQL Server. The variable names that you decide to use in your program have no relationship to the parameters of a stored procedure.

[dougancil]

joseph,

What I'm asking for is because I'm declaring values that need to be passed on each form, I am thinking that I need to either declare "global" variables or something within a module.

I am aware of the fact that the SP is contained on the SQL server, but my variables (in this case payrollstartdat e, payrollenddate) are pulled from the sql server at the start of this process and I'll need to save them as values to pass to my stored procedures. The way that I have my SP set up is that @payrollstartda te and @payrollenddate replace payrollstartdat e, payrollenddate simply as a cleaner naming convention. I hope that makes sense and I can post my code if necessary for my sp's.

[dougancil]

I think I solved this problem. I just created a module:

Module Module1
Public payperiodstartd ate As Date
Public payperiodenddat e As Date
End Module

That seems to have fixed the issue.