Hello,
I have a weirdest issue I've ever had.
I have a function that enters some data into the Oracle table and
returns the sequential row number for the new record (autonumber):
Private Function AddSystem(ByVal txt As TextBox, ByVal cn As
OracleConnection) As Integer
Try
cmdSys = New OracleCommand
With cmdSys
.Connection = cn
.CommandType = CommandType.StoredProcedure
.CommandText = "CONF_INSERT_SYSTEM_SP"
End With
With cmdSys.Parameters
.Clear()
.Add("mod_name", OracleType.VarChar, 200).Direction =
ParameterDirection.Input
.Item("mod_name").Value = Trim(txt.Text)
.Add("login", OracleType.VarChar, 20).Direction =
ParameterDirection.Input
.Item("login").Value = CType(Session("User"), String)
.Add("conf_id", OracleType.Number).Direction =
ParameterDirection.Input
.Item("conf_id").Value =
CInt(Me.ddlChangeType.SelectedItem.Value)
.Add("system_id", OracleType.Number).Direction =
ParameterDirection.Output
End With
cmdSys.ExecuteNonQuery()
Dim system_id As Integer =
CInt(cmdSys.Parameters("system_id").Value)
Return system_id
Catch ex As Exception
Finally
If Not IsNothing(cmdSys) Then
cmdSys.Dispose()
End If
End Try
End Function
The function itself works as expected, as well as the stored procedure.
This is the code that calls this function:
If Me.txtModule.Text <> "" Then
.Item("module_id").Value = AddModule(Me.txtModule, cn)
Else
.Item("module_id").Value = DBNull.Value
End If
Here the weird things start. The function enters data and returns the
row number, let's say, 23 (system_id). However, by the time it gets
back to the code that was calling it it increments by one, i.e. the
value of AddModule(Me.txtModule, cn) is 24, not 23. What's even more
surprising that at the same time the duplicate record is inserted into
the table.
If instead of inserting data I'm using a simple select statement to
select a single row and get the autonumber - the value still increments
by 1 by the time it gets back to the code that was calling the
function.
In other words, all this - incrementing by 1 and inserting an
additional row - happens when my program actually doesn't do anything.
The function had been executed as expected, and all this happens in
transition from the function back to the code that called it.
I have no idea how all this happens. I ended up instead of using
function just put the value I need intŠ¾ the session object and return
it this way. It's clumsy, but it works.
I would appreciate any thoughts on this.
Thank you.
Peter
