I am running a windows app. with a stored procedure and
the stored proc runs up to 90 seconds, the time out in
the connection string is set for 90 seconds. It seems
that the time out setting is ignored no matter what I set
it to. I have tried all different objects e.g.
datatables,etc... and all providers
(OLDEDB,ODBC,SLQCLIENT) If the SP runs under 30 seconds
the app works, else it returns:
System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the
operation or the server
This is all the code!!
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System.Configuration
Imports System.Data.Odbc
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim sConn As String
sConn = "user id=sa;pwd=pass;database=sample;
server=sample; Connect Timeout=90;"
Dim sStart As String = txtStartDate.Text.Trim
Dim sEnd As String = txtEndDate.Text.Trim
Dim sStore As String = txtStore.Text.Trim
Dim conn As New SqlConnection(sConn)
Dim cmd As New SqlCommand("gp_LasikCommissions_1", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim Param As New SqlParameter("@StartDate",
SqlDbType.DateTime)
Param.Direction = ParameterDirection.Input
Param.Value = sStart
cmd.Parameters.Add(Param)
Dim Param1 As New SqlParameter("@EndDate",
SqlDbType.DateTime)
Param1.Direction = ParameterDirection.Input
Param1.Value = sEnd
cmd.Parameters.Add(Param1)
Dim Param2 As New SqlParameter("@Store_No",
SqlDbType.Char)
Param2.Direction = ParameterDirection.Input
Param2.Value = sStore
cmd.Parameters.Add(Param2)
Dim myReader As SqlDataReader
Try
myReader = cmd.ExecuteReader()
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try
Try
While myReader.Read()
Console.WriteLine((myReader(0).ToString & ", " & myReader
(1).ToString & ", " & myReader(2).ToString))
End While
Catch ex As NullReferenceException
Console.WriteLine(ex.ToString)
Finally
' always call Close when done reading.
If Not myReader Is Nothing Then
myReader.Close()
End If
' always call Close when done reading.
conn.Close()
End Try
End Sub
End Class 3 1953
Set the command Timeout. The connection timeout only affects the time frame
that it takes the app to do the handshake with the server so it doesn't have
any bearing on the execution time of a command.
The command has a timeout property as well and adjusting it upward should do
the trick.
As an aside, I'd recommend taking a look at why my Proc takes 90 seconds to
run. Of course it depends on the type of app, but if there's user
interatction, that's a long time to run...may want to spin off a different
thread.... Just for giggles, run that proc through the index tuning wizard
and see what it comes up with, even though you can adjust the timeout
period, if you can shrink the time frame down, it may be worth adding some
indices (although I'm not implying that slapping on indexes is always the
best way to solve performance problems, b/c you have to maintain them and
they affect the speed of inserts and all.)
Anyway, the Command.Timeout should fix it for you. Let me know if it doesn.t
Bill
"rsherman" <an*******@discussions.microsoft.com> wrote in message
news:01****************************@phx.gbl... I am running a windows app. with a stored procedure and the stored proc runs up to 90 seconds, the time out in the connection string is set for 90 seconds. It seems that the time out setting is ignored no matter what I set it to. I have tried all different objects e.g. datatables,etc... and all providers (OLDEDB,ODBC,SLQCLIENT) If the SP runs under 30 seconds the app works, else it returns:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server
This is all the code!!
Option Strict On Imports System.Data Imports System.Data.SqlClient Imports System.Diagnostics Imports System.Configuration Imports System.Data.Odbc
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sConn As String
sConn = "user id=sa;pwd=pass;database=sample; server=sample; Connect Timeout=90;" Dim sStart As String = txtStartDate.Text.Trim
Dim sEnd As String = txtEndDate.Text.Trim
Dim sStore As String = txtStore.Text.Trim
Dim conn As New SqlConnection(sConn)
Dim cmd As New SqlCommand("gp_LasikCommissions_1", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim Param As New SqlParameter("@StartDate", SqlDbType.DateTime)
Param.Direction = ParameterDirection.Input
Param.Value = sStart
cmd.Parameters.Add(Param)
Dim Param1 As New SqlParameter("@EndDate", SqlDbType.DateTime)
Param1.Direction = ParameterDirection.Input
Param1.Value = sEnd
cmd.Parameters.Add(Param1)
Dim Param2 As New SqlParameter("@Store_No", SqlDbType.Char)
Param2.Direction = ParameterDirection.Input
Param2.Value = sStore
cmd.Parameters.Add(Param2)
Dim myReader As SqlDataReader
Try
myReader = cmd.ExecuteReader()
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try Try
While myReader.Read()
Console.WriteLine((myReader(0).ToString & ", " & myReader (1).ToString & ", " & myReader(2).ToString))
End While
Catch ex As NullReferenceException
Console.WriteLine(ex.ToString)
Finally
' always call Close when done reading.
If Not myReader Is Nothing Then
myReader.Close()
End If
' always call Close when done reading.
conn.Close()
End Try
End Sub
End Class
How about connection objects?
Public Function GetDataTableODBC(ByVal sSQL As
String, ByVal sConnString As String) As DataTable
Dim dtGeneric As New DataTable
Dim oDataSet As New DataSet 'create a new
dataset...
oDataSet.Clear()
Dim conn As New OdbcConnection(sConnString) 'pass
connString to conn obj...
conn.ConnectionTimeout = 90
Dim objDataAdapter As New OdbcDataAdapter(sSQL,
conn)
Try
objDataAdapter.Fill
(oDataSet, "Generic") 'fill dataSet with data...
Catch ex As OdbcException
MsgBox(ex.ToString, MsgBoxStyle.Critical)
Exit Try
End Try
dtGeneric = oDataSet.Tables("Generic")
Return dtGeneric
End Function -----Original Message----- Set the command Timeout. The connection timeout only
affects the time framethat it takes the app to do the handshake with the
server so it doesn't haveany bearing on the execution time of a command.
The command has a timeout property as well and adjusting
it upward should dothe trick.
As an aside, I'd recommend taking a look at why my Proc
takes 90 seconds torun. Of course it depends on the type of app, but if
there's userinteratction, that's a long time to run...may want to
spin off a differentthread.... Just for giggles, run that proc through the
index tuning wizardand see what it comes up with, even though you can
adjust the timeoutperiod, if you can shrink the time frame down, it may be
worth adding someindices (although I'm not implying that slapping on
indexes is always thebest way to solve performance problems, b/c you have to
maintain them andthey affect the speed of inserts and all.)
Anyway, the Command.Timeout should fix it for you. Let
me know if it doesn.t Bill "rsherman" <an*******@discussions.microsoft.com> wrote
in messagenews:01****************************@phx.gbl... I am running a windows app. with a stored procedure and the stored proc runs up to 90 seconds, the time out in the connection string is set for 90 seconds. It seems that the time out setting is ignored no matter what I
set it to. I have tried all different objects e.g. datatables,etc... and all providers (OLDEDB,ODBC,SLQCLIENT) If the SP runs under 30 seconds the app works, else it returns:
System.Data.SqlClient.SqlException: Timeout expired.
The timeout period elapsed prior to completion of the operation or the server
This is all the code!!
Option Strict On Imports System.Data Imports System.Data.SqlClient Imports System.Diagnostics Imports System.Configuration Imports System.Data.Odbc
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sConn As String
sConn = "user id=sa;pwd=pass;database=sample; server=sample; Connect Timeout=90;" Dim sStart As String = txtStartDate.Text.Trim
Dim sEnd As String = txtEndDate.Text.Trim
Dim sStore As String = txtStore.Text.Trim
Dim conn As New SqlConnection(sConn)
Dim cmd As New SqlCommand("gp_LasikCommissions_1",
conn) cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim Param As New SqlParameter("@StartDate", SqlDbType.DateTime)
Param.Direction = ParameterDirection.Input
Param.Value = sStart
cmd.Parameters.Add(Param)
Dim Param1 As New SqlParameter("@EndDate", SqlDbType.DateTime)
Param1.Direction = ParameterDirection.Input
Param1.Value = sEnd
cmd.Parameters.Add(Param1)
Dim Param2 As New SqlParameter("@Store_No", SqlDbType.Char)
Param2.Direction = ParameterDirection.Input
Param2.Value = sStore
cmd.Parameters.Add(Param2)
Dim myReader As SqlDataReader
Try
myReader = cmd.ExecuteReader()
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try Try
While myReader.Read()
Console.WriteLine((myReader(0).ToString & ", " &
myReader (1).ToString & ", " & myReader(2).ToString))
End While
Catch ex As NullReferenceException
Console.WriteLine(ex.ToString)
Finally
' always call Close when done reading.
If Not myReader Is Nothing Then
myReader.Close()
End If
' always call Close when done reading.
conn.Close()
End Try
End Sub
End Class
.
I'm not sure what you are asking me...both Connection and Command objects
have a timeout property. They timeout for entirely different reasons.
<an*******@discussions.microsoft.com> wrote in message
news:03****************************@phx.gbl... How about connection objects?
Public Function GetDataTableODBC(ByVal sSQL As String, ByVal sConnString As String) As DataTable Dim dtGeneric As New DataTable
Dim oDataSet As New DataSet 'create a new dataset... oDataSet.Clear() Dim conn As New OdbcConnection(sConnString) 'pass connString to conn obj...
conn.ConnectionTimeout = 90
Dim objDataAdapter As New OdbcDataAdapter(sSQL, conn) Try
objDataAdapter.Fill (oDataSet, "Generic") 'fill dataSet with data...
Catch ex As OdbcException MsgBox(ex.ToString, MsgBoxStyle.Critical) Exit Try End Try
dtGeneric = oDataSet.Tables("Generic") Return dtGeneric
End Function -----Original Message----- Set the command Timeout. The connection timeout only affects the time framethat it takes the app to do the handshake with the server so it doesn't haveany bearing on the execution time of a command.
The command has a timeout property as well and adjusting it upward should dothe trick.
As an aside, I'd recommend taking a look at why my Proc takes 90 seconds torun. Of course it depends on the type of app, but if there's userinteratction, that's a long time to run...may want to spin off a differentthread.... Just for giggles, run that proc through the index tuning wizardand see what it comes up with, even though you can adjust the timeoutperiod, if you can shrink the time frame down, it may be worth adding someindices (although I'm not implying that slapping on indexes is always thebest way to solve performance problems, b/c you have to maintain them andthey affect the speed of inserts and all.)
Anyway, the Command.Timeout should fix it for you. Let me know if it doesn.t Bill "rsherman" <an*******@discussions.microsoft.com> wrote
in messagenews:01****************************@phx.gbl... I am running a windows app. with a stored procedure and the stored proc runs up to 90 seconds, the time out in the connection string is set for 90 seconds. It seems that the time out setting is ignored no matter what I set it to. I have tried all different objects e.g. datatables,etc... and all providers (OLDEDB,ODBC,SLQCLIENT) If the SP runs under 30 seconds the app works, else it returns:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server
This is all the code!!
Option Strict On Imports System.Data Imports System.Data.SqlClient Imports System.Diagnostics Imports System.Configuration Imports System.Data.Odbc
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sConn As String
sConn = "user id=sa;pwd=pass;database=sample; server=sample; Connect Timeout=90;" Dim sStart As String = txtStartDate.Text.Trim
Dim sEnd As String = txtEndDate.Text.Trim
Dim sStore As String = txtStore.Text.Trim
Dim conn As New SqlConnection(sConn)
Dim cmd As New SqlCommand("gp_LasikCommissions_1", conn) cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim Param As New SqlParameter("@StartDate", SqlDbType.DateTime)
Param.Direction = ParameterDirection.Input
Param.Value = sStart
cmd.Parameters.Add(Param)
Dim Param1 As New SqlParameter("@EndDate", SqlDbType.DateTime)
Param1.Direction = ParameterDirection.Input
Param1.Value = sEnd
cmd.Parameters.Add(Param1)
Dim Param2 As New SqlParameter("@Store_No", SqlDbType.Char)
Param2.Direction = ParameterDirection.Input
Param2.Value = sStore
cmd.Parameters.Add(Param2)
Dim myReader As SqlDataReader
Try
myReader = cmd.ExecuteReader()
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try Try
While myReader.Read()
Console.WriteLine((myReader(0).ToString & ", " & myReader (1).ToString & ", " & myReader(2).ToString))
End While
Catch ex As NullReferenceException
Console.WriteLine(ex.ToString)
Finally
' always call Close when done reading.
If Not myReader Is Nothing Then
myReader.Close()
End If
' always call Close when done reading.
conn.Close()
End Try
End Sub
End Class
. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Newcomer |
last post by:
Hi,
I added a string property for my winforms control.
This string property can take multiple lines of text by using "\r\n".
For example, "Hello\r\nWorld"
SHOULD prints:
Hello
World
The...
|
by: Rachel Suddeth |
last post by:
I have an application where some forms have many (say 100) UserControls on
them (each of which contain a label, an image, and a data entry control),
and each UserControl has a ToolTip provider...
|
by: J.MOUZAKIS |
last post by:
Please help me to bring back the AspNetAccessProvider in VS2005. This
provider was in the whidbey version (or beta 1). Although the idea of using
SQLExpress is maybe the best, I have the problem of...
|
by: rsherman |
last post by:
I am running a windows app. with a stored procedure and
the stored proc runs up to 90 seconds, the time out in
the connection string is set for 90 seconds. It seems
that the time out setting is...
|
by: John |
last post by:
Hi
I have created custom membership/role/profile providers as per the
web.config below. My questions are;
1. Have I defined them correctly?
2. How can I make the Login and Create User...
|
by: Greg |
last post by:
Is it possible to use (or even find to download) the .NET Data provider
for SQL server with the 1.1 Framework? I am keen to use both, and not
to upgrade to the Framework version 2. I can't seem to...
|
by: john |
last post by:
I have an exisiting web app to which I have been adding Web Parts. I
have found it very useful to implement custom providers for membership,
roles, and personalization. This was done by starting...
|
by: Mike |
last post by:
Hi:
I have been trying to create a web application that provides suport
for two membership/profile databases: one for private users and one
for another set of users. These tw user have a...
|
by: alexandis |
last post by:
We have tables of logins (users), that differs much from standard
microsoft structure - we don't use control question/answer, date
fields, etc. But instead we have several additional fields.
I...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |