Needs Help With Creating a New Stored Procedure

I already know how you create a stored procedure to add information to a database or retrieve a value for one record. But I don't know how to create a stored procedure that will retrieve many records for a certain querystring value.

Here's my simple stored procedure to show one record:

CREATE PROCEDURE DisplayCity
(
@.CityID int
)
AS

SELECT City From City where CityID = @.CityID
GO

My code for displaying the City:

Sub ShowCity()

Dim strConnect As String

Dim objConnect As SqlConnection

Dim objCommand As New SqlCommand

Dim strCityID As String

Dim City As String

'Get connection string from Web.Config

strConnect = ConfigurationSettings.AppSettings("ConnectionString")

objConnect = New SqlConnection(strConnect)

objConnect.Open()

'Get incoming City ID

strCityID = request.params("CityID")

objCommand.Connection = objConnect

objCommand.CommandType = CommandType.StoredProcedure

objCommand.CommandText = "DisplayCity"

objCommand.Parameters.Add("@.CityID", CInt(strCityID))

'Display SubCategory

City = "" & objcommand.ExecuteScalar().ToString()

lblCity.Text = City

lblChosenCity.Text = City

objConnect.Close()

End Sub

Here's the code I'd like to get help with changing into a stored procedure:

Sub BindDataList()

Dim strConnect As String

Dim objConnect As New System.Data.SqlClient.SQLConnection

Dim objCommand As New System.Data.SqlClient.SQLCommand

Dim strSQL As String

Dim dtaAdvertiser As New System.Data.SqlClient.SQLDataAdapter()

Dim dtsAdvertiser As New DataSet()

Dim strCatID As String

Dim strCityID As String

Dim SubCategory As String

Dim SubCategoryID As String

Dim BusinessName As String

Dim City As String

'Get connection string from Web.Config

strConnect = ConfigurationSettings.AppSettings("ConnectionString")

objConnect = New System.Data.SqlClient.SQLConnection(strConnect)

objConnect.Open()

'Get incoming querystring values

strCatID = request.params("CatID")

strCityID = request.params("CityID")

'Start SQL statement

strSQL = "select * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City"

strSQL = strSQL & " where Advertiser.CategoryID=Categories.CategoryID"

strSQL = strSQL & " and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID"

strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID"

strSQL = strSQL & " and Advertiser.CountyID=County.CountyID"

strSQL = strSQL & " and Advertiser.CityID=City.CityID"

strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID = '" & strCatID & "'"

strSQL = strSQL & " and Advertiser.CityID = '" & strCityID & "'"

strSQL = strSQL & " and Approve=1"

strSQL = strSQL & " Order By ListingType, BusinessName,City"

'Set the Command Object properties

objCommand.Connection = objConnect

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

'Create a new DataAdapter object

dtaAdvertiser.SelectCommand = objCommand

'Get the data from the database and

'put it into a DataTable object named dttAdvertiser in the DataSet object

dtaAdvertiser.Fill(dtsAdvertiser, "dttAdvertiser")

'If no records were found in the category,

'display that message and don't bind the DataGrid

if dtsAdvertiser.Tables("dttAdvertiser").Rows.Count = 0 then

lblNoItemsFound.Visible = True

lblNoItemsFound.Text = "Sorry, no listings were found!"

else

'Set the DataSource property of the DataGrid

dtlAdvertiser.DataSource = dtsAdvertiser

'Set module level variable for page title display

BusinessName = dtsAdvertiser.Tables(0).Rows(0).Item("BusinessName")

SubCategory = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategory")

SubCategoryID = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategoryID")

City = dtsAdvertiser.Tables(0).Rows(0).Item("City")

'Bind all the controls on the page

dtlAdvertiser.DataBind()

end if

objCommand.ExecuteNonQuery()

'this is the way to close commands

objCommand.Connection.Close()

objConnect.Close()

End Sub

It's really no different. If I understand what you mean then you'd want the following stored procedure:

CREATE PROCEDURE DisplayCity
(
@.CatID Int,
@.CityID Int
)
AS

SELECT * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City
where Advertiser.CategoryID=Categories.CategoryID
and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID
and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID
and Advertiser.CountyID=County.CountyID
and Advertiser.CityID=City.CityID
and AdvertiserSubCategory.SubCategoryID = @.CatID
and Advertiser.CityID = @.CityID
and Approve=1
Order By ListingType, BusinessName,City

GO|||I actually need help with my Code for displaying the stored procedure and records. I only know how to retrieve one record using a stored procedure.|||Your original question was misleading, then :)

I'd recommend readingthese Microsoft tutorials on performing data access. There are many ways to loop through records and display data so find the one most suitable for you.|||

You would need to do a while statement. If you are using SQL Server then you can do your SQL DataReader. Just do a while loop and tell your code to keep reading until end of records and for each row add it to an array. Then you can bind thay array to a datagrid.