Adapters and commands

asp.net Bombay, India
  • 11 years ago

    I want to use three stored procedures with a dataset but don't know how to set it up. Can someone assist and tell me what I'm doing wrong. 

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

    Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)

    Dim cmdALL As New Data.SqlClient.SqlCommand

    Dim cmdTopic As New Data.SqlClient.SqlCommand 'command for second stored procedure

    Dim cmdMedia As New Data.SqlClient.SqlCommand ' 3rd stored procedure

     

    With cmdALL

    .CommandType = Data.CommandType.StoredProcedure

    .CommandText = "GetAllTopics"

    If DropDownList2.SelectedValue = "-1" Then

    .Parameters.AddWithValue("@Type", DropDownList2.SelectedValue)

    End If

    .Connection = conn

    End With

    Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdALL)With cmdTopic

    .CommandType = Data.CommandType.StoredProcedure

    .CommandText = "GetByTopic"

    If DropDownList3.SelectedIndex > 0 Then

    .Parameters.AddWithValue("@classificationid", Integer.Parse(DropDownList2.SelectedValue))

    End If

    .Connection = conn

    End With

    Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdTopic)With cmdMedia

    .CommandType = Data.CommandType.StoredProcedure

    .CommandText = "GetByMedia"

    If DropDownList3.SelectedValue > 0 Then

    .Parameters.AddWithValue("@Mediaid", DropDownList3.SelectedValue)

    End If

    .Connection = conn

    End With

    Dim adapter As New Data.SqlClient.SqlDataAdapter

     

    Try

    conn.Open()

    Dim ds As New Data.DataSet

    adapter.Fill(ds)

    GridView1.DataSource = ds

    GridView1.DataBind()

    Finally

    conn.Close()

    End Try

  • 11 years ago

    [quote]I want to use three stored procedures with a dataset but don't know how to set it up[/quote]

    Use a data adapter to load each stored procedure's data into a datatable in the dataset. Just change the command that that the data adapter uses in each case.

    You don't really need to create 3 separate commands as you have done (above).  

    Try something like this (untested):

    Dim ds as DataSet = New DataSet
    ds.Tables.Add(new DataTable("Table1"))
    ds.Tables.Add(new DataTable("Table2"))
    ds.Tables.Add(new DataTable("Table3"))

    Dim adapter As New Data.SqlClient.SqlDataAdapter
    Dim cmd As Data.SqlClient.SqlCommand

    ' first sp
    Set cmd = New Data.SqlClient.SqlCommand
    With cmd
        .CommandType = Data.CommandType.StoredProcedure
        .CommandText = "GetAllTopics"
        .Connection = conn
        If DropDownList2.SelectedValue = "-1" Then
            .Parameters.AddWithValue("@Type", DropDownList2.SelectedValue)   
        End If   
    End With

    adapter.SelectCommand = cmd
    adapter.Fill(ds.Tables("Table1"))

    ' second sp
    Set cmd = New Data.SqlClient.SqlCommand
    With cmd
        .CommandType = Data.CommandType.StoredProcedure
        .CommandText = "GetByTopic"
        .Connection = conn
        If DropDownList3.SelectedIndex > 0 Then
            .Parameters.AddWithValue("@classificationid", Integer.Parse(DropDownList2.SelectedValue))   
        End If
    End With

    adapter.SelectCommand = cmd
    adapter.Fill(ds.Tables("Table2"))

    ' third sp
    Set cmd = New Data.SqlClient.SqlCommand
    With cmd
        .CommandType = Data.CommandType.StoredProcedure
        .CommandText = "GetByMedia"
        .Connection = conn
        If DropDownList3.SelectedValue > 0 Then
            .Parameters.AddWithValue("@Mediaid", DropDownList3.SelectedValue)
        End If
    End With

    adapter.SelectCommand = cmd
    adapter.Fill(ds.Tables("Table3"))   

    This should give you a data set with 3 sources of data, which you can then use in whatever way you want.

    Joe

  • 11 years ago

    Thanks!  I will try this.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Beware of bugs in the above code; I have only proved it correct, not tried it.” - Donald Knuth