Running an sp from an aspx page

[Jimdeb03]

I need to run a stored procedure from an aspx page but because it takes a long time to complete, it times out at MyDataAdapter.Fill.
This sp doesn't return any recordset but the number of records affected would be nice.

Protected Sub BtnProcessDT_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnProcessDT.Click
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

MyConnection = New Global.System.Data.SqlClient.SqlConnection
MyConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("RevConnectionString").ConnectionString

MyDataAdapter = New SqlDataAdapter("uspINSERTDT", MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", SqlDbType.Int, 4))

MyDataAdapter.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output

DS = New DataSet() 'Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS, "RowCount") 'Fill the DataSet with the rows returned.


Basic syntax of the stored procedure.
It SELECTS a single record (at a time) from a table, processes the data and UPDATES four other tables.
ALTER PROCEDURE [dbo_DHT].[uspINSERTDT] (@RowCount INT OUTPUT)
AS
BEGIN
select @RowCount=@@ROWCOUNT
END

Do I need to create a DataSet if all I want to do is capture the number of records?
How do I increase the timeout?
This is strictly an intranet web application used by one person so performance isn't a big priority.

Editing the sp to reduce the amount of time to process isn't practical due to the amount of processing that occurs.

VS2005 Version 8.0.50727.42 (RTM.050727-4200)
.NET framework version 2.0.50727.3603
SQLExpress 2005 9.00.3042.00

 

[RiverGuy]

You're not returning a resultset, so don't use a DataTable or DataSet. Add a parameter to the parameters collection of your command, perform an .ExecuteNonQuery, and then look at the value of the parameter after it executes.

 

[JoeAtWork]

I would look at optimizing the stored procedure.