1

I have following code:

Dim executedCmd As OleDb.OleDbCommand = m_dbMgr.GetCommand()
executedCmd.CommandText = "select * from [Parameters] where "
Dim SQLcondition As String = String.Empty
For i As Integer = 0 To ParameterName.Count - 1
 executedCmd.CommandText += "ParameterName = @parametername" + i.ToString() + " and ParameterValue @ParamaterCondition" + i.ToString() + " @ParameterValue" + i.ToString()
 executedCmd.Parameters.AddWithValue("@parametername" + i.ToString(), ParameterName(i))
 executedCmd.Parameters.AddWithValue("@ParameterValue" + i.ToString(), ParameterValue(i))
 executedCmd.Parameters.AddWithValue("@ParamaterCondition" + i.ToString(), Condition(i))
Next

ParameterName, ParameterValue, ParameterCondition all are same length ArrayList, but the code does not work properly. I have verified all the variables have values.

When I run the code it reports a syntax error: "missing operations in query expression"

The problem is that ParameterCondition has values like ('>', '<', '=',.... some logical SQL operators).

Edit: How can I include conditions in parameters?

Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
Nest
  • 341
  • 4
  • 23

2 Answers2

1

Add 1 = 1 after where to simplify building logical expression. Notice that all conditions added by AND logical operator. You can generate parameter name from columns name.

executedCmd.CommandText = "select * from [Parameters] where 1 = 1"

....
executedCmd.CommandText += " AND " + ParameterName(i) + " " + Condition(i) + " @" + ParameterName(i)
executedCmd.Parameters.AddWithValue("@" + ParameterName(i), ParameterValue(i))

ParameterCondition must all be binary operators.

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
0

The database engine would check the syntax of the SQL statement before it replaces the parameters with values. So somethinhg like "WHERE @p1 @p2 @p3" will not be parsed correctly. Replace your condition with a string-expression e.g.

commandtext = parameterName + condition + " @p1"
Stephan Keller
  • 1,623
  • 11
  • 13