How to get the returned dataset from storeprocedure

How to get the returned dataset from storeprocedure

• Oscar_Ruiz

  hi,

  see dataset.Load(...) method for this i guess it need datareader

  hope this helps

  
  
  

• Pandiarajan.net

  Hi there,
  
  In your code, I do not see any place where you define the connection or command used by the SqlDataAdapter object....You are only creating a new object.
  
  I have managed to replicate the error you are having by creating a new SqlDataAdpater but not setting the connection and command to be used by it.
  
  As far as I can see, you are only defining the connection and command for the SqlCommand object "cmdFind" which does nothing in your code (i.e. it is never used to execute commands against the DB defined by your connection)
  
  Remove all references in your code to cmdFind and work with the SqlDataAdpater instead. So your code should look like:
  
  

  Dim ds As DataSet
  Dim da As SqlDataAdapter

  da = New System.Data.SqlClient.SqlDataAdapter("POS_ItemFindDetailByItemID", cnAlfol)
  da.SelectCommand.CommandType = CommandType.StoredProcedure
  da.SelectCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ItemID", SqlDbType.BigInt, 50, "@ItemID"))
  da.SelectCommand.Parameters("@ItemID").Value = CType(value, Integer)
  

  ds = New System.Data.DataSet("dtItems")
  cnAlfol.Open()
  da.Fill(ds)
  cnAlfol.Close()
  MsgBox(ds.Tables(0).Rows.Count)

  
  Also, consider wrapping the da.FillI() call around a Try...Catch block such as:
  
  Try
      da.Fill(ds)
  Catch ex As Exception
      MsgBox(ex.Message)
  End Try
  
  I didn't in my code (naughty me) but I think I said something about it. Anyway, it's partially my fault for not putting it in.
  
  Hope that helps a bit, but sorry if it doesn't
  
  
  

• donno20

  Dim cmdFind As New SqlCommand
  Dim ds As DataSet
  Dim da As New SqlDataAdapter

  cmdFind.CommandType = CommandType.StoredProcedure
  cmdFind.Connection = cnAlfol
  cmdFind.CommandText = "POS_ItemFindDetailByItemID"

  'define parameters
  cmdFind.Parameters.Add(New SqlParameter("@ItemID", SqlDbType.BigInt, 50, "@ItemID"))

  
  'define parameters
  cmdFind.Parameters("@ItemID").Value = CType(value, Integer)

  ds = New System.Data.DataSet("dtItems")
  cnAlfol.Open()
  da.Fill(ds) <----i got the error in here
  cnAlfol.Close()
  MsgBox(ds.Tables(0).Rows.Count)

  error msg = InvalidOperationException was unhandled

  Can you help me

  
  

• Brian Kinder

  Hi there,
  
  Here's an example, you'll prolly need to polish it up (exception handling etc) but hopefully it's enough to get you started. Once the code has executed, the result set of the stored procedure should be in the Dataset object I defined in the code.
  
  Also, I have spelt out the namespaces in full but if you have the required Imports statements this is not necessary.
  
  Also, you mention that you could get more than one record back. Are you sure It should seem to me that the ItemID column would be a good primary key candidate for the Items table (which you query in your stored proc) so you'd only ever get one row back.
  
  Anyway, it's not for me to say because I have no idea what your data looks like, I can only assume.
  
  NB. I assume that your connection, cnAlfol, has already been defined.
  
  Dim da As System.Data.SqlClient.SqlDataAdapter
  Dim ds As System.Data.DataSet
  Dim param As System.Data.SqlClient.SqlParameter
  
  param = New System.Data.SqlClient.SqlParameter("@ItemID", SqlDbType.BigInt)
  param.Value = CType(value, Integer)
  
  da = New System.Data.SqlClient.SqlDataAdapter("POS_ItemFindDetailByItemID", cnAlfol)
  da.SelectCommand.CommandType = CommandType.StoredProcedure
  da.SelectCommand.Parameters.Add(param)
  
  ds = New System.Data.DataSet("Items")
  cnAlfol.Open()
  da.Fill(ds)
  cnAlfol.Close()
  
  Hope that helps a bit, but sorry if it doesn't
  
  
  
• How to get the returned dataset from storeprocedure