Can't extend timeout on sql server

[andrewsmd]

I keep getting this error in my asp application

System.Data.SqlClient.SqlException: NULL textptr (text, ntext, or image pointer) passed to WriteText function.Timeout expired.

It is saying that the procedure running has timed out. However, I know it hasn't because I followed the data and it is getting inserted. The problem is, the user is trying to upload a lot of records. The user uploads and excel file and I use the openrowset to insert them into a temporary table to add them in the correct spot. They are in the temporary table, but the timeout error happens and the procedure stops. Is there any way I can make the time out longer? I have set it in my web.config connection string, iis, and in the code behind in my vb. Here is what the code looks like.

Here is the asp sql data source

<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>

Here is the timeout in the vb in the code behind

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

And here is the connection string.

<add name="connectOrthman" connectionString="Data Source=10.2.25.11;Initial Catalog=partsonline_orthman_com_parts;User ID=partsonline_orthman_com_user;Password=hk7mzv;CommandTimeout = 240;Connect Timeout=240;" providerName="System.Data.SqlClient"/>

Is there some where I can change this in sql server? I am really lost on how to fix this. I have been timing it and it always seems to run for about 40 seconds. I appriciate any help Thanks

[edited by: marcel at 6:10 pm (utc) on Sep. 15, 2009]
[edit reason] fixed accidental smiley [/edit]

[marcel]

You are setting the e.Command.CommandTimeout on the Selecting event of your SQLDataSource, is this the correct event?

The SQLDataSource control also has a Inserting, Updating, and Deleting event you can set this value for.

[andrewsmd]

How do I set it on the inserting event because I have looked through to see if I could find a selectcommand timeout and I cannot seem to find one anywhere.

[marcel]

How do I set it on the inserting event

[webmasterworld.com...]

[andrewsmd]

Ah, the first time around on that I missed the .selecting at the end of the sub declaration. I guess that's what you get when your expected to work on 5 projects at the same time. I will try it out and see if it works. Thanks,

[andrewsmd]

Alright thanks, I think I have finally found that it is timing out due to a while loop that is never finishing. I will look into this some more, this problem is really kicking my butt. Thanks for all of the help.