User-1104215994 posted
Hi,
I am trying to update an Index but it halts! .How can I fix this?
Actually similar thing happens also for the SELECT FOR UPDATE!!!
Sample code:
Dim connectionString As String = _
"SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=***))(CONNECT_DATA=(SERVER=dedicated)
(SERVICE_NAME=***)));uid=**;pwd=**;"
Dim connectionString1 As String = _
"SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=***))(CONNECT_DATA=(SERVER=dedicated)
(SERVICE_NAME=*****)));uid=**;pwd=**;"
Dim maxIndex As Long = Nothing
Dim connOra As New Data.OracleClient.OracleConnection(connectionString)
Dim connOra1 As New Data.OracleClient.OracleConnection(connectionString1)
Dim myReader As OracleDataReader = Nothing
Try
'Query Available Index
Dim comm As New Data.OracleClient.OracleCommand("
SELECT TO_NUMBER (NVL (a.cur_val, 0)) + 1 FROM schema.APP_PARMS a
WHERE UPPER(a.parm_nm) = 'ID' FOR UPDATE", connOra)
connOra.Open()
myReader = comm.ExecuteReader()
'Check if Query Returns value
If myReader.HasRows Then
While myReader.Read()
If Not myReader(0) Is DBNull.Value Then
maxIndex = myReader(0)
Dim comm1 As New Data.OracleClient.OracleCommand("UPDATE schema.APP_PARMS a SET a.cur_val = :requestID
WHERE UPPER(a.parm_nm) = 'ID'", connOra1)
comm1.Parameters.AddWithValue(":requestID", maxIndex)
connOra1.Open()
comm1.ExecuteNonQuery()
'Close Connection
connOra1.Close()
End If
End While
End If
'Close Connection
connOra.Close()
If (Not (myReader) Is Nothing) Then
myReader.Close()
End If
Catch ex As Exception
Dim err As String = ex.Message
End Try
