StrataFrame Forum / StrataFrame Application Framework / Business Objects and Data Access (How do I?) / Increase Connection Timeout / Latest Posts

RE: Increase Connection Timeout

Bill Cunnien — Fri, 09 May 2008 11:06:20 GMT

Thanks. Took me a minute to make the right connection from the command to the BO. Here is my revised code:

ADUserBO mADUser = new ADUserBO();
String mPartNum = String.Empty;
if (PartNumCBE.SelectedValue != null)
{
mPartNum = (String)PartNumCBE.SelectedValue;
}
SqlCommand cmd = new SqlCommand("spx_GetRunningInventory_FinishedGoods");
cmd.CommandTimeout = 0;
cmd.Parameters.AddWithValue("@partnum", mPartNum).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("@div", mADUser.LocationIndex).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@invdate", InvDateDE.DateTime.ToShortDateString()).SqlDbType = SqlDbType.VarChar;
cmd.CommandType = CommandType.StoredProcedure;
rawMaterialValuationBO1.FillDataTable(cmd);
gridView1.Columns["CurrQty"].FilterInfo = new DevExpress.XtraGrid.Columns.ColumnFilterInfo("CurrQty > 0");
gridView1.ApplyColumnsFilter();

Works great! Thanks a bunch. Oh...and the wait window...awefully nice feature!!

Now, I just need to work on getting that sproc to run faster than 90 seconds. It has several nested CASE statements that I think are gumming up the works. Not too sure how to simplify those conversions that are going on.

Happy Friday!
Bill

RE: Increase Connection Timeout

Trent L. Taylor — Fri, 09 May 2008 09:52:02 GMT

[quote]If the command timeout can be set, how do I do that from the BO?[/quote]

Through a command:

[codesnippet]'-- Establish Locals
Dim cmd As New SqlCommand("MyStoredSproc")

'-- Never time out
cmd.CommandTimeout = 0

'-- Set the parms
cmd.Parameters.AddWithValue("@Parm1", 1).SqlDbType = SqlDbType.Int

'-- Set the command type
cmd.CommandType = CommandType.StoredProcedure

'-- Execute the command
Me.ExecuteNonQuery(cmd)[/codesnippet]

RE: Increase Connection Timeout

Bill Cunnien — Fri, 09 May 2008 09:36:04 GMT

Thanks Paul and Edhy!!

I am still using SQL Server 2000...I will see if I can find a corresponding configuration for setting the timeout.

Here is my code that I use for retrieving the data:

String mPartNum = String.Empty;
if (PartNumCBE.SelectedValue != null)
{
mPartNum = (String)PartNumCBE.SelectedValue;
}
SqlParameter mItemCode = new SqlParameter("@partnum", mPartNum);
ADUserBO mADUser = new ADUserBO();
SqlParameter mDiv = new SqlParameter("@div", mADUser.LocationIndex);
SqlParameter mInvDate = new SqlParameter("@invdate", InvDateDE.DateTime.ToShortDateString());
rawMaterialValuationBO1.FillByStoredProcedure("spx_GetRunningInventory_FinishedGoods", mItemCode, mDiv, mInvDate);
gridView1.Columns["CurrQty"].FilterInfo = new DevExpress.XtraGrid.Columns.ColumnFilterInfo("CurrQty > 0");
gridView1.ApplyColumnsFilter();

If the command timeout can be set, how do I do that from the BO?

Thanks,
Bill

RE: Increase Connection Timeout

Trent L. Taylor — Fri, 09 May 2008 09:24:01 GMT

Yeah, Paul nailed it on the last post. The most common timeout that is overlooked is on the command itself. You can set the connection timeout until you are blue in the face...but if you don't set the command time out it can still timeout on you. So if you set the command timeout you should be good to go! :)

RE: Increase Connection Timeout

Paul Chase — Fri, 09 May 2008 07:38:43 GMT

Bill,

I thought that I had found where to set it on a per query basis, I set it server wide to deal with an issue with E-connect for Great Plains, to set in on a per query basis simply set the commandtimeout property of the Sql Command.

Dim loCommand As New SqlCommand()

loCommand.CommandTimeout = 150

'-- Build the Query

loCommand.CommandText = "SELECT * FROM WHATEVER"

RE: Increase Connection Timeout

Paul Chase — Fri, 09 May 2008 07:18:03 GMT

Bill,

You can set the remote query timeout on the server, I had to change this when I was doing some import operations from my old foxpro data to Sql. I'm not sure if there is a way to change it ona per query basis but this did work for me with the same error.

RE: Increase Connection Timeout

Edhy Rijo — Thu, 08 May 2008 22:18:34 GMT

Hi Bill,

I found a thread that may give you an idea about using a separate thread for your query. Check it here: http://forum.strataframe.net/FindPost11979.aspx

RE: Increase Connection Timeout

Bill Cunnien — Thu, 08 May 2008 18:50:19 GMT

Here is the error:

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

My query has been timed at about 90 seconds. What can I do to get StrataFrame to increase this timeout?

Thanks!!

RE: Increase Connection Timeout

Bill Cunnien — Thu, 08 May 2008 18:33:18 GMT

Perhaps I should have title this thread: Increase Query Timeout

Increase Connection Timeout

Bill Cunnien — Thu, 08 May 2008 17:58:22 GMT

I have a query that is taking about 90 seconds. The window times out before any data is displayed. How do I increase the connection timeout for this query?

Thanks,
Bill