Bonjour,
Voilà j'ai un gros soucis lors de l'exécution d'une fonction vb de mon App_code. Dans une fonction "UpdateMapping", j'ai 3 requetes qui s'enchainent, 1 d'insertion et 2 de mise à jour (update).
J'ai une erreur qui s'affiche à l'exécution et qui dit : "ORA-01036: illegal variable name/number"
Or quand je réduis ma fonction à la requete d'insertion + la 1iere requete de mise à jour alors là je n'ai aucune erreur et la mise à jour et l'insertion se font correctement.
Or lorsque je rajoute la deuxiéme requete de mise à jour alors là rien ne va plus.
Mes 3 requetes utilisent en partie les memes paramètres.
Merci pour votre aide - Sabine -
Voici ma Fonction:
Shared Function UpdateMapping(ByVal NAT As String, ByVal SRCKEY As String, ByVal UsrNam As String, ByVal Win_Log As String, ByVal ResCod As String, ByVal HDO As Boolean, ByVal BUM As Boolean, ByVal SLM As Boolean, ByVal RGM As Boolean, ByVal DTM As Boolean, ByVal REP As Boolean, ByVal MAIL As String) As Integer
Dim connectionString As String
Dim queryString As String
Dim queryStringHis As String
Dim queryString2 As String
Dim rowsAffected As Integer = 0
Dim dbConnection As IDbConnection
Dim dbCommand As IDbCommand
Dim dbParam_NAT As IDataParameter
Dim dbParam_SRC As IDataParameter
Dim dbParam_HDO As IDataParameter
Dim dbParam_BUM As IDataParameter
Dim dbParam_SLM As IDataParameter
Dim dbParam_RGM As IDataParameter
Dim dbParam_DTM As IDataParameter
Dim dbParam_REP As IDataParameter
Dim dbParam_MAIL As IDataParameter
Dim dbParam_User As IDataParameter
Dim dbParam_WINLOG As IDataParameter
Dim dbParam_USRNAM As IDataParameter
Dim dbParam_RESCOD As IDataParameter
Dim dbParam_UpdDate As IDataParameter
'Query definition for the history
queryStringHis = _
"INSERT INTO REF_TMS_USR_ROL_HIS(NAT, USR, USR_S_LAB, USR_L_LAB, WIN_LOG, USR_RES, USR_NAM, HDO,BUM, SLM,RGM, DTM, REP,EMAIL, ZT_UPD_BY, ZT_UPD_DATE, ZT_ETL_DATE, ZT_AFFILIATE, ZT_FLUX_ID, ZT_EXEC_ID,ZT_SOURCE_ID)" & _
" SELECT NAT, USR, USR_S_LAB, USR_L_LAB, WIN_LOG, USR_RES, USR_NAM, HDO, BUM, SLM, RGM, DTM, REP, EMAIL, :UPD_BY, :UPD_DATE , ZT_ETL_DATE, ZT_AFFILIATE, ZT_FLUX_ID, ZT_EXEC_ID, ZT_SOURCE_ID FROM REF_TMS_USR_ROL WHERE NAT = :NAT AND USR = :USR "
'Query definition for the update of REF_TMS_USR_ROL
queryString = "UPDATE REF_TMS_USR_ROL " & _
" SET HDO = :HDO, BUM = :BUM, SLM = :SLM, RGM = :RGM, DTM = :DTM, REP = :REP, EMAIL = :MAIL,WIN_LOG=:WINLOG, USR_NAM = :USRNAM,USR_RES = :RESCOD " & _
" WHERE NAT = :NAT AND USR = :USR"
' ' Query definition for the update of REF_USR
queryString2 = "UPDATE REF_USR " & _
"SET EMAIL= :MAIL" & _
"WHERE NAT= :NAT AND UPPER(USR_NT_ID)= :WINLOG; "
' ' Query definition for REF_LAB
' 'queryString3 = "UPDATE REF_LAB" & _
' ' "SET VAL_L_LAB=:USRNAM" & _
' ' "WHERE VAL=:USR AND NAT=:NAT"
Try
' Retrieve connection string from the configuration file
connectionString = ConfigurationManager.ConnectionStrings("SIMSDWH").ConnectionString
dbConnection = New OracleConnection(connectionString)
dbCommand = dbConnection.CreateCommand()
' To insert into the historic table REF_TMS_USR_ROL_HIS
dbCommand.CommandText = queryStringHis
' Parameters definition
dbParam_NAT = New OracleParameter(":NAT", System.Data.DbType.String)
dbParam_NAT.Value = NAT
dbCommand.Parameters.Add(dbParam_NAT)
dbParam_SRC = New OracleParameter(":USR", System.Data.DbType.String)
dbParam_SRC.Value = SRCKEY
dbCommand.Parameters.Add(dbParam_SRC)
dbParam_User = New OracleParameter(":UPD_BY", System.Data.DbType.String)
dbParam_User.Value = HttpContext.Current.User.Identity.Name
dbCommand.Parameters.Add(dbParam_User)
dbParam_UpdDate = New OracleParameter(":UPD_DATE", System.Data.DbType.Date)
dbParam_UpdDate.Value = Date.Now
dbCommand.Parameters.Add(dbParam_UpdDate)
' Open the connection
dbConnection.Open()
dbCommand.Transaction = dbConnection.BeginTransaction()
' Execute the query for the history
dbCommand.ExecuteNonQuery()
' New parameter for the update query
dbParam_HDO = New OracleParameter(":HDO", System.Data.DbType.Int16)
dbParam_HDO.Value = HDO
dbCommand.Parameters.Add(dbParam_HDO)
dbParam_SLM = New OracleParameter(":SLM", System.Data.DbType.Int16)
dbParam_SLM.Value = SLM
dbCommand.Parameters.Add(dbParam_SLM)
dbParam_DTM = New OracleParameter(":DTM", System.Data.DbType.Int16)
dbParam_DTM.Value = DTM
dbCommand.Parameters.Add(dbParam_DTM)
dbParam_REP = New OracleParameter(":REP", System.Data.DbType.Int16)
dbParam_REP.Value = REP
dbCommand.Parameters.Add(dbParam_REP)
dbParam_BUM = New OracleParameter(":BUM", System.Data.DbType.Int16)
dbParam_BUM.Value = BUM
dbCommand.Parameters.Add(dbParam_BUM)
dbParam_RGM = New OracleParameter(":RGM", System.Data.DbType.Int16)
dbParam_RGM.Value = RGM
dbCommand.Parameters.Add(dbParam_RGM)
dbParam_MAIL = New OracleParameter(":MAIL", System.Data.DbType.String)
dbParam_MAIL.Value = MAIL + "@sanofi-aventis.com"
dbCommand.Parameters.Add(dbParam_MAIL)
dbParam_USRNAM = New OracleParameter(":USRNAM", System.Data.DbType.String)
dbParam_USRNAM.Value = UCase(UsrNam)
dbCommand.Parameters.Add(dbParam_USRNAM)
dbParam_WINLOG = New OracleParameter(":WINLOG", System.Data.DbType.String)
If Win_log = Nothing Then
dbParam_WINLOG.Value = ""
Else
dbParam_WINLOG.Value = UCase(Win_log)
End If
dbCommand.Parameters.Add(dbParam_WINLOG)
dbParam_RESCOD = New OracleParameter(":RESCOD", System.Data.DbType.String)
If ResCod = Nothing Then
dbParam_RESCOD.Value = ""
Else
dbParam_RESCOD.Value = UCase(ResCod)
End If
dbCommand.Parameters.Add(dbParam_RESCOD)
dbCommand.Parameters.Remove(dbParam_User)
dbCommand.Parameters.Remove(dbParam_UpdDate)
' To update table REF_TMS_USR_ROL
dbCommand.CommandText = queryString
dbCommand.ExecuteNonQuery()
dbCommand.Parameters.Remove(dbParam_RESCOD)
dbCommand.Parameters.Remove(dbParam_RGM)
dbCommand.Parameters.Remove(dbParam_BUM)
dbCommand.Parameters.Remove(dbParam_REP)
dbCommand.Parameters.Remove(dbParam_DTM)
dbCommand.Parameters.Remove(dbParam_SLM)
dbCommand.Parameters.Remove(dbParam_HDO)
'dbCommand.Parameters.Remove(dbParam_MAIL)
'dbCommand.Parameters.Remove(dbParam_WINLOG)
dbCommand.Parameters.Remove(dbParam_SRC)
dbCommand.Parameters.Remove(dbParam_USRNAM)
dbCommand.CommandText = queryString2
dbCommand.ExecuteNonQuery()
dbCommand.Transaction.Commit()
Catch e As NullReferenceException
rowsAffected = 0
'Enregistrement dans le log
logWriter.Write(Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name & "->" & Reflection.MethodBase.GetCurrentMethod().Name & " : " & e.Message)
Catch e As OracleException
rowsAffected = 0
'Enregistrement dans le log
logWriter.Write(Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name & "->" & Reflection.MethodBase.GetCurrentMethod().Name & " : " & e.Message)
If Not dbCommand Is Nothing Then
dbCommand.Transaction.Rollback()
End If
Finally
If Not dbConnection Is Nothing Then
If dbConnection.State <> ConnectionState.Closed Then
dbConnection.Close()
End If
End If
End Try
End Function