Solved

ASP.NET VB running query against several servers times out.

Posted on 2011-03-02
7
288 Views
Last Modified: 2012-05-11
I have an asp.net vb app that will let you enter any query and run it against a single sql 2000 server or as many as 100 servers.  The results are displayed in a gridview.  This works great, unless it times out.  I have increased the connection timeout but still seem to have the same problem.  Thanks in advance for the help
VB Code ********
If RadioButton1.Checked = True Then 'All servers
                    myConnection = New SqlConnection("server=10.20.14.31;uid=user;pwd=pass;database=SCADA_DATA")
                    'establishing connection. you need to provide password for sql server
                    Try
                        myConnection.Open()
                        'opening the connection
                        myCommand = New SqlCommand("Select * from serverlist", myConnection)
                        'executing the command and assigning it to connection 
                        dr = myCommand.ExecuteReader()
                        While dr.Read()
                            'reading from the datareader
                            Dim VobServer As String = dr(1).ToString()
                            'MsgBox(VobServer)
                            'Start loop of new sql command
                            Try
                                If My.Computer.Network.Ping(VobServer) Then
                                    dbProvider = "provider = sqloledb;Data Source=" & VobServer & ";Initial Catalog=Windman;User ID=user;Password=pass"
                                    sql = txtQuery.Text
                                    con = New OleDb.OleDbConnection(dbProvider)
                                    con.Open()
                                    da = New OleDb.OleDbDataAdapter(sql, con)
                                    da.Fill(ds, "QueryResult")
                                    GridView1.DataSource = ds.Tables("QueryResult").DefaultView
                                    GridView1.DataBind()
                                Else

                                End If
                            Catch ex As Exception
                                'txtError.Text = ""
                                'txtDateTime.Text = ""
                                'txtError.Text = VobServer & " " & ex.Message
                                'txtDateTime.Text = Date.Now
                                'SqlDataSource1.Insert()
                            End Try
                            'displaying the data from the table
                        End While
                        dr.Close()
                        myConnection.Close()
                    Catch ex As Exception
                        'txtError.Text = ""
                        'txtDateTime.Text = ""
                        'txtError.Text = ex.Message
                        'txtDateTime.Text = Date.Now
                        'SqlDataSource1.Insert()
                    End Try
End If

Open in new window

0
Comment
Question by:NCSA SCADA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35021478
Hi,

Try this

da.SelectCommand.CommandTimeout = 0

This will set NO TIME OUT for your query, but be careful because since there is no time out it could get stuck if the query does not respond.

Hope that helps
0
 

Author Comment

by:NCSA SCADA
ID: 35021724
Hmm ..... thanks - I will give it a try
0
 

Author Comment

by:NCSA SCADA
ID: 35021748
would it be placed after
da = New OleDb.OleDbDataAdapter(sql, con)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35021777
Yes, just before you call the Fill method
0
 

Author Comment

by:NCSA SCADA
ID: 35021888
I still get an error.
Sys.WebForms.PageRequestManagerTimeoutException: The server request timed out
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 35021931
Ok, then, the error is getting from your ScriptManager,  try this, change your ScriptManaget tag in you markup  with this

<asp:ScriptManager ID="ScriptManager1" AsyncPostBackTimeOut="36000" runat="server" />
0
 

Author Closing Comment

by:NCSA SCADA
ID: 35022105
Thanks so much for the help.. have a good one
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question