Weird behavior when calling function

[Peter Afonin]

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

[msdn]

Somehow your function is called twice???

Set breakpoints on all events and see if one is firing twice.

Sa

"Peter Afonin" <pv*@speakeasy.net> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...
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

[Peter Afonin]

Hello,

I've done this first. No, it's called only once.

Thank you,

Peter

"msdn" <sq**********@hotmail.com> wrote in message
news:uD**************@TK2MSFTNGP03.phx.gbl...

Somehow your function is called twice???

Set breakpoints on all events and see if one is firing twice.

Sa

"Peter Afonin" <pv*@speakeasy.net> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...
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

[Praveen]

Hi,
Check if there exists any Triggers on that table.
Bye,
Praveen

[Peter Afonin]

Hello,

No, no triggers. And again - even if I don't insert anything and just
select a single number - it still increments by one by the time it gets
to the code that called this function.

Thank you,

Peter