passing parameter to Store Procedure in SqlDataAdapter

[Texpert]

I am working on this screen, where top part of the screen is a browse/edit/add panel for table called "Package"

And depending on the Package_ID (@@Identity) I am filling a datagrid in the bottom part of the same screen which has data from various other tables , like "Components" , "formats" etc.

I have written a store procedure to get data for the datagrid, and the store procedure gets an input parameter for Package_Id.

I am plugging this existing store procedure to the SqlDataAdapter in the page. But I am not quite sure how can I pass a parameter to the Store procedure which is in the SqlDataAdapter ? when I tried to use "Property" window of SqlDataAdapter and defined parameter value to be PKG_ID.Text (from the above part of the screen), it gives me error, parameter not passed.

Also, since the Datagrid gets data from various tables, it does not generate Insert, Update and Delete statements while configuring SqlDataAdapter

Should I go for SQLCommand class and put the SQL statements inside the code behind to fill the datagrid and not use the SqlDataAdapter ?
if I do so then how to fill a datagrid ?

Can you suggest a better approach to code to achieve what I am trying to do here ? :sad: :confused:

create store procedure script

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SelectComponentDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SelectComponentDetails]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE Procedure sp_SelectComponentDetails
( @PX_Package_Id char)
AS

DECLARE @myERROR int -- Local @@ERROR
       , @myRowCount int -- Local @@ROWCOUNT

BEGIN
SELECT     Component.ComponentID, Component.FormatID, Component.PackageID, Component.Description, Component.Comp_SKU_Flag, 
                      Component.MixedOrNon, Component.MixedByWho, Format.FormatType, Package.PackageName
FROM         Component INNER JOIN
                      Format ON Component.FormatID = Format.FormatID INNER JOIN
                      Package ON Component.PackageID = Package.PackageId

WHERE     (Component.PackageID = @PX_Package_Id)

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR

END
RETURN

HANDLE_ERROR:
    --ROLLBACK TRAN
    RETURN @myERROR

GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

part of vb code

Public Sub datasave(ByVal DataSet As DataSet)
        If DataExists() Then
            ViewState.Item("_Data") = DataSet
        Else
            ViewState.Add("_Data", DataSet)
        End If
    End Sub
    Public Function DataRetrieve() As DataSet
        Return CType(ViewState.Item("_Data"), DataSet)
    End Function
    Public Function DataExists() As Boolean
        If Not ViewState.Item("_Data") Is Nothing Then Return True
    End Function

Private Sub Add_Comp_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Add_Comp_btn.Click
        If DataExists() = False Then Exit Sub
        DsComponents1 = DataRetrieve()
        Dim rowNew As System.Data.DataRow = DsComponents1.Tables(0).NewRow
        DsComponents1.Tables(0).Rows.Add(rowNew)
        CompDataGrid.EditItemIndex = CompDataGrid.Items.Count
        CompDataGrid.DataSource = DsComponents1
        CompDataGrid.DataBind()
        datasave(DsComponents1)
    End Sub

    Private Sub Delete_Comp_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete_Comp_Btn.Click

        Dim objItem As DataGridItem
        Delete_Comp_Btn.Attributes("onclick") = "return getconfirm();"

        If DataExists() = False Then Exit Sub
        If CompDataGrid.SelectedIndex = -1 Then Exit Sub
        DsComponents1 = DataRetrieve()
        DsComponents1.Tables(0).Rows(CompDataGrid.SelectedIndex).Delete()
        CompDataGrid.EditItemIndex = -1
        CompDataGrid.SelectedIndex = -1
        CompDataGrid.DataSource = DsComponents1
        CompDataGrid.DataBind()
        datasave(DsComponents1)

    End Sub

    Private Sub Edit_Comp_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Edit_Comp_Btn.Click
        If DataExists() = False Then Exit Sub
        If CompDataGrid.SelectedIndex = -1 Then Exit Sub
        Dim DsComponents1 As DataSet = DataRetrieve()
        CompDataGrid.DataSource = DsComponents1
        CompDataGrid.EditItemIndex = CompDataGrid.SelectedIndex
        CompDataGrid.DataBind()
    End Sub

    Private Sub Cancel_Comp_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel_Comp_Btn.Click
        If DataExists() = False Then Exit Sub
        CompDataGrid.SelectedIndex = -1
        CompDataGrid.EditItemIndex = -1
        CompDataGrid.DataSource = DataRetrieve()
        CompDataGrid.DataBind()
    End Sub

    Private Sub OK_Comp_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Comp_Btn.Click
        If DataExists() = False Then Exit Sub
        If CompDataGrid.EditItemIndex = -1 Then Exit Sub
        Dim intCount As Integer
        Dim dscomponents As DataSet = DataRetrieve()
        With CompDataGrid
            For intCount = 1 To .Items(.EditItemIndex).Cells.Count
                If intCount = .Items(.EditItemIndex).Cells.Count Then Exit For
                If .Items(.EditItemIndex).Cells(intCount).Controls.Count Then
                    If TypeOf (.Items(.EditItemIndex).Cells(intCount). _
                    Controls(0)) Is TextBox Then
                        Dim strValue As String = CType(.Items(.EditItemIndex). _
                        Cells(intCount).Controls(0), TextBox).Text
                        If strValue <> "" Then
                            dscomponents.Tables(0).Rows(.EditItemIndex).Item( _
                            intCount - 1) = strValue
                        Else
                            dscomponents.Tables(0).Rows(.EditItemIndex).Item( _
                            intCount - 1) = System.DBNull.Value
                        End If
                    End If
                End If
            Next
            .SelectedIndex = -1
            .EditItemIndex = -1
            datasave(dscomponents)
            .DataSource = dscomponents
            .DataBind()
        End With
        SqlDataAdapter1.Update(DataRetrieve)
        CompDataGrid.DataSource = DataRetrieve()
        CompDataGrid.DataBind()
    End Sub

    Private Sub Add_Components_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Add_Components.Click
        'If Not IsPostBack Then
        SqlDataAdapter1.Fill(DsComponents1)
        CompDataGrid.DataSource = DsComponents1
        CompDataGrid.DataBind()
        datasave(DsComponents1)
        'End If
    End Sub

thanks in advance.
Tejoo.