J
john
Hello,
We have a tablet pc that is trying to sync data
to our main sql server during the night but we keep
getting a timeout issue. The tablet pc has an access
database that contains about 5000 rows of data. We have
setup the machine.config for a executionTimeout of 900.
Below is our code for the specific vb.net app and the
webservice it uses. Can someone see if I am missing
something as to why this is bombing out? Also is there a
we to do a trace to determine where it is actually
timeing out? Thanks in advance.
John
vb.net code for tablet pc
Dim ds As New DataSet()
Dim oleDr As OleDbDataReader
Dim update As String
Dim errnbr As Integer
Dim errmsg As String
Try
Dim Olecmd As New OleDbCommand( _
"SELECT projectid, unit, bldg, lot, plantype, unitplan,
phase, isdeleted,
updatedate " & _
"FROM seqsheets WHERE updatedate > #" & lastsync & "#",
OleDbConnection1)
Dim da As New OleDbDataAdapter(Olecmd)
cmd.CommandType = CommandType.Text
da.Fill(ds)
Service.Url = ServiceUrl
update = Service.InsertSeqsheet(ds)
If update <> "true" Then
errnbr = CInt(Microsoft.VisualBasic.Left(update, InStr
(update, "^") - 1))
errmsg = Microsoft.VisualBasic.Right(update, Len(update) -
InStr(update,
"^"))
SendErrorToDB("HH3 Name: " & HH3Name & " " & errmsg, "53-
" &
errnbr.ToString, "SendSeqsheets")
End If
Catch ex As OleDbException
SendErrorToDB("HH3 Name: " & HH3Name & " " &
ex.Message, "54-" &
ex.ErrorCode.ToString, " Trace: " &
ex.StackTrace.ToString)
Finally
If OleDbConnection1.State = ConnectionState.Open Then
OleDbConnection1.Close()
End If
End Try
webservice
Dim cnt As Integer
Dim x As Integer
Dim isdeleted As Integer
Try
Dim cn As New SqlConnection(strConn)
Dim dr As SqlDataReader
cn.Open()
With ds.Tables(0)
For x = 0 To .Rows.Count - 1
Dim cmd1 As New SqlCommand("SELECT count(*) FROM
seqsheets " & _
"WHERE projectid = " & CStr(.Rows(x).Item(0)) & " " & _
"AND unit = '" & CStr(.Rows(x).Item(1)) & "' " & _
"AND bldg = '" & CStr(.Rows(x).Item(2)) & "' " & _
"AND lot = '" & CStr(.Rows(x).Item(3)) & "'", cn)
dr = cmd1.ExecuteReader()
While dr.Read
cnt = dr.GetInt32(0)
End While
dr.Close()
If CStr(.Rows(x).Item(7)) = "true" Then
isdeleted = 1
Else
isdeleted = 0
End If
If cnt = 0 Then
Dim cmd As New SqlCommand( _
"INSERT INTO seqsheets (projectid, unit, bldg, lot,
plantype, unitplan,
phase, isdeleted, updatedate) " & _
"VALUES (" & CStr(.Rows(x).Item(0)) & ", '" & CStr(.Rows
(x).Item(1)) & "',
'" & _
CStr(.Rows(x).Item(2)) & "', '" & CStr(.Rows(x).Item(3))
& "', '" & _
CStr(.Rows(x).Item(4)) & "', '" & CStr(.Rows(x).Item(5))
& "', '" & _
CStr(.Rows(x).Item(6)) & "', " & isdeleted & ", '" & _
CStr(.Rows(x).Item(8)) & "')", cn)
cmd.CommandType = CommandType.Text
dr = cmd.ExecuteReader()
dr.Close()
Else
Dim cmd As New SqlCommand( _
"SELECT updatedate FROM seqsheets " & _
"WHERE projectid = " & CStr(.Rows(x).Item(0)) & " " & _
"AND unit = '" & CStr(.Rows(x).Item(1)) & "' " & _
"AND bldg = '" & CStr(.Rows(x).Item(2)) & "' " & _
"AND lot = '" & CStr(.Rows(x).Item(3)) & "'", cn)
cmd.CommandType = CommandType.Text
Dim vcChk As Date = CDate(cmd.ExecuteScalar)
Dim hhChk As Date = CDate(.Rows(x).Item(8))
If vcChk < hhChk Then
Dim cmd2 As New SqlCommand( _
"UPDATE seqsheets " & _
"SET plantype = '" & CStr(.Rows(x).Item(4)) & "', " & _
"unitplan = '" & CStr(.Rows(x).Item(5)) & "', " & _
"phase = '" & CStr(.Rows(x).Item(6)) & "', " & _
"isdeleted = " & isdeleted & ", " & _
"updatedate = '" & CStr(.Rows(x).Item(8)) & "' " & _
"WHERE projectid = " & CStr(.Rows(x).Item(0)) & " " & _
"AND unit = '" & CStr(.Rows(x).Item(1)) & "'" & " " & _
"AND bldg = '" & CStr(.Rows(x).Item(2)) & "'" & " " & _
"AND lot = '" & CStr(.Rows(x).Item(3)) & "'", cn)
cmd2.CommandType = CommandType.Text
dr = cmd2.ExecuteReader()
dr.Close()
End If
End If
Next
End With
cn.Close()
Return "true"
Catch ex As SqlException
Return CStr(ex.Number) & "^" & ex.Message
End Try
We have a tablet pc that is trying to sync data
to our main sql server during the night but we keep
getting a timeout issue. The tablet pc has an access
database that contains about 5000 rows of data. We have
setup the machine.config for a executionTimeout of 900.
Below is our code for the specific vb.net app and the
webservice it uses. Can someone see if I am missing
something as to why this is bombing out? Also is there a
we to do a trace to determine where it is actually
timeing out? Thanks in advance.
John
vb.net code for tablet pc
Dim ds As New DataSet()
Dim oleDr As OleDbDataReader
Dim update As String
Dim errnbr As Integer
Dim errmsg As String
Try
Dim Olecmd As New OleDbCommand( _
"SELECT projectid, unit, bldg, lot, plantype, unitplan,
phase, isdeleted,
updatedate " & _
"FROM seqsheets WHERE updatedate > #" & lastsync & "#",
OleDbConnection1)
Dim da As New OleDbDataAdapter(Olecmd)
cmd.CommandType = CommandType.Text
da.Fill(ds)
Service.Url = ServiceUrl
update = Service.InsertSeqsheet(ds)
If update <> "true" Then
errnbr = CInt(Microsoft.VisualBasic.Left(update, InStr
(update, "^") - 1))
errmsg = Microsoft.VisualBasic.Right(update, Len(update) -
InStr(update,
"^"))
SendErrorToDB("HH3 Name: " & HH3Name & " " & errmsg, "53-
" &
errnbr.ToString, "SendSeqsheets")
End If
Catch ex As OleDbException
SendErrorToDB("HH3 Name: " & HH3Name & " " &
ex.Message, "54-" &
ex.ErrorCode.ToString, " Trace: " &
ex.StackTrace.ToString)
Finally
If OleDbConnection1.State = ConnectionState.Open Then
OleDbConnection1.Close()
End If
End Try
webservice
Dim cnt As Integer
Dim x As Integer
Dim isdeleted As Integer
Try
Dim cn As New SqlConnection(strConn)
Dim dr As SqlDataReader
cn.Open()
With ds.Tables(0)
For x = 0 To .Rows.Count - 1
Dim cmd1 As New SqlCommand("SELECT count(*) FROM
seqsheets " & _
"WHERE projectid = " & CStr(.Rows(x).Item(0)) & " " & _
"AND unit = '" & CStr(.Rows(x).Item(1)) & "' " & _
"AND bldg = '" & CStr(.Rows(x).Item(2)) & "' " & _
"AND lot = '" & CStr(.Rows(x).Item(3)) & "'", cn)
dr = cmd1.ExecuteReader()
While dr.Read
cnt = dr.GetInt32(0)
End While
dr.Close()
If CStr(.Rows(x).Item(7)) = "true" Then
isdeleted = 1
Else
isdeleted = 0
End If
If cnt = 0 Then
Dim cmd As New SqlCommand( _
"INSERT INTO seqsheets (projectid, unit, bldg, lot,
plantype, unitplan,
phase, isdeleted, updatedate) " & _
"VALUES (" & CStr(.Rows(x).Item(0)) & ", '" & CStr(.Rows
(x).Item(1)) & "',
'" & _
CStr(.Rows(x).Item(2)) & "', '" & CStr(.Rows(x).Item(3))
& "', '" & _
CStr(.Rows(x).Item(4)) & "', '" & CStr(.Rows(x).Item(5))
& "', '" & _
CStr(.Rows(x).Item(6)) & "', " & isdeleted & ", '" & _
CStr(.Rows(x).Item(8)) & "')", cn)
cmd.CommandType = CommandType.Text
dr = cmd.ExecuteReader()
dr.Close()
Else
Dim cmd As New SqlCommand( _
"SELECT updatedate FROM seqsheets " & _
"WHERE projectid = " & CStr(.Rows(x).Item(0)) & " " & _
"AND unit = '" & CStr(.Rows(x).Item(1)) & "' " & _
"AND bldg = '" & CStr(.Rows(x).Item(2)) & "' " & _
"AND lot = '" & CStr(.Rows(x).Item(3)) & "'", cn)
cmd.CommandType = CommandType.Text
Dim vcChk As Date = CDate(cmd.ExecuteScalar)
Dim hhChk As Date = CDate(.Rows(x).Item(8))
If vcChk < hhChk Then
Dim cmd2 As New SqlCommand( _
"UPDATE seqsheets " & _
"SET plantype = '" & CStr(.Rows(x).Item(4)) & "', " & _
"unitplan = '" & CStr(.Rows(x).Item(5)) & "', " & _
"phase = '" & CStr(.Rows(x).Item(6)) & "', " & _
"isdeleted = " & isdeleted & ", " & _
"updatedate = '" & CStr(.Rows(x).Item(8)) & "' " & _
"WHERE projectid = " & CStr(.Rows(x).Item(0)) & " " & _
"AND unit = '" & CStr(.Rows(x).Item(1)) & "'" & " " & _
"AND bldg = '" & CStr(.Rows(x).Item(2)) & "'" & " " & _
"AND lot = '" & CStr(.Rows(x).Item(3)) & "'", cn)
cmd2.CommandType = CommandType.Text
dr = cmd2.ExecuteReader()
dr.Close()
End If
End If
Next
End With
cn.Close()
Return "true"
Catch ex As SqlException
Return CStr(ex.Number) & "^" & ex.Message
End Try