OracleException: ORA-01036: illegal variable name/number

[johneydluca]

Hi
I am fairly new to .Net. I have a problem when I try to insert data into Oracle table using .Net. I use OracleClient interface.

Here is HOw I am doing this. I tried declaring paramters both as OracleType and SqlDBTYpe. But I am still getting the same error message.


Dim keyValue As String = CStr(DataGrid1.DataKeys(e.Item.ItemIndex))

' get the edit text boxes
Dim dist_type As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Dim dist_value As string = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Dim EFF_START_DT As dateTIME = CType(e.Item.Cells(5).Controls(0), TextBox).Text
Dim EFF_END_DT As dateTIME = CType(e.Item.Cells(6).Controls(0), TextBox).Text
Dim ADDED_DT As dateTIME = CType(e.Item.Cells(7).Controls(0), TextBox).Text
Dim ADDED_BY As string = CType(e.Item.Cells(8).Controls(0), TextBox).Text
Dim MOD_DT As DATETIME = CType(e.Item.Cells(9).Controls(0), TextBox).Text
Dim MOD_BY As string = CType(e.Item.Cells(10).Controls(0), TextBox).Text
Dim MSG_TXT1 As string = CType(e.Item.Cells(11).Controls(0), TextBox).Text
Dim MSG_TXT2 As string = CType(e.Item.Cells(12).Controls(0), TextBox).Text
Dim MSG_TXT3 As string = CType(e.Item.Cells(13).Controls(0), TextBox).Text

dim strSQL as string

' TODO: update the Command value for your application
Dim myConnection As New OracleConnection(ConnectionString)
Dim UpdateCommand As OracleCommand = new OracleCommand()
UpdateCommand.Connection = myConnection

If AddingNew = True Then
strSQL = "INSERT INTO ekt_comm_stmt_msg_rule (DISTRIBUTION_TP_CD, DISTRIBUTION_DESC, EFFECTIVE_START_DT,"
strSQL = strSQL + "EFFECTIVE_END_DT, CREATE_USER_ID, CREATE_TS, LAST_MODIFY_USER_ID, LAST_MODIFY_TS, MSG_TEXT1, MSG_TEXT2, MSG_TEXT3) "
strSQL = strSQL + "VALUES (@dist_type, @dist_value, @EFF_START_DT, @EFF_END_DT,"
strSQL = strSQL + " @ADDED_BY, @ADDED_DT, @MOD_BY, @MOD_DT, @msg1, @MSG2, @MSG3)"
UpdateCommand.CommandText = strSQL
Else
strSQL = ""
strSQL = "UPDATE ekt_comm_stmt_msg_rule SET DISTRIBUTION_TP_CD = @dist_type, DISTRIBUTION_DESC=@dist_value, "
strSQL = strSQL + "EFFECTIVE_START_DT=@EFF_START_DT, EFFECTIVE_END_DT = @EFF_END_DT,"
strSQL = strSQL + "CREATE_TS = @ADDED_DT, CREATE_USER_ID = @ADDED_BY,"
strSQL = strSQL + "LAST_MODIFY_TS = @MOD_DT, LAST_MODIFY_USER_ID = @MOD_BY, "
strSQL = strSQL + "msg_TEXT1 = @MSG1,MSG_TEXT2 = @MSG2, MSG_TEXT3 = @MSG3 "
strSQL = strSQL + "WHERE MSG_RULE_ID =" & keyvalue & ""
UpdateCommand.CommandText = strSQL
End If


UpdateCommand.Parameters.Add("@dist_type", OracleType.VARChar,10).Value = dist_type
UpdateCommand.Parameters.Add("@dist_value", OracleType.VARChar, 18).Value = dist_value
UpdateCommand.Parameters.Add("@EFF_START_DT", OracleType.dateTIME).Value = EFF_START_DT
UpdateCommand.Parameters.Add("@EFF_END_DT", OracleType.dateTIME).Value = EFF_END_DT
UpdateCommand.Parameters.Add("@MSG1", OracleType.varChar, 132).Value = MSG_TXT1
UpdateCommand.Parameters.Add("@MSG2", OracleType.varChar, 132).Value = MSG_TXT2
UpdateCommand.Parameters.Add("@MSG3", OracleType.varChar, 132).Value = MSG_TXT3
UpdateCommand.Parameters.Add("@ADDED_DT", OracleType.dateTIME).Value = ADDED_DT
UpdateCommand.Parameters.Add("@ADDED_BY", OracleType.Char,30).Value = ADDED_BY
UpdateCommand.Parameters.Add("@MOD_DT", OracleType.dateTIME).Value = MOD_DT
UpdateCommand.Parameters.Add("@MOD_BY", OracleType.Char,30).Value = MOD_BY

Response.Write ("<br>" & strSQL & "<br>")
' execute the command
Try
myConnection.Open()
UpdateCommand.ExecuteNonQuery()

Catch ex as Exception
Message.Text = ex.ToString()

Finally
myConnection.Close()

End Try


I get following Error Message:-

System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number at System.Data.OracleClient.OracleConnection.CheckError(OciHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleParameterBinding.Bind(OciHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection) at System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& rowidDescriptor, ArrayList& refCursorParameterOrdinals) at System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciHandle& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at ASP.sec_msg1_aspx.DataGrid_Update(Object Sender, DataGridCommandEventArgs E) in D:\MyWeb\Security\PA\sec_msg1.aspx:line 138

Line 138 is where I have Call ExecuteNonQuery()
While executing I am executing Insert part of IF Block.



Does anyone know What I am doing wrong here?


Thanks a lot in Advance.

Johney

 

[chiph]

What happens when you paste your SQL into osql?

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[johneydluca]

Hi Chiph,

Thanks a lot for your help. As you suggested, I had added Primary key field also to my query. Earlier I thought key field gets added automatically. Also for OracleCommand I have changed Query and Parameters as
-- Query


strSQL = ""
strSQL = "INSERT INTO ekt_comm_stmt_msg_rule (MSG_RULE_ID, DISTRIBUTION_TP_CD, DISTRIBUTION_DESC, "
strSQL = strSQL + "EFFECTIVE_START_DT, EFFECTIVE_END_DT, CREATE_USER_ID, CREATE_TS, LAST_MODIFY_USER_ID, "
strSQL = strSQL + " LAST_MODIFY_TS, MSG_TEXT1, MSG_TEXT2, MSG_TEXT3) "
strSQL = strSQL + "VALUES msgid, :dist_type, :dist_value, :EFF_START_DT, :EFF_END_DT,"
strSQL = strSQL + " :ADDED_BY, :ADDED_DT, :MOD_BY, :MOD_DT, :msg1, :MSG2, :MSG3)"



Parameters

UpdateCommand.Parameters.Add("msgid", OracleType.Number, 9, "MSG_RULE_ID").Value = recordCount


Thanks again

Johney