Parameterized query of ACCESS under asp.net and asp_javascript skills-JS Tutorial-php.cn

Parameterized query of ACCESS under asp.net and asp_javascript skills

WBOY

Release： 2016-05-16 19:03:48

Original

1267 people have browsed it

Today I will share with you some of my methods and experience in using ACCESS parameterized queries
I hope it can inspire you. I hope experts can give me some advice if I have written something wrong

ASP. NET uses OleDbCommand's new OleDbParameter to create parameter queries
ASP uses Command's CreateParameter method to create parameterized queries
(SQL stored procedure queries are also created using this method)

ASP.NET C# syntax
OleDbParameter parm = new OleDbParameter(Name, Type, Direction, Size, Value);
(actually it has seven overloads, you can see them in VS.net)
Parameter
Name is optional, a string representing the Parameter object name.
Type optional, long integer value, specifies the Parameter object data type.
Direction optional, long integer value, specifying the Parameter object type. .
Size optional, long integer value, specifies the maximum length of the parameter value (in characters or bytes).
Value is optional, variant type, specifies the value of the Parameter object.
The following is an example to query all news published by tsing in the news table

Copy the code The code is as follows:

 sql="select * from newss where username=? order by id" 
 //Note that the query conditions are all represented by ? 
 OleDbConnection conn = new OleDbConnection(connString); 
 OleDbCommand cmd = new OleDbCommand(sql,conn); 
 OleDbParameter parm = new OleDbParameter("temp",OleDbType.VarChar, 50); 
 //temp is a Parameter object and can be defined at will, OleDbType.VarChar is specified as a string, Length 50 
 parm.Direction = ParameterDirection.Input; 
 //Specify its type input parameter 
 cmd.Parameters.Add(parm); 
 cmd.Parameters["temp"].Value = " tsing"; 
 //Query tsing, which can also be written as cmd.Parameters[0] 
conn.Open(); 
cmd.ExecuteReader(); 

ASP VBSCRIPT syntax

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)
The parameters are the same as above
The following is an example to query all the news published by tsing in the news table
--- -------------------------------------------------- -

Copy code The code is as follows:

set conn = Server.CreateObject("Adodb.Connection ") 
 conn.ConnectionString = connString 
 conn.open() 
 set mycmd = Server.CreateObject("ADODB.Command") 
 mycmd.ActiveConnection=conn 
 mycmd.CommandText=sql 
 mycmd.Prepared = true 
 set mypar = mycmd.CreateParameter("temp",129,1,50,"tsing") 
 mycmd.Parameters.Append mypar 
 set myrs = mycmd.Execute 

Basically the same as the above, but the difference is that asp expresses parameters differently
129 is adChar, 1 indicates the input parameter (actually the default value)
Please Refer to MICROSOFT's ADOVB.Inc:

Copy code The code is as follows:

 '---- ParameterDirectionEnum Values ----   
 Const adParamUnknown = 0   
 Const adParamInput = 1   
 Const adParamOutput = 2   
 Const adParamInputOutput = 3   
 Const adParamReturnValue = 4   
'---- DataTypeEnum Values ----   
 Const adEmpty = 0   
 Const adTinyInt = 16   
 Const adSmallInt = 2   
 Const adInteger = 3   
 Const adBigInt = 20   
 Const adUnsignedTinyInt = 17   
 Const adUnsignedSmallInt = 18   
 Const adUnsignedInt = 19   
 Const adUnsignedBigInt = 21   
 Const adSingle = 4   
 Const adDouble = 5   
 Const adCurrency = 6   
 Const adDecimal = 14   
 Const adNumeric = 131   
 Const adBoolean = 11   
 Const adError = 10   
 Const adUserDefined = 132   
 Const adVariant = 12   
 Const adIDispatch = 9   
 Const adIUnknown = 13   
 Const adGUID = 72   
 Const adDate = 7   
 Const adDBDate = 133   
 Const adDBTime = 134   
 Const adDBTimeStamp = 135   
 Const adBSTR = 8   
 Const adChar = 129   
 Const adVarChar = 200   
 Const adLongVarChar = 201   
 Const adWChar = 130   
 Const adVarWChar = 202   
 Const adLongVarWChar = 203   
 Const adBinary = 128   
 Const adVarBinary = 204   
 Const adLongVarBinary = 205  

