Solved

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

Posted on 2011-03-02
7
282 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
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 …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 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