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 OracleConnection(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_NAME_C,
@EXPIRY_DT, @DAYS_TO_EXPIRE_N, @CREATED_DT, @CREATED_BY_N)"

Dim command As New OracleCommand
command = New OracleCommand(lsString)
command.CommandType = CommandType.Text
command.Parameters.Add("@CASE_LIST_NAME_C", OracleType.VarChar,
100)
command.Parameters.Add("@EXPIRY_DT", OracleType.DateTime)
command.Parameters.Add("@DAYS_TO_EXPIRE_N", OracleType.Number)
command.Parameters.Add("@CREATED_DT", OracleType.DateTime)
command.Parameters.Add("@CREATED_BY_N", OracleType.Number)
command.Connection = connection
Try
connection.Open()
command.Parameters("@CASE_LIST_NAME_C").Value = "Test"
command.Parameters("@EXPIRY_DT").Value = Date.Today
command.Parameters("@DAYS_TO_EXPIRE_N").Value = 100
command.Parameters("@CREATED_DT").Value = Date.Today
command.Parameters("@CREATED_BY_N").Value = 2
command.ExecuteNonQuery()

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 OracleConnection(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_NAME_C,
@EXPIRY_DT, @DAYS_TO_EXPIRE_N, @CREATED_DT, @CREATED_BY_N)"

Dim command As New OracleCommand
command = New OracleCommand(lsString)
command.CommandType = CommandType.Text
command.Parameters.Add("@CASE_LIST_NAME_C", OracleType.VarChar,
100)
command.Parameters.Add("@EXPIRY_DT", OracleType.DateTime)
command.Parameters.Add("@DAYS_TO_EXPIRE_N", OracleType.Number)
command.Parameters.Add("@CREATED_DT", OracleType.DateTime)
command.Parameters.Add("@CREATED_BY_N", OracleType.Number)
command.Connection = connection
Try
connection.Open()
command.Parameters("@CASE_LIST_NAME_C").Value = "Test"
command.Parameters("@EXPIRY_DT").Value = Date.Today
command.Parameters("@DAYS_TO_EXPIRE_N").Value = 100
command.Parameters("@CREATED_DT").Value = Date.Today
command.Parameters("@CREATED_BY_N").Value = 2
command.ExecuteNonQuery()

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 OracleConnection(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_NAME_C,
@EXPIRY_DT, @DAYS_TO_EXPIRE_N, @CREATED_DT, @CREATED_BY_N)"

Dim command As New OracleCommand
command = New OracleCommand(lsString)
command.CommandType = CommandType.Text
command.Parameters.Add("@CASE_LIST_NAME_C", OracleType.VarChar,
100)
command.Parameters.Add("@EXPIRY_DT", OracleType.DateTime)
command.Parameters.Add("@DAYS_TO_EXPIRE_N", OracleType.Number)
command.Parameters.Add("@CREATED_DT", OracleType.DateTime)
command.Parameters.Add("@CREATED_BY_N", OracleType.Number)
command.Connection = connection
Try
connection.Open()
command.Parameters("@CASE_LIST_NAME_C").Value = "Test"
command.Parameters("@EXPIRY_DT").Value = Date.Today
command.Parameters("@DAYS_TO_EXPIRE_N").Value = 100
command.Parameters("@CREATED_DT").Value = Date.Today
command.Parameters("@CREATED_BY_N").Value = 2
command.ExecuteNonQuery()

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