我也加了C#组,因为这不是VB的问题,而是ExecuteNonQuery给出了超时。如何增加超时时间?
具体的错误信息: ERROR [HYT00] [Microsoft][ODBC SQL Server Driver]Timeout expired
我们有CMS系统,在该系统中我创建了一个IFRAME,调用这个ASPX+VB代码,它在不同的物理文件夹中,并且它有经典的托管管道。
当我在旧应用程序中运行它时,它能正常工作。但我将页面复制到新服务器并创建新应用程序池/网站Call OldASPPAGES时,下面的'ExecuteNonQuery()'函数会出现ODBC超时。
有时候它能运行,但十次里面有九次我会超时。我检查了记录,有247条记录。但下面你可以看到每个循环都会组成2个SQL语句。(更新和插入)... 2 x 247 = 大约500条记录。
但由于SQL语句是连接在一起的,所以执行一次ExecuteNonQuery,实际上是执行了500个SQL语句....
我知道这是垃圾,不能接受...但是我继承了这个东西,暂时无法重写它。
重点:
以下是原始语句:我只添加了:conn.ConnectionTimeout = 240 如你所见,我添加了240秒,但仍然在大约30秒后超时...所以这不是正确的位置....
问题: - 为什么这个语句在旧的ASP环境中可以工作(没有问题),但在IFRAME中的这个环境中不能工作...
- 我将超时设置为240,但仍然在30秒后超时。在哪里可以设置更长的超时时间?
有人能提供建议吗?
谢谢
Private Function data30bis_save(ByVal data_30bis_list, ByVal user_id)
lblMessage.Text = "data30bis_save"
Dim conn As OdbcConnection, cmd As OdbcCommand, ra As Integer, sql As String, data_30bis As Attest30bis.Data30bisCobonet
' important: always purge the old situation before loading the new one !
data30bis_cleanup(user_id)
conn = New OdbcConnection("dsn=chris2;uid=xxx;pwd=ssddddd;")
conn.ConnectionTimeout = 240
conn.Open()
sql = ""
lblMessage.Text = "st"
For Each data_30bis In data_30bis_list
sql = sql & "update usr_codes set "
If Len(data_30bis.analyseDate) > 0 Then
sql = sql & "a30b_analysedate=convert(datetime,'" & data_30bis.analyseDate.ToString & "',103)"
Else
sql = sql & "a30b_analysedate=getdate()"
End If
sql = sql & ", a30b_errorcode=" & data_30bis.errorCode
sql = sql & ", a30b_inputcompanyidvalid=" & Abs(CInt(data_30bis.inputCompanyIdValid))
sql = sql & ", a30b_inputnossvalid=" & Abs(CInt(data_30bis.inputNossValid))
sql = sql & ", a30b_noss=" & data_30bis.noss
sql = sql & ", a30b_reservecontractorconcept=" & Abs(CInt(data_30bis.reserveContractorConcept))
If Len(data_30bis.reserveContractorDate) > 0 Then
sql = sql & ", a30b_reservecontractordate=convert(datetime,'" & data_30bis.reserveContractorDate.ToString & "',103)"
Else
sql = sql & ", a30b_reservecontractordate=getdate()"
End If
sql = sql & ", a30b_reservemakingconcept=" & Abs(CInt(data_30bis.reserveMakingConcept))
If Len(data_30bis.reserveMakingDate) > 0 Then
sql = sql & ", a30b_reservemakingdate=convert(datetime,'" & data_30bis.reserveMakingDate.ToString & "',103)"
Else
sql = sql & ", a30b_reservemakingdate=getdate()"
End If
sql = sql & ", a30b_date_modified=getdate()"
sql = sql & " where user_id='" & user_id & "' and no_tva='" & data_30bis.companyId & "' and cd_pays_tva = 'BE'"
sql = sql & vbCrLf
' 2nd SQL
sql = sql & "insert a30b_controles (dt_ctrl, user_id, cd_pays_tva, no_tva, no_societe, a30b_analysedate, a30b_errorcode, a30b_inputcompanyidvalid, a30b_inputnossvalid, "
sql = sql & "a30b_noss, a30b_reservecontractorconcept, a30b_reservecontractordate, a30b_reservemakingconcept, a30b_reservemakingdate, a30b_companyId) "
sql = sql & "values (getdate(),'" & user_id & "','FR','massif',''," & "convert(datetime,'" & data_30bis.analyseDate.ToString & "',103)"
sql = sql & "," & data_30bis.errorCode & "," & Abs(CInt(data_30bis.inputCompanyIdValid)) & "," & Abs(CInt(data_30bis.inputNossValid)) & "," & data_30bis.noss & "," & Abs(CInt(data_30bis.reserveContractorConcept))
sql = sql & ",convert(datetime,'" & data_30bis.reserveContractorDate.ToString & "',103)," & Abs(CInt(data_30bis.reserveMakingConcept))
sql = sql & ",convert(datetime,'" & data_30bis.reserveMakingDate.ToString & "',103)," & data_30bis.companyId & ")"
sql = sql & vbCrLf
Next
lblMessage.Text = sql
cmd = New OdbcCommand(sql, conn)
ra = cmd.ExecuteNonQuery()
lblMessage.Text = "ssssst"
conn.Close()
cmd = Nothing
conn = Nothing
lblMessage.Text = "ssssat"
Return ra
End Function