Link to home
Start Free TrialLog in
Avatar of phoenixfire425
phoenixfire425Flag for United States of America

asked on

Query TimeOut on ASP.net page and SQL Query 30 seconds

I have an asp.net application that I am having major timeout issues with.
But that is not the only place that I am getting timeouts.
It usually times out in exactly. 30 Seconds
if I use the query tool that is in "enterprise manager" then it also times out.
Is there a way to change the global timeout the SQL?
When using "Microsoft query" or "Query Analyzer"
I DONOT receive any timeouts
Is there a way that I can incorporate the same protocol in the ASP.net page that "Microsoft query" or "Query Analyzer?"

This problem is driving me crazy.
Avatar of chapmandew
chapmandew
Flag of United States of America image

YOu need to change the command time out property in the connection string.
sorry...in the web.config

Try adding a Connect Timeout in the web.config

<add key="DBConnection" value="server=LocalHost;uid=sa;pwd=;database=DataBaseName;Connect Timeout=200; pooling='true'; Max Pool Size=200"/>
Avatar of phoenixfire425

ASKER

Here is my current asp.net web.config connection script.
And it still timesout.

   <add name="CommerceCenterConnectionString3" connectionString="Data Source=Diamed-SQL;Initial Catalog=CommerceCenter;Connect Timeout=200;Persist Security Info=True;User ID=admin;Password=*****"
     providerName="System.Data.SqlClient" />
Server Error in '/' Application.
--------------------------------------------------------------------------------

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace:


