An unhandled exception occurs in System. Data. dll. Other information: items to be used

In the data room charging system, some people use the stored procedure in the joint query module. First, I tried to create a view in the database, then, make a judgment on the UI Layer and generate the query condition strCondition. A problem occurs in the joint query module of the Data room charging System: the unhandled exception of the System. Data. SqlClient. SqlException type is in the S

In the data room charging system, some people use the stored procedure in the joint query module. First, I tried to create a view in the database, then, make a judgment on the UI Layer and generate the query condition strCondition. A problem occurs in the joint query module of the Data room charging System: the unhandled exception of the System. Data. SqlClient. SqlException type is in the S

In the data room charging system, some people use the stored procedure in the joint query module. First, I tried to create a view in the database, then, make a judgment on the UI Layer and generate the query condition strCondition.

An error occurred in the "joint query" module of the Data room Charging System: An unhandled exception of the "System. Data. SqlClient. SqlException" type occurs in System. Data. dll. Other information: A non-Boolean expression is specified in the context where conditions should be used (near '@ strCondition.

The DAL-Layer Code for the error is:

Public Function QueryOnLineStatus (ByVal strCondition As String) As List (Of Entity. queryOnLineStatusViewEntity) Implements IDAL. IQueryOnLineStatusView. queryOnLineStatus Dim plain text As String = "select * from QueryOnLineStatus_View where @ strCondition" 'defines the query String (strCondition is the query condition passed by the UI Layer) Dim primitive type As CommandType = CommandType. the Text 'defines the command type Dim sqlHelper As New SqlHelper 'to instantiate the SqlHelper class Dim myList As List (Of Entity. queryOnLineStatusViewEntity) Dim dtb As New DataTable Dim parameters As SqlParameter () parameters = {New SqlParameter ("@ strCondition", strCondition)} dtb = sqlHelper. executeSelect (plain text, struct type, parameters) myList = Entity. entityHelper. convertToList (Of Entity. queryOnLineStatusViewEntity) (dtb) 'entityhelper. convertToList converts the able type to a generic set Return myList End Function

In the code, the strCondition in the SQL statement is a query condition passed from the UI Layer. When this error occurs, it is proved in debugging that the query condition is correct. For example, the strCondition value is: "cardNumber = '1'", so the entire SQL statement is not "select * from QueryOnLineStatus_View where cardNumber = '1!

Finally, the solution to this problem is too unexpected: the difference between "where" and "@ strCondition" in the original SQL statement "select * from QueryOnLineStatus_View where @ strCondition"SpaceRemove the SQL statement and convert it to "select * from QueryOnLineStatus_View where @ strCondition ".

After a while, I tried it many times in the database to find out why ....

First look at the test results in the database:

1. When the where clause in the query statement is followed by a value (assuming the value is 1 ):

(1) If there is no space between where and 1 in the query statement, the query results are correct;

(2) If there is a space between where and 1 in the query statement, an error is returned: A non-Boolean expression is specified in the context of the condition (near '1;

2. Assume that the where clause is followed by a statement in the query statement (assuming that the statement is "1 = 1 "):

(1) There is a space between where and "1 = 1" in the query statement, and the query results are correct;

(2) If there is no space between where and "1 = 1" in the query statement, the following error occurs: A syntax error occurs near '=.

In SQL statements, where is followed by a Boolean value.

【Eye-catching]

After the where clause, if it is only a Boolean value or a Boolean variable, there cannot be spaces between the where clause and the Boolean value (in this case, in my code, @ strCondition is a variable of the Boolean type, but if the where clause is followed by a statement (of course, this statement is also a value of the Boolean type as a whole, for example, "1 = 1"), there must be spaces between this statement and where.