SqlException: Timeout expired

SqlException: Timeout expired

• stuartdunkeld

  Hi,

  I implemented the code from the function and put it in the main code. Now the problem is solved.

  I took your advice and am using executeScalar now instead of using read.

  
  Thanks for your input,

  Mark

  
  

• Heather_Hope

  The full SaveBon function: The code in the calling code is using a transaction to be sure that all inserts and update succeed or fail.

  Private Function SaveBon(ByVal lln As String) As String
  
  Dim blnHasBon As Boolean
  Dim strSQL As String = "SELECT wps_Opdracht.idWerkbon FROM wps_Opdracht WHERE wps_Opdracht.idLeerling='" & lln & "' AND wps_Opdracht.blnAfgerond = 0"
  Dim cnnSaveBon As New SqlConnection(ConfigurationSettings.AppSettings("SQLConnectionString"))
  Dim cmdHasBon As New SqlCommand(strSQL, cnnSaveBon)
  cnnSaveBon.Open()
  Dim rdHasBon As SqlDataReader = cmdHasBon.ExecuteReader()
  While rdHasBon.Read()
  blnHasBon = True
  End While
  rdHasBon.Close()
  
  Dim tmpConvert As nweOpzet.SSIT.Converters
  Dim lngNieuweWerkplek As Integer
  strSQL = "SELECT MIN (id) FROM wps_Werkplek WHERE blnIngebruik = 1 AND id NOT IN (SELECT idWerkplek FROM wps_Opdracht WHERE blnAfgerond = 0) AND idTypeWerkplek IN (SELECT idTypeWerkplek FROM wps_Werkbon WHERE idWerkbon = " & intBonId & ")"
  Dim cmdNieuweWerkplek As New SqlCommand(strSQL, cnnSaveBon)
  Dim rdNieuweWerkplek As SqlDataReader = cmdNieuweWerkplek.ExecuteReader
  While rdNieuweWerkplek.Read()
  lngNieuweWerkplek = rdNieuweWerkplek.GetValue(0)
  End While
  rdNieuweWerkplek.Close()
  cnnSaveBon.Close()
  
  If blnHasBon = True Then
  Dim strUpdate As String
  strUpdate = "UPDATE wps_Opdracht SET idWerkbon=" & intBonId & ", datDatumStart='" & tmpConvert.ConvertDBDate(strdatDatumStart, strdatDatumStartEng) & "'" & _
  ", txtCijfer='', blnAllesIngeleverd=0, txtBeoordeling='', lngDoorloopTijd=0, idWerkplek= " & lngNieuweWerkplek & _
  " WHERE wps_Opdracht.idLeerling='" & lln & "' AND wps_Opdracht.blnAfgerond = 0"
  SaveBon = strUpdate
  Else
  Dim strInsert As String
  strInsert = "INSERT INTO wps_Opdracht (idLeerling, idWerkbon, datDatumStart, idWerkplek, blnMedeLlnGekozen) VALUES ('" & lln & "', " & intBonId & ", '" & tmpConvert.ConvertDBDate(strdatDatumStart, strdatDatumStartEng) & "', " & lngNieuweWerkplek & " , 1)"
  SaveBon = strInsert
  End If

  End Function

  
  

• OrfWare

  clearly this is not an efficient way of doing things.

  • Why do you need to open and close the connection every time Use a connection that is global to the scope of SaveBon()

  • You can achieve the same result for

  While rdHasBon.Read()
    blnHasBon = True
  End While

  using

  blnHasBon  = rdHasBon.HasRows()

  • I would create a dataview in the beginning and put a filter on it in SaveBon everytime depending on the parameters. This would use much less DB resources.

   

   

  
  

• sved

  There are several issues here.

  • I would not use concatenated queries here--I would use SqlCommand objects and Parameters to manage the input parameters.
  • I would probably do this on the server in a SP where the logic does not require several round-trips to accomplish. A single SP can do all of this in a single operation thus negating the need for client-side transaction management.
  • I would always qualify my Exec statements to specify that I want a single value(ExecuteScalar) or a single row (CommandBehavior) instead of looping through the rowset using Read.
  • You might have filled the Connection Pool--thus the timeout. This means you're leaking connections (not closing them or overflowing the capacity of the server to process rowsets due to recursion or other load issues).
  • Another reason for timeouts is deadly embrace but SQL Server usually catches this state. That is, one query is holding resources needed by another. Take a look at sp_lock to see if this is the case.

  hth

  
  
  

• Emil Steen @ Cognitio

  I dont see any problems in the second code snippet. Can i see the complete definition for the method

  function SaveBon(string) as string
  

  
  
• SqlException: Timeout expired