Solved

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

Posted on 2011-03-02
7
292 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
Industry Leaders: 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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