Solved

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

Posted on 2011-03-02
7
274 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now