Today I'm going to share with you some of the methods and experiences of using access parameterized queries
Hope for everyone to have some inspiration, there is a wrong place to hope that the experts to teach a lot
asp.net use OleDbCommand new OleDbParameter to create parameter goods query
ASP uses Command's CreateParameter method to create parameterized query
(SQL stored procedure queries are also established by this method)
asp.net C # syntax
OleDbParameter parm = new OleDbParameter (Name, Type, Direction, Size, Value);
(In fact, it has seven overloads, and you can see it in vs.net.)
Parameters
Name Optional, string that represents the Parameter object name.
Type optional, long integer value specifying the Parameter object data type.
Direction optional, long integer value specifying the Parameter object type ...
Size optional, Long value specifying the maximum length of the parameter value in characters or bytes.
Value optional, Variant type, specifying the values of the Parameter object.
Here's an example of all the News Tsing published
Copy Code code as follows:
Sql= "SELECT * from Newss where username=?" ORDER BY ID "
Note that the condition of the query is indicated by a number.
OleDbConnection conn = new OleDbConnection (connstring);
OleDbCommand cmd = new OleDbCommand (sql,conn);
OleDbParameter parm = new OleDbParameter ("temp", OleDbType.VarChar, 50);
Temp is a parameter object that can be arbitrarily defined, OleDbType.VarChar specified as a string, length 50
Parm. Direction = ParameterDirection.Input;
Specify 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)
Parameter ditto
Here's an example of all the News Tsing published
------------------------------------------------------
Copy Code code 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
The same as the above basically different local method is ASP in the expression of the parameters of the different
129 for adchar,1 is to indicate the input parameter (is actually the default value)
Please refer to Microsoft's Adovb.inc:
Copy Code code 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
I write the C # class, and VBScript function, I hope to be helpful
Copy Code code as follows:
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 Statement </param>
<param name= "Total" > bytes </param>
Public Accselect (string strsql, int total)
{
sql = strSQL;
t = total;
}
<summary>
Constructors
</summary>
<param name= "strSQL" > parameter query Statement </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;
}
}
}
}
Call method
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 ();
Copy Code code as follows:
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
' Invoke 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
