Getting ORA-01036: illegal variable name/number error on select query vb.net code

Question

I am getting below error while executing a select statement on vb.net for oracle. I am trying to pass account number as a parameter to the select statement . Below is the SQL statement

    Dim strSearchSQL As String
    strSearchSQL = "SELECT A.ACCT_GRP_CD, A.AVG_METH_CD , B.AVG_METH_DSCR, A.ACCT_GRP_DSCR,  A.COST_POOL_CD, A.ABC_MULT_NUM, A.INTRM_METH_CD, A.RATE_POOL_RPT_CD FROM GL_ACCT_GRP_TB A, GL_AVG_METH_TB B WHERE A.AVG_METH_CD = B.AVG_METH_CD"

    If Not AccountGroup = "" Then
        strSearchSQL = strSearchSQL & " AND ACCT_GRP_CD LIKE '%{: AccountGroup}%' "
    End If
    If Not OrderBy = "" And Not OrderAs = "" Then
        strSearchSQL = strSearchSQL & " ORDER BY " & OrderBy & " " & OrderAs
    Else
        strSearchSQL = strSearchSQL & " ORDER BY ACCT_GRP_CD XYZ "
    End If

    Dim cmd As New OracleCommand
    cmd.CommandText = strSearchSQL
    cmd.CommandType = CommandType.Text
    cmd.Connection = Connection.GetConnection
    
    cmd.Parameters.Clear()
    Dim Param1 As OracleParameter = New OracleParameter("AccountGroup", OracleType.Char, 500)
    Param1.Direction = ParameterDirection.Input
    Param1.Value = AccountGroup.Trim
    cmd.Parameters.Add(Param1)

and Below is the error that I am getting

Error : ORA-01036: illegal variable name/number

Please help me to identify where exactly I am making mistake

It looks like the parameter (AccountGroup) is optional in your code, but adding it is mandatory — JayV
– JayV, Commented Jan 8, 2022 at 11:26

MT0 · Accepted Answer · 2022-01-08 12:19:46Z

1

You only want to add the AccountGroup parameter if it is present in the SQL:

cmd.Parameters.Clear()
If Not AccountGroup = "" Then
  Dim Param1 As OracleParameter = New OracleParameter("AccountGroup", OracleType.Char, 500)
  Param1.Direction = ParameterDirection.Input
  Param1.Value = AccountGroup.Trim
  cmd.Parameters.Add(Param1)
End If

answered Jan 8, 2022 at 12:19

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sid Over a year ago

Thanks a lot , its working for me now. same way i tired to change OrderBy and OrderAs also but getting error like " ora-00936 missing expression". could you please help me on this please.

sid Over a year ago

Below is the changes : If Not OrderBy = "" And Not OrderAs = "" Then strSearchSQL = strSearchSQL & " ORDER BY {:OrderBy} {:OrderAs} " Else strSearchSQL = strSearchSQL & " ORDER BY ACCT_GRP_CD ASC " End If

sid Over a year ago

If Not OrderBy = "" And Not OrderAs = "" Then Dim Param2 As OracleParameter = New OracleParameter("OrderBy", OracleType.VarChar) Param2.Direction = ParameterDirection.Input Param2.Value = OrderBy cmd.Parameters.Add(Param2) Dim Param3 As OracleParameter = New OracleParameter("OrderAs", OracleType.VarChar) Param3.Direction = ParameterDirection.Input Param3.Value = OrderAs cmd.Parameters.Add(Param3) End If

Wernfried Domscheit Over a year ago

ORDER BY {:OrderBy} {:OrderAs} is certainly wrong syntax!

Wernfried Domscheit · Accepted Answer · 2022-01-08 16:05:13Z

0

Try it like this:

Dim strSearchSQL As String

Dim cmd As New OracleCommand
cmd.CommandType = CommandType.Text
cmd.Connection = Connection.GetConnection

strSearchSQL = "SELECT A.ACCT_GRP_CD, A.AVG_METH_CD , B.AVG_METH_DSCR, A.ACCT_GRP_DSCR,  A.COST_POOL_CD, A.ABC_MULT_NUM, A.INTRM_METH_CD, A.RATE_POOL_RPT_CD "
strSearchSQL = strSearchSQL & " FROM GL_ACCT_GRP_TB A"
strSearchSQL = strSearchSQL & " JOIN GL_AVG_METH_TB B ON A.AVG_METH_CD = B.AVG_METH_CD " ' -> Modern join syntax

If Not AccountGroup = "" Then
    strSearchSQL = strSearchSQL & " WHERE ACCT_GRP_CD LIKE :AccountGroup "
    Dim Param1 As OracleParameter = New OracleParameter("AccountGroup", OracleType.VarChar, 500)
    Param1.Direction = ParameterDirection.Input 
    ' ParameterDirection.Input and OracleType.VarChar are default, you could skip them
    Param1.Value = "%{" & AccountGroup.Trim & "}%"
    cmd.Parameters.Add(Param1)    
End If

If Not OrderBy = "" And Not OrderAs = "" Then
    strSearchSQL = strSearchSQL & " ORDER BY " & OrderBy & " " & OrderAs
Else
    strSearchSQL = strSearchSQL & " ORDER BY ACCT_GRP_CD XYZ "
End If

cmd.CommandText = strSearchSQL

answered Jan 8, 2022 at 16:05

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

5 Comments

sid Over a year ago

@MT0 : What is the right way to declare below statement. i am getting issue for that , strSearchSQL = strSearchSQL & " AND ACCT_GRP_CD LIKE '%{: AccountGroup}%' " Seems LIKE is not executing here properly. Please suggest what is the right way to pass the parameter here.

sid Over a year ago

Thanks for you time. This code is working but I am having issue in LIKE statement . seems it not working the way it should work. its not accepting % matches . Please suggest if we can modify any different way so the LIKE key word will work with % sign with parameter.

Wernfried Domscheit Over a year ago

What do you mean by "is not working"? Your code is different to my. BTW, what is the purpose for curly brackets {}? Are you really looking for some text{AccountGroup}some text (with curly brackets)?

sid Over a year ago

I here trying to get all the records by passing any part of account group. The way 'LIKE' statement work. If I am changing my code to ACCT_GRP_CD LIKE :AccountGroup then I am getting the records associate with that particular account group. Ex : if account group is " USAMD2022" . I want if I am passing value as MD then this one or any account group is having "MD" should retrieve. But for ACCT_GRP_CD LIKE :AccountGroup I have to pass complete account group to get the " USAMD2022". Please suggest how to solve this.

Wernfried Domscheit Over a year ago

Then it would be Param1.Value = "%" & AccountGroup.Trim & "%"

Collectives™ on Stack Overflow

Getting ORA-01036: illegal variable name/number error on select query vb.net code

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related