Oracle throwing an exception, "ORA-01036: illegal variable name/nu

[vvenk]

Hello:

I wrote a small program to insert a row into an Oracle Table:

CREATE TABLE case_list
(case_list_name _c VARCHAR2(100) NOT NULL,
expiry_dt DATE NOT NULL,
days_to_expire_ n NUMBER(*,0),
created_dt DATE DEFAULT SYSDATE,
created_by_n NUMBER)

When I execute the following code,
Using connection As New OracleConnectio n(myConnection)
Dim lsString As String
lsString = "INSERT INTO CASE_LIST (CASE_LIST_NAME _C, EXPIRY_DT,
DAYS_TO_EXPIRE_ N, CREATED_DT, CREATED_BY_N) Values (@CASE_LIST_NAM E_C,
@EXPIRY_DT, @DAYS_TO_EXPIRE _N, @CREATED_DT, @CREATED_BY_N)"

Dim command As New OracleCommand
command = New OracleCommand(l sString)
command.Command Type = CommandType.Tex t
command.Paramet ers.Add("@CASE_ LIST_NAME_C", OracleType.VarC har,
100)
command.Paramet ers.Add("@EXPIR Y_DT", OracleType.Date Time)
command.Paramet ers.Add("@DAYS_ TO_EXPIRE_N", OracleType.Numb er)
command.Paramet ers.Add("@CREAT ED_DT", OracleType.Date Time)
command.Paramet ers.Add("@CREAT ED_BY_N", OracleType.Numb er)
command.Connect ion = connection
Try
connection.Open ()
command.Paramet ers("@CASE_LIST _NAME_C").Value = "Test"
command.Paramet ers("@EXPIRY_DT ").Value = Date.Today
command.Paramet ers("@DAYS_TO_E XPIRE_N").Value = 100
command.Paramet ers("@CREATED_D T").Value = Date.Today
command.Paramet ers("@CREATED_B Y_N").Value = 2
command.Execute NonQuery()

Catch ex As Exception
MessageBox.Show (ex.Message)
End Try
End Using
I get an exception "ORA-01036: illegal variable name/number "

Can somebody tell me what I am doing wrong?

Thanks.

venki

[Kerry Moorman]

venki,

I believe that Oracle named parameters must begin with a :, not an @.

Kerry Moorman
"vvenk" wrote:

Hello:

I wrote a small program to insert a row into an Oracle Table:

CREATE TABLE case_list
(case_list_name _c VARCHAR2(100) NOT NULL,
expiry_dt DATE NOT NULL,
days_to_expire_ n NUMBER(*,0),
created_dt DATE DEFAULT SYSDATE,
created_by_n NUMBER)

When I execute the following code,
Using connection As New OracleConnectio n(myConnection)
Dim lsString As String
lsString = "INSERT INTO CASE_LIST (CASE_LIST_NAME _C, EXPIRY_DT,
DAYS_TO_EXPIRE_ N, CREATED_DT, CREATED_BY_N) Values (@CASE_LIST_NAM E_C,
@EXPIRY_DT, @DAYS_TO_EXPIRE _N, @CREATED_DT, @CREATED_BY_N)"

Dim command As New OracleCommand
command = New OracleCommand(l sString)
command.Command Type = CommandType.Tex t
command.Paramet ers.Add("@CASE_ LIST_NAME_C", OracleType.VarC har,
100)
command.Paramet ers.Add("@EXPIR Y_DT", OracleType.Date Time)
command.Paramet ers.Add("@DAYS_ TO_EXPIRE_N", OracleType.Numb er)
command.Paramet ers.Add("@CREAT ED_DT", OracleType.Date Time)
command.Paramet ers.Add("@CREAT ED_BY_N", OracleType.Numb er)
command.Connect ion = connection
Try
connection.Open ()
command.Paramet ers("@CASE_LIST _NAME_C").Value = "Test"
command.Paramet ers("@EXPIRY_DT ").Value = Date.Today
command.Paramet ers("@DAYS_TO_E XPIRE_N").Value = 100
command.Paramet ers("@CREATED_D T").Value = Date.Today
command.Paramet ers("@CREATED_B Y_N").Value = 2
command.Execute NonQuery()

Catch ex As Exception
MessageBox.Show (ex.Message)
End Try
End Using
I get an exception "ORA-01036: illegal variable name/number "

Can somebody tell me what I am doing wrong?

Thanks.

venki

[vvenk]

Kerry:

You are a God-send! Thank you. That was the issue.

"Kerry Moorman" wrote:

venki,

I believe that Oracle named parameters must begin with a :, not an @.

Kerry Moorman
"vvenk" wrote:

Hello:

I wrote a small program to insert a row into an Oracle Table:

CREATE TABLE case_list
(case_list_name _c VARCHAR2(100) NOT NULL,
expiry_dt DATE NOT NULL,
days_to_expire_ n NUMBER(*,0),
created_dt DATE DEFAULT SYSDATE,
created_by_n NUMBER)

When I execute the following code,
Using connection As New OracleConnectio n(myConnection)
Dim lsString As String
lsString = "INSERT INTO CASE_LIST (CASE_LIST_NAME _C, EXPIRY_DT,
DAYS_TO_EXPIRE_ N, CREATED_DT, CREATED_BY_N) Values (@CASE_LIST_NAM E_C,
@EXPIRY_DT, @DAYS_TO_EXPIRE _N, @CREATED_DT, @CREATED_BY_N)"

Dim command As New OracleCommand
command = New OracleCommand(l sString)
command.Command Type = CommandType.Tex t
command.Paramet ers.Add("@CASE_ LIST_NAME_C", OracleType.VarC har,
100)
command.Paramet ers.Add("@EXPIR Y_DT", OracleType.Date Time)
command.Paramet ers.Add("@DAYS_ TO_EXPIRE_N", OracleType.Numb er)
command.Paramet ers.Add("@CREAT ED_DT", OracleType.Date Time)
command.Paramet ers.Add("@CREAT ED_BY_N", OracleType.Numb er)
command.Connect ion = connection
Try
connection.Open ()
command.Paramet ers("@CASE_LIST _NAME_C").Value = "Test"
command.Paramet ers("@EXPIRY_DT ").Value = Date.Today
command.Paramet ers("@DAYS_TO_E XPIRE_N").Value = 100
command.Paramet ers("@CREATED_D T").Value = Date.Today
command.Paramet ers("@CREATED_B Y_N").Value = 2
command.Execute NonQuery()

Catch ex As Exception
MessageBox.Show (ex.Message)
End Try
End Using
I get an exception "ORA-01036: illegal variable name/number "

Can somebody tell me what I am doing wrong?

Thanks.

venki