Get Multiple Values From Database ASP.NET/C#

Question

I am trying to get/return multiple values from an SQL-Server database using and display them on an ASP.NET page.

I am using a stored procedure to perform the SELECT command on the Database side.

I am able to return the first value that matches the variable @PERSON but only one row is returned each time.

Any help would be much appreciated.

Database handler class

public MainSQL()
{
       _productConn = new SqlConnection();
       _productConnectionString += "data source=mssql.database.co.uk;InitialCatalog=test_data;User ID=username;Password=password";
       _productConn.ConnectionString = _productConnectionString;
}
public string GetItemName(int PersonID)
{
     string returnvalue = string.Empty;
     SqlCommand myCommand = new SqlCommand("GetItem", _productConn);
     myCommand.CommandType = CommandType.StoredProcedure;
     myCommand.Parameters.Add(new SqlParameter("@PERSON", SqlDbType.Int));
     myCommand.Parameters[0].Value = PersonID;
     _productConn.Open();
     returnvalue = (string)myCommand.ExecuteScalar();             
     _productConn.Close();
     return (string)returnvalue;
 }

Stored Procedure

USE [test_data]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [ppir].[GetItem]    
    (
    @PERSON int
    )   
AS
    /*SET NOCOUNT ON;*/
    SELECT Description FROM [Items] WHERE PersonID = @PERSON
    RETURN

return.aspx

namespace test
{
    public partial class Final_Page : System.Web.UI.Page
    {
        MainSQL GetInfo;

        protected void Page_Load(object sender, EventArgs e)
        {
            int PersonId = (int)Session["PersonID"];
            GetInfo = new MainSQL();
            string itemname = GetInfo.GetItemName(PersonId);
            ReturnItemName.Text = itemname;

        } // End Page_Load

    } // End Class 

} // End Namespace

Royi Namir · Accepted Answer · 2011-11-12 20:57:42Z

3

you should use sql datareader instead.:

ExecuteScalar returns you only the first result while reader returns you each result by loop until reader.Read()==false.

e.g. :

 DataReader data_reader= MySqlCommand.ExecuteReader( ); 
   while(data_reader.Read())  
  {
       ...
  }

edited Nov 12, 2011 at 20:57

answered Nov 12, 2011 at 14:13

Royi Namir

149k145 gold badges502 silver badges833 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Wenger · Accepted Answer · 2011-11-12 21:07:19Z

2

I Change your GetItem method like this :

public List<string> GetItemName(int PersonID)
{
   List<string> returnvalues = new List<string>();
   SqlCommand myCommand = new SqlCommand("GetItem", _productConn);
   myCommand.CommandType = CommandType.StoredProcedure;
   myCommand.Parameters.Add(new SqlParameter("@PERSON", SqlDbType.Int));
   myCommand.Parameters[0].Value = PersonID;

   _productConn.Open();
   DataReader dr = myCommand.ExecuteReader(); 
   While(dr.Read() )  
   {
       returnvalues.Add(dr[0].ToString()); 
   }         

   _productConn.Close();
   return returnvalues;
 }

edited Nov 12, 2011 at 21:07

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

answered Nov 12, 2011 at 17:59

M.Azad

3,77310 gold badges50 silver badges77 bronze badges

Comments

user1041890 · Accepted Answer · 2011-11-12 14:24:25Z

0

Does your stored procedure return one row for the id or does it return multiple rows? Ultimately you will need to loop over your results. If the stored procedure returns one record per call, then you need to loop over the ids in the aspx page. If the stored procedure returns multiple rows, then you can use and sqlDataReader instead of the ExecuteScalar call. Loop over the rows that were return and add them to some sort of a collection or list. Then return it to your page. You will still have to modify your aspx page to handle the collection, however.

answered Nov 12, 2011 at 14:24

user1041890

462 bronze badges

Comments

Daniel Mann · Accepted Answer · 2011-11-12 17:57:33Z

0

You could use a SQLDataReader, or a SQLDataAdapter to fill in a DataSet, although for webforms you might be better served by separating your data access from your page entirely by way of using an ObjectDataSource.

answered Nov 12, 2011 at 17:57

Daniel Mann

59.5k13 gold badges106 silver badges128 bronze badges

Collectives™ on Stack Overflow

Get Multiple Values From Database ASP.NET/C#

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related