A content management system written four years ago, applied in the company's internal Internet, yesterday, DBA said that the SQL statement did not use parameterized calls, resulting in aggravation of server burden, and resource occupation. There are several statements that have a large resource, similar to: select art_id, art_title, ... from usr_news.view_article where art_ispassed = 'y' and art_class = 4066 ORDER BY ART_PASSTIME DESC where Art_Class in where clauses in the WHERE clause The value is first determined to combine the entire SQL statement, then execute it through the ADO. Due to different parameter values, different SQL statements are generated at the server, if there is 100,000 values, the server will create a cache for the 100,000 SQL statements. DBA said that it is to be changed to the art_class =: v method (Oracle database), I use a similar code similar to access stored procedure, as follows: DIM UserIDUserid = 1234 ... command1.commandtext = "SELECT * from users where userid =: V "Command1.Parameters.Append Command1.createParameter (": v ",,,,, userid) SET RS = Command1.execute runs at the CreateParameter statement: AdoDb.command (0x800A0BB9) Parameter type is incorrect, or not Can be accepted within the range or conflict with other parameters. Dizzy, why is the stored procedure can, directly SQL statement? Plus the parameters of the omitted parameters or wrong. I have tried it several times, I can't, so I find Microsoft technical support, and I have finally solved several turnover. It turns out that the parameters in the sql statement are used by ADO access. "" No ":" @ ", the following is the summary of Microsoft engineers on this issue:
Problem Description: In ASP, if you call the ADO operation serialization SQL query via VBScript. You follow the 0x800A0BB9 error in the call to the .NET. Solution: In VB, you can refer to the following two articles: how to invoke a parameterized ado query using VBA / C / java http://support.microsoft.com/?id=181734 info: Visual Basic Accessing An Oracle Database Using ADO http://support.microsoft.com/?id=176936 Need to note that in VBScript, many constants are not defined, such as Adinteger, we need to replace specific values. For details of CreateParameter, please refer to: createparameter method http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcreateParam.asp Thank you again for your call Microsoft.
