View source for Call Stored Procedure with Parameter

[[Category:SQL]]
[[Category:VB.NET]]

== Save Function ==

<pre class="brush:vb;">
   Private Function SaveOrgStructure() As Boolean
        If ValidateData() Then
            Dim RetVal As Boolean = True
            Dim Conn As SqlConnection = New SqlConnection(ConnectionString)
            Conn.Open()
            Dim strSPName As String
            If EditType.Value = "E" Then
                If Me.EffectiveDate.Value <> Me.EffectiveDateHidden.Value Then
                    strSPName = "isp_OrgStructureAmend"
                Else
                    strSPName = "isp_OrgStructureUpdate"
                End If
            Else
                strSPName = "isp_OrgStructureInsert"
            End If
            Using Comm As New SqlCommand(strSPName, Conn)
                Comm.CommandType = CommandType.StoredProcedure

                Comm.Parameters.Add("@Description", SqlDbType.NVarChar, 50)
                Comm.Parameters.Add("@ExternalRef", SqlDbType.NVarChar, 50)
                Comm.Parameters.Add("@EffectiveDate", SqlDbType.DateTime)
                Comm.Parameters.Add("@OrgLevelCatID", SqlDbType.Int)
                Comm.Parameters.Add("@OrgStructureID", SqlDbType.Int)
                Comm.Parameters.Add("@ParentID", SqlDbType.Int)
                Comm.Parameters.Add("@WorkAreaAtrributeISs_CSV", SqlDbType.NVarChar, 500)
                Comm.Parameters.Add("@OrgLevelID", SqlDbType.Int)

                Comm.Parameters("@Description").Value = Description.Value
                Comm.Parameters("@ExternalRef").Value = ExternalRef.Value
                Comm.Parameters("@EffectiveDate").Value = CDate(EffectiveDate.Value)
                '-----------------------------
                Comm.Parameters("@OrgLevelCatID").Value = CInt(cmbStructure.SelectedValue)

                If EditType.Value = "E" Then
                    Comm.Parameters("@OrgLevelID").Value = orgLevelID.Value
                    Comm.Parameters("@OrgStructureID").Value = StructID.Value
                    Comm.Parameters("@ParentID").Value = cachedOrgLevelId.Value
                Else
                    Dim OrgCls As New clsOrg
                    OrgCls = GetOrgIDs(orgLevelID.Value)

                    Comm.Parameters("@OrgStructureID").Value = OrgCls.OrgStructureID
                    Comm.Parameters("@ParentID").Value = cachedOrgLevelId.Value
                    Comm.Parameters("@OrgLevelID").Direction = ParameterDirection.Output
                End If

                If strSPName = "isp_OrgStructureAmend" Then
                    Comm.Parameters.Add("@EffectiveTo", SqlDbType.DateTime)
                    Comm.Parameters("@EffectiveTo").Value = DateAdd(DateInterval.Day, -1, CDate(EffectiveDate.Value))
                End If
                '-----------------------------
                Dim AttributeCSV As String = ""

                For Each tRow As TableRow In TblWorkAreaAttributes.Rows
                    For Each tControl As Label In tRow.Cells(0).Controls
                        If IsNumeric(tControl.Text) Then
                            AttributeCSV &= IIf(AttributeCSV.Length > 0, ",", "")
                            AttributeCSV &= tControl.Text & "|"
                        End If
                    Next
                    For Each tControl As DropDownList In tRow.Cells(1).Controls
                        AttributeCSV &= tControl.SelectedValue
                    Next
                Next


                Comm.Parameters("@WorkAreaAtrributeISs_CSV").Value = AttributeCSV

                Comm.ExecuteReader()
                If EditType.Value = "I" Then
                    RetVal = Comm.Parameters("@OrgLevelID").Value
                End If
            End Using
            Conn.Close()
            Return RetVal
        Else
            Return False
        End If
    End Function
</pre>

== Fill dataset  ==
<pre class="brush:vb;">

</pre>