[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
   System.Data.SqlClient.SqlDataReader.get_MetaData() +62
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +141
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
   System.Web.UI.Control.PreRenderRecursiveInternal() +86
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
what happens when you change it to 400?

Also, there may be underlying problems/design issues if you have a query on your web page taking over 30 seconds.  Do you want to try to optimize that query instead?  It may be a good idea.....
i changed the value to 2000 and i still receive the timeout.
the query is just in general a large query.
I have tried doing some optimizing and it still takes longer then 30 seconds to run.
i would love to be able to change some setting in the SQL server to allow a longer timeout period.
perhaps 60 seconds or 120 seconds.
one of the tables that we use is very large. and its the p21_sales_history_report_view

Here is the Query.
This is without the Dates in the critera

SELECT     TOP 100 PERCENT p21_sales_history_report_view.customer_id, p21_sales_history_report_view.ship2_name,
                      p21_sales_history_report_view.invoice_date, p21_sales_history_report_view.invoice_no, p21_sales_history_report_view.order_no,
                      p21_view_inv_mast.item_id, p21_view_inv_mast.item_desc + ' ' + p21_view_inv_mast.extended_desc AS Item,
                      p21_sales_history_report_view.sales_price AS Sales, SUM(p21_sales_history_report_view.cogs_amount) AS MAC,
                      SUM(p21_sales_history_report_view.cogs_amount) AS Cogs,
                      p21_sales_history_report_view.sales_price - p21_sales_history_report_view.cogs_amount AS [Profit$ MAC],
                      SUM(p21_sales_history_report_view.sales_price - p21_sales_history_report_view.cogs_amount)
                      / p21_sales_history_report_view.sales_price * 100 AS [Profit% MAC],
                      SUM(p21_sales_history_report_view.other_cost * (CASE WHEN dbo.p21_view_invoice_line.sales_unit_size > 0 THEN p21_sales_history_report_view.qty_shipped
                       / dbo.p21_view_invoice_line.sales_unit_size ELSE p21_sales_history_report_view.qty_shipped END)) AS [Rebated Cost],
                      SUM(p21_sales_history_report_view.sales_price)
                      - p21_sales_history_report_view.other_cost * (CASE WHEN dbo.p21_view_invoice_line.sales_unit_size > 0 THEN p21_sales_history_report_view.qty_shipped
                       / dbo.p21_view_invoice_line.sales_unit_size ELSE p21_sales_history_report_view.qty_shipped END) AS [Profit $ Rebated],
                      SUM(p21_sales_history_report_view.sales_price - p21_sales_history_report_view.other_cost * (CASE WHEN dbo.p21_view_invoice_line.sales_unit_size
                       > 0 THEN p21_sales_history_report_view.qty_shipped / dbo.p21_view_invoice_line.sales_unit_size ELSE p21_sales_history_report_view.qty_shipped
                       END)) / p21_sales_history_report_view.sales_price * 100 AS [Profit% Rebated], p21_sales_history_report_view.unit_price,
                      CASE WHEN dbo.p21_view_invoice_line.sales_unit_size > 0 THEN p21_sales_history_report_view.qty_shipped / dbo.p21_view_invoice_line.sales_unit_size
                       ELSE p21_sales_history_report_view.qty_shipped END AS qty_shipped, p21_sales_history_report_view.unit_of_measure,
                      p21_sales_history_report_view.period, p21_sales_history_report_view.year_for_period, p21_sales_history_report_view.salesrep_id
FROM         dbo.p21_sales_history_report_view p21_sales_history_report_view INNER JOIN
                      dbo.p21_view_inv_loc p21_view_inv_loc ON p21_sales_history_report_view.inv_mast_uid = p21_view_inv_loc.inv_mast_uid AND
                      p21_sales_history_report_view.sales_location_id = p21_view_inv_loc.location_id INNER JOIN
                      dbo.p21_view_inv_mast p21_view_inv_mast ON p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid INNER JOIN
                      dbo.p21_view_invoice_line ON p21_sales_history_report_view.invoice_no = dbo.p21_view_invoice_line.invoice_no AND
                      p21_sales_history_report_view.order_no = dbo.p21_view_invoice_line.order_no AND
                      p21_sales_history_report_view.item_id = dbo.p21_view_invoice_line.item_id
GROUP BY p21_sales_history_report_view.ship2_name, p21_sales_history_report_view.customer_id, p21_sales_history_report_view.class_1id,
                      p21_sales_history_report_view.sales_price, p21_sales_history_report_view.sales_price - p21_sales_history_report_view.cogs_amount,
                      p21_sales_history_report_view.item_id, p21_sales_history_report_view.period, p21_sales_history_report_view.year_for_period,
                      p21_view_inv_mast.item_id, p21_view_inv_mast.item_id, p21_view_inv_mast.item_desc, p21_sales_history_report_view.unit_of_measure,
                      p21_sales_history_report_view.unit_price, p21_sales_history_report_view.qty_shipped, p21_sales_history_report_view.other_cost,
                      p21_sales_history_report_view.invoice_no, p21_sales_history_report_view.salesrep_id, p21_sales_history_report_view.invoice_date,
                      p21_view_inv_mast.extended_desc, p21_view_inv_loc.moving_average_cost, p21_sales_history_report_view.order_no,
                      CASE WHEN dbo.p21_view_invoice_line.sales_unit_size > 0 THEN p21_sales_history_report_view.qty_shipped / dbo.p21_view_invoice_line.sales_unit_size
                       ELSE p21_sales_history_report_view.qty_shipped END, dbo.p21_view_invoice_line.sales_unit_size
HAVING      (p21_sales_history_report_view.item_id NOT IN ('FREIGHT', 'FUEL SURCHARGE', 'RESTOCK FEE', 'REPAIR')) AND
                      (p21_sales_history_report_view.sales_price > 0)

One thing I think you should consider doing is have a SQL job post the data from this query to a table every hour or so.  Then have your web page just pull data off of that table.  Is latency acceptable on the site? If it is, then this is for sure the way to go.
Well i have never really considered that.
but some of the reports that pull from the site that is acceptable
but most of them it has to be live data.
also this would not really work to well because the site has variables that they select.
or it could be a range of dates or some other variable.

Attached is the .aspx code.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CustomerPurchasingData.aspx.vb" Inherits="Template_Reports_CustomerPurchasingData" %>
 
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body style="text-align: center">
    <form id="form1" runat="server">
   <h1 style="text-align: center">
       Customer Sales Data<br />
       <a href="CustomerPurchasingDataFull.aspx"><span style="font-size: 10pt"><strong>Customer Sales
           Data Full Report</strong></span></a></h1>
    <div style="text-align: center">
        <table style="width: 412px">
            <tr>
                <td style="width: 200px; text-align: center">
                    <asp:Calendar ID="Calendar1" runat="server" BackColor="White" BorderColor="#3366CC"
                        BorderWidth="1px" CellPadding="1" DayNameFormat="Shortest" Font-Names="Verdana"
                        Font-Size="7pt" ForeColor="#003399" Height="124px" Width="111px">
                        <SelectedDayStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
                        <TodayDayStyle BackColor="#99CCCC" ForeColor="White" />
                        <SelectorStyle BackColor="#99CCCC" ForeColor="#336666" />
                        <WeekendDayStyle BackColor="#CCCCFF" />
                        <OtherMonthDayStyle ForeColor="#999999" />
                        <NextPrevStyle Font-Size="8pt" ForeColor="#CCCCFF" />
                        <DayHeaderStyle BackColor="#99CCCC" ForeColor="#336666" Height="1px" />
                        <TitleStyle BackColor="#003399" BorderColor="#3366CC" BorderWidth="1px" Font-Bold="True"
                            Font-Size="10pt" ForeColor="#CCCCFF" Height="25px" />
                    </asp:Calendar>
                </td>
                <td style="width: 4px">
                    TO
                </td>
                <td style="width: 212px; text-align: center">
                    <asp:Calendar ID="Calendar2" runat="server" BackColor="White" BorderColor="#3366CC"
                        BorderWidth="1px" CellPadding="1" DayNameFormat="Shortest" Font-Names="Verdana"
                        Font-Size="7pt" ForeColor="#003399" Height="124px" Width="111px">
                        <SelectedDayStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
                        <TodayDayStyle BackColor="#99CCCC" ForeColor="White" />
                        <SelectorStyle BackColor="#99CCCC" ForeColor="#336666" />
                        <WeekendDayStyle BackColor="#CCCCFF" />
                        <OtherMonthDayStyle ForeColor="#999999" />
                        <NextPrevStyle Font-Size="8pt" ForeColor="#CCCCFF" />
                        <DayHeaderStyle BackColor="#99CCCC" ForeColor="#336666" Height="1px" />
                        <TitleStyle BackColor="#003399" BorderColor="#3366CC" BorderWidth="1px" Font-Bold="True"
                            Font-Size="10pt" ForeColor="#CCCCFF" Height="25px" />
                    </asp:Calendar>
                </td>
            </tr>
        </table>
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
            DataTextField="Sales_Rep" DataValueField="id">
        </asp:DropDownList><br />
        <a href="CustomerPurchasingDataFull.aspx"></a>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceCenterConnectionString3 %>"
            SelectCommand="SELECT [Sales Rep] AS Sales_Rep, [id] FROM [REPCOM_Sales_Rep_ID]">
        </asp:SqlDataSource>
        &nbsp;&nbsp;<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
            BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px"
            CellPadding="3" DataSourceID="SqlDataSource1" Font-Names="Arial" Font-Size="8pt"
            GridLines="Vertical">
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
            <Columns>
                <asp:BoundField DataField="customer_id" HeaderText="customer_id" SortExpression="customer_id" />
                <asp:BoundField DataField="ship2_name" HeaderText="ship2_name" SortExpression="ship2_name" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="invoice_date" DataFormatString="{0:MM/dd/yyyy}"
                    HeaderText="invoice_date" HtmlEncode="False" SortExpression="invoice_date" />
                <asp:BoundField DataField="invoice_no" HeaderText="invoice_no" SortExpression="invoice_no" />
                <asp:BoundField DataField="order_no" HeaderText="order_no" SortExpression="order_no" />
                <asp:HyperLinkField DataNavigateUrlFields="order_no" DataNavigateUrlFormatString="OrderDetail.aspx?order_number={0}"
                    DataTextField="order_no" HeaderText="Order_details" SortExpression="order_no"
                    Target="_blank" />
                <asp:BoundField DataField="item_id" HeaderText="item_id" SortExpression="item_id" />
                <asp:BoundField DataField="Item" HeaderText="Item" SortExpression="Item" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="Sales" DataFormatString="{0:c}"
                    HeaderText="Sales" HtmlEncode="False" SortExpression="Sales" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="Profit$ MAC" DataFormatString="{0:c}"
                    HeaderText="Profit$" HtmlEncode="False" SortExpression="Profit$ MAC" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="Profit% MAC" DataFormatString="{0:.00}%"
                    HeaderText="Profit%" HtmlEncode="False" SortExpression="Profit% MAC" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="MAC" DataFormatString="{0:c}"
                    HeaderText="MAC" HtmlEncode="False" SortExpression="MAC" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="Rebated_Cost" DataFormatString="{0:c}"
                    HeaderText="Rebated Cost" HtmlEncode="False" SortExpression="Rebated Cost" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="unit_price" DataFormatString="{0:c}"
                    HeaderText="unit_price" HtmlEncode="False" SortExpression="unit_price" />
                <asp:BoundField ApplyFormatInEditMode="True" DataField="qty_shipped" DataFormatString="{0:.}"
                    HeaderText="qty_shipped" HtmlEncode="False" SortExpression="qty_shipped" />
                <asp:BoundField DataField="unit_of_measure" HeaderText="UOM" SortExpression="unit_of_measure" />
                <asp:BoundField DataField="salesrep_id" HeaderText="salesrep_id" SortExpression="salesrep_id" />
            </Columns>
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
            <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="Gainsboro" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceCenterConnectionString3 %>"
            SelectCommand="SELECT customer_id, ship2_name, invoice_date, invoice_no, order_no, item_id, Item, Sales, MAC, Cogs, [Profit$ MAC], [Profit% MAC], [Rebated Cost] AS Rebated_Cost, [Profit $ Rebated], [Profit% Rebated], unit_price, qty_shipped, unit_of_measure, salesrep_id FROM REPCOM_CustomerSalesData WHERE (invoice_date BETWEEN @var1 AND @var2) AND (salesrep_id = @varSalesRep)">
            <SelectParameters>
                <asp:ControlParameter ControlID="Calendar1" Name="var1" PropertyName="SelectedDate" />
                <asp:ControlParameter ControlID="Calendar2" Name="var2" PropertyName="SelectedDate" />
                <asp:ControlParameter ControlID="DropDownList1" Name="varSalesRep" PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>
    
    </div>
    </form>
</body>
</html>

Open in new window

anyone have any ideas on this one?
Have you checked to see if the fields you are querying on (WHERE clause) is indexed correctly?
I beleive that they are. how would i check this?
Capture the statements that are ran from your web app in profiler and look at the execution plans.  here is an article I wrote on how to do this:

http://blogs.techrepublic.com.com/datacenter/?p=269
since i am not on 2005 yet. how should i go about this?
Run profiler and feed the trace as a workload into the index tuning wizard.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspX
http://support.microsoft.com/default.aspx?scid=kb;en-us;q247070

Take a look at that..
With this in mind is there a way to make ASP.net connection string that does not use this connection?
No really....their resolution is to "not do it"....

...your users are not going to want to wait longer than 30 seconds for a page to come up.  I suggest snapshotting the data to a table and query from there.
(No points please, but ) It is not the Connect Timeout, but the Command Timeout.  They are different animals.
well i am still at a loss i have tried everything on this form.

every time that i use the Command Timeout. i get an error on the page and it will not display anything.

what is the connection string?
What do you mean "what is the connection string"?  It is the code you use to connect to the db.  Can you post yours (leave out the username/pwd)?
Have you looked in your web.config to see if there is a command time out in there?
no there is no command timeout in my web.config file.
everytime tthat i try to add it to my connection string the page when it loads the config file it bombs out with an error. when i use this connection     <add name="CommerceCenterConnectionString3" connectionString="Data Source=Diamed-SQL;Initial Catalog=CommerceCenter;Connect Timeout=2000;Persist Security Info=True;User ID=admin;Password=******"
     providerName="System.Data.SqlClient" />
_________
Server Error in '/' Application.
--------------------------------------------------------------------------------

Keyword not supported: 'command timeout'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Keyword not supported: 'command timeout'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace:


[ArgumentException: Keyword not supported: 'command timeout'.]
   System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +1481474
   System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +102
   System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52
   System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +24
   System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +125
   System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +56
   System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +138
   System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
   System.Web.UI.WebControls.ListControl.PerformSelect() +31
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
   System.Web.UI.Control.PreRenderRecursiveInternal() +86
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Im sorry my connction script was
    <add name="CommerceCenterConnectionString3" connectionString="Data Source=Diamed-SQL;Initial Catalog=CommerceCenter;Connect Timeout=2000;Persist Security Info=True;User ID=admin;Password=******;command timeout=60"
     providerName="System.Data.SqlClient" />
Take a look at this link...I guess the setting is "timeout"

http://www.codersource.net/asp_net_web_configuration_file.html
I beleive you will find that the CommandTimeout is usually set in code and not in the Connection string.
chapmandew. that was a great formal document but sadly i did not use anything from there. is there something i missed. i did learn some info though. thanks.

acperkins.
do you have an exapmle of where and how that CommendTimout is used?
The definition of  the CommandTimeout is in the .NET documentation:
SqlCommand.CommandTimeout Property
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(VS.80).aspx
so i am assuming that this goes into my code behind?

if so how do i add it into this

Imports System.Data.SqlClient
Imports System.Data
Partial Class Template_Reports_CustomerPurchasingData
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim UName As String = Mid$(Request.ServerVariables("LOGON_USER"), InStr(Request.ServerVariables("LOGON_USER"), "\") + 1, 500)
        Dim nConn As SqlConnection = New SqlConnection("Data Source=DIAMED-SQL;Initial Catalog=REPCOM;Persist Security Info=True;User ID=admin;Password=*******")
        Dim nCMD As SqlCommand = nConn.CreateCommand()

        nCMD.CommandText = "INSERT INTO UserLogs([User], Date, CustomerSalesData) VALUES ( '" & UName & "' , GETDATE() , ' x ' )"
        nConn.Open()

        nCMD.ExecuteNonQuery()
        nConn.Close()

    End Sub
End Class
>>so i am assuming that this goes into my code behind?<<
You assume well and preferably before you execute the query.

However, I have to point out that if all you are doing is inserting a single record than you have bigger problems...
no this query returns on average 20,000 to 30,000 rows.

the query that you see in the code behind is a query that tracks the time a user loads that report.

the main query is on the aspx page.
the tracking query is on the code behind. (ignore that one)

Also i have tried ever way i can think of to add that script and it gives me an error everytime.
can you send a sample of this in the code behind page.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No i have not tried
nCMD.CommandType = CommandType.Text

nCMD.CommandTimeout = 120              ' 2 minutes
nCMD.ExecuteNonQuery()

Where would i put that.  In the code behind or the aspx file?

and no i am not using a store procedure.  I am unsure howto create, and run the store procedure with my query.  I am passing the variables from the asp.net page to the server.  i designed the page using Visual Studio.
>>No i have not tried<<
I realized that is why I pointed it out.  It is however not related to your current problem.

>>Where would i put that.  In the code behind or the aspx file?<<
It goes in the code behind code in your vb file.
acperkins,
You are the amazing! thank you

all i did was added
nCMD.CommandTimeout = 120              ' 2 minutes
to my code behind


Imports System.Data.SqlClient
Imports System.Data
 
Partial Class Template_Reports_CustomerPurchasingData
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim UName As String = Mid$(Request.ServerVariables("LOGON_USER"), InStr(Request.ServerVariables("LOGON_USER"), "\") + 1, 500)
        Dim nConn As SqlConnection = New SqlConnection("Data Source=DIAMED-SQL;Initial Catalog=REPCOM;Persist Security Info=True;User ID=*****;Password=******")
        Dim nCMD As SqlCommand = nConn.CreateCommand()
        nCMD.CommandTimeout = 120              ' 2 minutes
 
        nCMD.CommandText = "INSERT INTO UserLogs([User], Date, CustomerSalesData) VALUES ( '" & UName & "' , GETDATE() , ' x ' )"
        nConn.Open()
 
        nCMD.ExecuteNonQuery()
        nConn.Close()
    End Sub
End Class

Open in new window

thank you very much you saved my sanity.
Final question about this.

is there a way to add this globally to the web.config file or somplace ?
so that i do not have to add this to ever code behind that i have?
well sadly for what ever reason i changed all of my code behinds and its back to timing out at 30 seconds.. and i have no idea why.
I dont understand this now. it seems like the querys run faster. but now i cannot figure out why after 30 seconds its timing out again.. and suggestions?

>>is there a way to add this globally to the web.config file or somplace ?<<
Thankfully that is not possible, unless you had a single data class and you set the CommandTimeout there.  I say thankfully, because it should never be treated as a panacea.  You should only use it as a last resort, when all other alternatives have been tried.

>>but now i cannot figure out why after 30 seconds its timing out<<
30 seconds is the default.  So I would have to guess that for whatever reason it is not executing the line that sets the CommandTimeout.

>>and suggestions?<<
I am afraid my days are numbered on this site.  I have been declared persona non grata.  So I will not be able to help you much further.

Good luck.
thank you very much
Avatar of elizbbw
elizbbw

I had this same 30-second timeout problem in an asp page connecting to a sql server 2005 database.  The magic solution for me was to change the CommandTimeout value in the asp page to something higher than the default 30 seconds.