Parameterized queries for access

See also many people on the forum asked about the security issues that access was injecting
The way many people solve the problem is to replace special characters with replace, but it doesn't work too much.
Today I'm going to share with you some of the ways and experience of using access parameterization queries
Hope to have some inspiration, there is a wrong to write the place hope that the experts more advice

ASP. NET uses OleDbCommand's new OleDbParameter to create a parameter cargo query
ASP uses Command's CreateParameter method to create parameterized queries
(SQL stored procedure query is also built using this method)

ASP C # syntax

OleDbParameter parm = new OleDbParameter (Name, Type, Direction, Size, Value);
(Actually it has seven overloads that everyone can see in vs.net)
Parameters
Name Optional, string that represents the Parameter object name.
Type optional, long integer value that specifies the Parameter object data type.
Direction optional, long integer value, specifying Parameter object Type ...
The Size is an optional, Long value that specifies the maximum length of the parameter value, in characters or bytes.
Value optional, Variant type, specifying the value of the Parameter object.
The following is an example of a news list in which all Tsing are published
-------------------------------------------------------
Sql= "SELECT * from Newss where username=? ORDER BY ID "
Note that the conditions of the query are indicated by the number
OleDbConnection conn = new OleDbConnection (connstring);
OleDbCommand cmd = new OleDbCommand (sql,conn);
OleDbParameter parm = new OleDbParameter ("temp", OleDbType.VarChar, 50);
Temp as Parameter object can be arbitrarily defined, OleDbType.VarChar specified as a string, length 50
Parm. Direction = ParameterDirection.Input;
Specifying its type input parameters
Cmd. Parameters.Add (Parm);
Cmd. parameters["Temp"]. Value = "Tsing";
Query Tsing can also be written as CMD. Parameters[0]
Conn. Open ();
Cmd. ExecuteReader ();

ASP VBScript Syntax

Set parameter = command. CreateParameter (Name, Type, Direction, Size, Value)
Parameters above
The following is an example of a news list in which all Tsing are published
------------------------------------------------------
ET 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 Web Teaching Network

The same as the above basically different local law is the ASP in the expression of the parameters above the different
129 is adchar,1 indicates the input parameter (is actually the default value)
Please refer to Microsoft's Adovb.inc:

"----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

I write the C # class, and VBScript functions, I hope to help everyone

Using System;
Using System.Data;
Using System.Configuration;
Using System.Web;
Using System.Data.OleDb;
Namespace Acc_select
{
<summary>
Summary description of Accselect
</summary>
public class Accselect
{
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\dq\db1.mdb "
private String conn = configurationmanager.connectionstrings["Tsingconnectionstring"]. ToString ();
public String sql = string. Empty;
public int t = 4;
public object v = null;
Public Accselect ()
{
}

<summary>
constructors, passing ACC parameter query statements
</summary>
<param name= "strSQL" >strsql character type </param>
Public Accselect (String strSQL)
{
sql = strSQL;
}
<summary>
constructors, passing ACC parameter query statements
</summary>
<param name= "strSQL" > parameter query Statements </param>
<param name= "Total" > bytes </param>
Public Accselect (String strSQL, int. total)
{
sql = strSQL;
t = total;
}
<summary>
constructor function
</summary>
<param name= "strSQL" > parameter query Statements </param>
<param name= "Total" > bytes </param>
<param name= "value" >object value </param>
Public Accselect (String strSQL, Int. Total, Object value)
{
sql = strSQL;
t = total;
v = value;
}
<summary>
Getodd method returns OleDbDataReader
</summary>
<param name= "ODT" > Define OleDbType Type </param>
<returns></returns>
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;
}
}
}
}
Calling methods
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 
' Call Method
' 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

Reprinted from: http://www.aspnetjia.com/Cont-203.html

Parameterized queries for access