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, Jan 08 '22 at 11:26

score 1 · Answer 1 · answered Jan 08 '22 at 12:19

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 08 '22 at 12:19

MT0

143,790
11
59
117

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 Jan 09 '22 at 04:35
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 Jan 09 '22 at 04:41
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 – sid Jan 09 '22 at 04:42
`ORDER BY {:OrderBy} {:OrderAs}` is certainly wrong syntax! – Wernfried Domscheit Jan 14 '22 at 21:28

score 0 · Answer 2 · answered Jan 08 '22 at 16:05

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 08 '22 at 16:05

Wernfried Domscheit

54,457
9
76
110

@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 Jan 14 '22 at 19:25
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. – sid Jan 14 '22 at 19:29
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)? – Wernfried Domscheit Jan 14 '22 at 21:26
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. – sid Jan 17 '22 at 04:04
Then it would be `Param1.Value = "%" & AccountGroup.Trim & "%"` – Wernfried Domscheit Jan 17 '22 at 06:24

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

2 Answers2