Get Multiple Values From Database ASP.NET/C#
- by user1043177
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
