CODEWITHSUNDEEP
oracle-databasevb.netselectoraclecommand
sid
sid

Reputation: 9

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

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

Upvotes: 1

Views: 196

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

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

Upvotes: 0

MT0
MT0

Reputation: 167867

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

Upvotes: 1

Related Questions