Adapters and commands

• • Reply

  13 years ago

  by ritesh.rajput

  Ritesh Rajput

  , India

  Joined 13 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

  Report abuse
• • Reply

  13 years ago

  by joe90

  J Alan

  , United Kingdom

  Joined 15 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
  

  Report abuse
• • Reply

  13 years ago

  by ritesh.rajput

  Ritesh Rajput

  , India

  Joined 13 years ago

  Thanks!  I will try this.

  Report abuse