"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

"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