Parameter transfer for Combined Query of Data room Charging System

In my data room charging system, the most I studied was the Combined Query, because at the first time, I did not make such a Combined Query, but saved some time for simple query. In this article, I want to talk about the problem of passing parameters.

At the beginning, I used SQL statements to pass parameters. The SQL statements were written in this way (basicstudentinfo. filename = cmbfieldname. selectindex, which dynamically combines the query field and the index of the drop-down box ):

"select * from CardInfo,StudentInfowhere crdstudentno=stdstudentno and @FileName1" & _                        basicStudnetInfo1.strOperator & "@value1 " & " " _& basicStudnetInfo1.strRelation & " " & "@FileName2" & basicStudentInfo2.strOperator & "@value2" & " " _& basicStudentInfo2.strRelation & " " & "@FileName3" & basicStudentInfo3.strOperator & "@value3"

 

Here, basicstudentinfo is the class I defined. When I pass the parameter, I pass @ filename to the basicstudentinfo. filename. tostring type, which is char type. However, if the problem arises, the results cannot be found. Then, I removed the @ filename parameter and directly used the concatenated string to directly write basicstudentinfo. filename. tostring. Then the result is displayed. At the beginning, I couldn't figure out the results, and then I put them first.

"select *from CardInfo,StudentInfo where crdstudentno=stdstudentno and " & _ basicStudnetInfo1.fieldName.ToString & basicStudnetInfo1.strOperator & "@value1 " & " " _& basicStudnetInfo1.strRelation & " " & basicStudentInfo2.fieldName.ToString & basicStudentInfo2.strOperator & "@value2" & " " _& basicStudentInfo2.strRelation & " " & basicStudentInfo3.fieldName.ToString & basicStudentInfo3.strOperator & "@value3"

 

 

When I was reading English the next day, I suddenly thought that the field names in this table should be written in SQL statements like stdstudentno, But if I pass parameters like this, then the field name to be queried will be changed to 'stdstudentno', that is, 'stdstudentno' = 'Card No. ', which leads to the issue that I cannot find the result.

Yang Yuan said that passing in the table name as a parameter will lead to the same problem. Then I consulted him.

The solution is as follows: after receiving these parameters in the stored procedure, define a temporary variable of the character type in the stored procedure and splice these strings in the stored procedure. If so, you can use this stored procedure for the four combined queries. That is, when passing parameters, you can upload another table name.

The above is the parameter passing problem that I encountered during the combined query.

In general, I used the null value judgment on the interface in the Combined Query. I only used the selection judgment statement in addition to the relation judgment statement. In the case of a small example, the selection judgment statement is conducive to expansion, therefore, when selecting a judgment statement, I always try to avoid it first, and write it first when I cannot think of it.

I believe in a word that everyone has a bitter life. So every time you think of a simple method, you will encounter other problems. For example, when there is only one condition, I must check the and relationship of the three conditions. So there is no simple method, only the most suitable one.