Stored Procedure | Statement A Content management system written four years ago, applied on the company intranet, the DBA said yesterday that the SQL statement did not use parameterized calls, resulting in heavier server burdens and large resource consumption. It also lists a few resource-intensive statements, similar to the following:
SELECT art_id, Art_title, ... From Usr_news. View_article WHERE art_ispassed= ' Y ' and art_class=4066 order by Art_passtime DESC
The value of the art_class in the WHERE clause is first determined and then combined so that the entire SQL statement is executed through ADO. Depending on the parameter value, a different SQL statement is generated on the server side, and if there are 100,000 values, the server creates a cache for the 100,000 SQL statements. The DBA says to change to ART_CLASS=:V (Oracle database), I write with code similar to the access stored procedure, as follows:
Dim userid
userid=1234
...
Command1.commandtext = "SELECT * from Users where userid=:v"
Command1.Parameters.Append Command1.createparameter (": V",,,, UserID)
Set rs = Command1.execute
The runtime has the following error message at the CreateParameter statement:
Adodb.command (0X800A0BB9)
The parameter type is incorrect, is not within the acceptable range, or conflicts with other parameters.
Dizzy dead, why stored procedure can, direct SQL statement not?
It is wrong to add the omitted parameter.
Tried a few times, not all, so looking for Microsoft technical support, after the turnover finally resolved. The parameters in the SQL statement when accessed through ADO are used "?" No ":" "@", the following is a summary of Microsoft engineers on this issue:
Problem Description:
In ASP, if an ADO operation is called through VBScript, the SQL query is serialized. You follow. NET has a 0x800a0bb9 error in its invocation method.
Solution:
In VB call ADO serialization query, 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
It should be noted that in VBScript, many constants are undefined, such as adinteger, and we need to replace them with specific values. For more information on 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 calling Microsoft.
