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

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.