SQL Timeout - Microsoft IIS Web Server and ASP.NET forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

SQL Timeout

andrewsmd

9:43 pm on Sep 8, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

I have a file uploader. It let's the user upload an excel file. Once the file is uploaded, I call a stored procedure which does some things to the database. It also, calls another stored procedure that opens the excel file using the openrowset command and inserts the data into a temp table where it can be read by another stored procedure and inserted. The problem I am having is that all of this is taking too long and I am getting this error

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) at System.Web.UI.WebControls.SqlDataSource.Insert() at devOrthman.Product.SubmitDrawingButton_Click(Object sender, EventArgs e)

I have added a command timeout to my connection string and changed it in IIS and I still get this error. Does anyone have any idea how I can make this wait longer? I can post the code of my page if need be. Thanks,

Ocean10000

1:56 pm on Sep 9, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

FYI, the command object does not inherit the timeout set in the connection object. You have to set the commandtimeout [msdn.microsoft.com] in the command object. Setting it to zero, will make it wait indefinatly.

andrewsmd

2:32 pm on Sep 9, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

So I have a new question for you then. I was going to try and do that in the vb like this
Me.drawingsSqlDataSource.commandtimeout = 0
but I get an error saying commandtimeout is not a member of system.web.ui.webcontrols.sqldatasource.
I can tell you that the sql datasource is in the asp page like this.

<asp:SqlDataSource ID="drawingsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:orthmanConnectionString %>"
SelectCommand="getDrawings" SelectCommandType="StoredProcedure" InsertCommand="addDrawing"
InsertCommandType="StoredProcedure" DeleteCommandType="StoredProcedure" DeleteCommand="deleteDrawing"
OnDeleted="On_Record_Deleted" >
<SelectParameters>
<asp:Parameter Name="ProductId" Type="Int32" DefaultValue="0" />
</SelectParameters>
<InsertParameters>
<asp:ControlParameter ControlID="DrawingTextBox" Name="Name" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="DrawingOverviewTextBox" Name="OverviewText" PropertyName="Text"
Type="String" />
<asp:Parameter DefaultValue="0" Name="productID" Type="Int16" />
<asp:Parameter Name="PathToPDF" Type="String" />
<asp:Parameter Name="sourceFile" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

Do I need to set it in the asp side?

[edited by: marcel at 7:23 pm (utc) on Sep. 9, 2009]
[edit reason] fixed accidental smiley [/edit]

marcel

2:55 pm on Sep 9, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Add the appropriate Event to your SQLDataSource with the following code. For example the 'Selecting' event:

Protected Sub drawingsSqlDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles drawingsSqlDataSource.Selecting
e.Command.CommandTimeout = 0
End Sub

andrewsmd

3:23 pm on Sep 9, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

So I added that and it compiled but I am still getting the time out error. Any other ideas?

Ocean10000

4:13 pm on Sep 9, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

There are 4 command objects in the SqlDataSource. Did you set it for the correct one?

andrewsmd

4:40 pm on Sep 9, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

I'm not for sure. I added it like so
Private Sub drawingsSqlDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles drawingsSqlDataSource.Selecting
e.Command.CommandTimeout = 0
End Sub
However, it needs to be on the insert command but I don't see any documentation for something like
e.command.insertcommand.commandtimeout
or e.insertcommand.commandtimeout
Did I do it correctly? Thanks,

marcel

6:55 pm on Sep 9, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

In Visual Studio, select the SQLDatasource in Design View, then in the properties pane, select Events and then double-click in the correct event.

andrewsmd

7:55 pm on Sep 9, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

I have found the error and it's not the timeout issue, it's something else.