469,579 Members | 1,830 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Weird behavior when calling function

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

May 23 '06 #1
4 1178
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
May 23 '06 #2
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

May 24 '06 #3
Hi,
Check if there exists any Triggers on that table.
Bye,
Praveen

May 24 '06 #4
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

May 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Les Paul | last post: by
2 posts views Thread by Robert Waters | last post: by
11 posts views Thread by ncf | last post: by
2 posts views Thread by Zzzbla | last post: by
14 posts views Thread by Nak | last post: by
5 posts views Thread by Pupeno | last post: by
5 posts views Thread by comp.lang.php | last post: by
33 posts views Thread by Michael Speer | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.