Post
by mkashyap » Mon 08 Nov 2010 22:39
I am a coworker of DTSup. We are trying to figure this out. Below is the code I am trying to run. I am using dotConnect for Oracle version 5.70.190(trial version) and Visual Studio 2010. I am opening a connection in the direct mode. I don't know if that makes any difference but what I am seeing is very strange.
Dim cm As New OracleCommand
cm.Connection = conn
cm.CommandText = ssql
cm.ExecuteNonQuery()
cmd.Connection = conn
cmd.CommandText = "F21.FSF_SCHED_FCST_WARNINGS_CP.ins"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("sched_forecast_audit_id_in", OracleDbType.Number, 5)
cmd.Parameters("Sched_forecast_audit_id_In").Direction = ParameterDirection.Input
cmd.Parameters("Sched_forecast_audit_id_In").SourceColumn = "sched_forecast_audit_id"
cmd.Parameters.Add("message_in", OracleDbType.VarChar, 4000)
cmd.Parameters("message_in").Direction = ParameterDirection.Input
cmd.Parameters("message_in").SourceColumn = "message"
cmd.Parameters.Add("severity_in", OracleDbType.Char, 1)
cmd.Parameters("severity_in").Direction = ParameterDirection.Input
cmd.Parameters("severity_in").SourceColumn = "severity"
cmd.Parameters.Add("created_datetime_in", OracleDbType.Date, 0)
cmd.Parameters("created_datetime_in").Direction = ParameterDirection.Input
cmd.Parameters.Add("created_by_in", OracleDbType.VarChar, 4000)
cmd.Parameters("created_by_in").Direction = ParameterDirection.Input
cmd.Parameters.Add("sched_fcst_warning_id_out", OracleDbType.Number, 0)
cmd.Parameters("sched_fcst_warning_id_out").Direction = ParameterDirection.InputOutput
cmd.Parameters("sched_fcst_warning_id_out").SourceColumn = "sched_fcst_warning_id"
Dim adapter As OracleDataAdapter = New OracleDataAdapter()
Dim ds As DSScheduleForecastWarnings = New DSScheduleForecastWarnings()
Dim row As DSScheduleForecastWarnings.FSF_SCHED_FCST_WARNINGSRow = ds.FSF_SCHED_FCST_WARNINGS.NewFSF_SCHED_FCST_WARNINGSRow
ds.FSF_SCHED_FCST_WARNINGS.AddFSF_SCHED_FCST_WARNINGSRow(0, 482, "qwe", "W", DateTime.Now, "asdsa")
adapter.InsertCommand = cmd
adapter.Update(ds.Tables("FSF_SCHED_FCST_WARNINGS"))
'end data adapter
Dim I As Integer = cmd.Parameters.Item("sched_fcst_warning_id_Out").Value
As I step through the code, in the watch window, I see the parameters being added correctly, the insert command takes place without any problems. I see the name of the adapter.insertcommand.Parameters(5) set to "sched_Fcst_warning_id_out" which is correct. As soon as I execute the adapter.update statement, I see that the adapter.insertcommand.Parameters(5) displays "sched_fcst_warning_id_in" in my watch window. Therefore, the next line will fail because "sched_fcst_warning_id_Out" is no longer the name of the parameter. I get a "A parameter is not contained by this Parameters collection" error. If I look at the value as adapter.insertcommand.parameters(5).value, it is 0.
I am not sure what is happening, why is the name of the parameter changing? This procedure works just fine with 5.35 version of Devart.
I think DTSup will upload the sample code but this must be easy enough to try. Is there a reason why the parameter name is changing and goes from being an inputout to only an input parameter? Is there something wrong in the way we are setting up the parameters?
I should add that the Database procedure itself is executed and I see the change in the relevant table.
Appreciate your help in this.