Skill 3, Database search techniques
1082
In a database search, if we only use the equals symbol to search "Pro", we cannot extract those records that match "ASP Pro". Here's how to use SQL's like operator to implement partial text search.
Ask:
When you extract a recordset from a database, is it possible to extract records that contain more content than the text I specified, and do not have specific requirements for where the search text will appear?
For example:
StrName = ' Direct '
Rsshops = Server.CreateObject ("ADODB"). Recordset ")
strSQL = "SELECT * from shops WHERE Name =
' & StrName & '
Can I extract the recordset that contains the "direct" word in the store name?
For:
Instead of using the equals ("=") operator in the SQL command, use the LIKE operator rather instead.
To extract records that contain "direct" in those names, you can use the following query command:
SELECT * from shops WHERE Name like '%direct% '
Please follow the code here to modify the query that is actually used.
4, the numerical display format
One of the most disappointing changes in the ASP for VB programmers is that there is no longer a format function in VBScript. So, how do you format a number so that it no longer appears in the default format? For example, display numbers as percentages, currency amounts, or plus decimal placeholders. The following code helps you solve this problem and tells you how to format the output by replacing the FORMATXXXX function in VB format.
Ask:
In the application I developed, a page will display some ####.### #格式的数字. How can you implement it in ASP?
For:
Using the FormatNumber function of VBScript, this function returns a formatted numeric expression. The syntax is as follows:
FormatNumber (Expression [, Numdigitsafterdecimal][,includeleadingdigit
][,useparensfornegativenumbers][,groupdigits]]]
For example, FormatNumber ("123.45", 4) will return 123.4500.
Please refer to Http://msdn.microsoft.com/scripting's VBScript Help for more information.
5, the SQL command in the quotation mark processing
Although the SQL command constructed with VBScript code is not itself a problem, the legitimate SQL statement is no longer valid because the form data entered by the user contains either single or double quotes. I believe many readers have already encountered this problem. The next thing we want to do is explain how to solve this kind of problem.
Ask:
I have a question about SQL Server update, insert Record command. I want to update the database with a string with one or more quotes, and SQL takes the quotation marks in the string as grammatical symbols and prompts for errors. What should I do? Is there another string delimiter? I've tried double quotes, [], {}, and so on, but it's no use.
For:
Before executing the SQL command, you should replace the single quotes in all character data with two single quotes.
That is, if a quoted string appears in the SQL command (for example, the user's name is O ' Brian), the problem occurs:
Update Table Set NameField = ' O ' Brian '
To resolve the problem, you must convert the single quotation mark in the string to two single quotes:
Update Table Set NameField = ' O ' ' Brian '
You can use the Replace function to implement this transformation:
StrName = Replace (StrName, "'", "")
