在 vb.net for oracle 上執行 select 陳述句時出現以下錯誤。我正在嘗試將帳號作為引數傳遞給 select 陳述句。下面是SQL陳述句
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)
下面是我得到的錯誤
錯誤：ORA-01036：非法變數名稱/編號
請幫助我確定我在哪里犯了錯誤
uj5u.com熱心網友回復：
如果AccountGroup引數存在于 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
uj5u.com熱心網友回復：
試試這樣：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/406393.html
標籤：