附我写的C#类，和VBSCRIPT函数，希望对大家有帮助

复制代码代码如下:

using System;  
using System.Data;  
using System.Configuration;  
using System.Web;  
using System.Data.OleDb;  
namespace acc_select  
{  
 ///   
 /// accselect 的摘要说明  
 ///   
 public class accselect  
 {  
 //"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:dqdb1.mdb"  
 private string conn = ConfigurationManager.ConnectionStrings["tsingConnectionString"].ToString();  
 public string sql = string.Empty;  
 public int t = 4;  
 public object v = null;  
 public accselect()  
 {  
 }  
 ///   
 /// 构造函数，传递ACC参数查询语句  
 ///   
 /// strsql字符型  
 public accselect(string strsql)  
 {  
 sql = strsql;  
 }  
 ///   
 /// 构造函数，传递ACC参数查询语句  
 ///   
 /// 参数查询语句  
 /// 字节数  
 public accselect(string strsql, int total)  
 {  
 sql = strsql;  
 t = total;  
 }  
 ///   
 /// 构造函数  
 ///   
 /// 参数查询语句  
 /// 字节数  
 /// OBJECT值  
 public accselect(string strsql, int total, object value)  
 {  
 sql = strsql;  
 t = total;  
 v = value;  
 }  
 ///   
 /// getOdd方法返回OleDbDataReader  
 ///   
 /// 定义OleDbType类型  
 ///   
 public OleDbDataReader getOdd(OleDbType odt)  
 {  
 OleDbConnection conns = new OleDbConnection(this.conn);  
 OleDbCommand cmd = new OleDbCommand(this.sql, conns);  
 OleDbParameter parm = new OleDbParameter("temp", odt, this.t);  
 parm.Direction = ParameterDirection.Input;  
 cmd.Parameters.Add(parm);  
 cmd.Parameters[0].Value = this.v;  
 conns.Open();  
 OleDbDataReader oda = cmd.ExecuteReader();  
 cmd.Dispose();  
 return oda;  
 }  
 string Sql  
 {  
 get  
 {  
 return sql;  
 }  
 set  
 {  
 sql = value;  
 }  
 }  
 int T  
 {  
 get  
 {  
 return t;  
 }  
 set  
 {  
 t = value;  
 }  
 }  
 object V  
 {  
 get  
 {  
 return v;  
 }  
 set  
 {  
 v = value;  
 }  
 }  
 }  
}  
//调用方法  
//accselect acc = new accselect();  
//acc.sql = "select * from dtt where d_id=?";  
//acc.t = 10;  
//acc.v = 1;  
//OleDbDataReader oda = acc.getOdd(OleDbType.VarChar);  
//Repeater1.DataSource = oda;  
//Repeater1.DataBind(); 

复制代码代码如下:

function acc_sql(sql,adotype,adodct,strlong,values)  
dim connstring,mycmd,myrs,conn  

 connString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db1.mdb")  
 set conn = Server.CreateObject("Adodb.Connection")  
 conn.ConnectionString = connString  
 conn.open()  
 set mycmd = Server.CreateObject("ADODB.Command")  
 mycmd.ActiveConnection=conn  
 mycmd.CommandText=sql  
 mycmd.Prepared = true  
 set mypar = mycmd.CreateParameter("temp",adotype,adodct,strlong,values)  
 mycmd.Parameters.Append mypar  
 set myrs = mycmd.Execute  
 set acc_sql=myrs   
end function   
'调用方法  
'dim rs  
'sql="select * from users where id=? order by id"  
'set rs=acc_sql(sql,3,1,4,1)  
'if not rs.eof then  
 'response.Write(rs(1))  
'end if 