--'数据库的连接字符串 -- Access
set dbconnection=Server.CREATEOBJECT("ADODB.CONNECTION")
DBPath = Server.MapPath("customer.mdb")
dbconnection.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
--'数据库的连接字符串 -- Sql server 2000
const connStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=;Initial Catalog=DB_Cy2004;Data Source=."
Dim conn
set conn=server.createobject("ADODB.CONNECTION")
If err<>0 then
err.clear
Else
conn.open connstr
if err then
err.clear
end if
End if
--做单步操作
Con.Execute("DELETE FROM ShopCart WHERE ID = " & Request("ID"))
--返回一个最大值 (即首行首列)
Dim theMaxId
theMaxId = conn.execute("select isnull(max(sId),0) as theMaxId from tSurvey")(0)
--新增操作
Dim sqlSurvey
Dim rsSurvey
sqlSurvey = "select * from tSurvey"
set rsSurvey=server.CreateObject("adodb.recordset")
rsSurvey.Open sqlSurvey,conn,3,3
rsSurvey.AddNew
rsSurvey("sId") = cint(theMaxId) + 1
rsSurvey("CurrentDate") = Request.Form("CurrentDate")
rsSurvey.Update
rsSurvey.Close
conn.Close
--更改操作
1、============
Set rsCartCgi = Server.CreateObject("ADODB.RecordSet")
rsCartCgi.ActiveConnection = Con
rsCartCgi.CursorType = 1
rsCartCgi.LockType = 3
rsCartCgi.Source = "select * from ShopCart where ClientID=" & ClientID & " and WineNo = " & WineNo
rsCartCgi.open()
rsCartCgi("Quantity") = rsCartCgi("Quantity") + abs(Fix(Quantity))
rsCartCgi("Total") = rsCartCgi("Quantity") * rsCartCgi("UnitPrice")
rsCartCgi.Update
===========
2。
Dim sqlSurvey
Dim rsSurvey
sqlSurvey = "select * from tSurvey where id = 5"
set rsSurvey=server.CreateObject("adodb.recordset")
rsSurvey.Open sqlSurvey,conn,3,3
//rsSurvey.AddNew
rsSurvey("sId") = cint(theMaxId) + 1
rsSurvey("CurrentDate") = Request.Form("CurrentDate")
rsSurvey.Update
rsSurvey.Close
conn.Close
===================
3. 查询数据,循环数据
Dim strSql
Dim rsC_summaryShow
Dim strWhere
strWhere = ""
If Trim(request("txtKeyWord")) <> "" Then
strWhere = strWhere & " and userName like '%"&Trim(request("txtKeyWord")) &"%' "
End if
If Trim(request("BeginDate")) <> "" Then
strWhere = strWhere & " and userRegisterTime >= '" & Trim(request("BeginDate")) & "'"
end if
If Trim(request("endDate")) <> "" Then
strWhere = strWhere & " and userRegisterTime <= '" & Trim(request("endDate")) & " 23:55:00'"
end if
If Trim(request("userCheckPassed")) <> "" Then
strWhere = strWhere & " and userCheckPassed = '" & Trim(request("userCheckPassed")) & "'"
else
'strWhere = strWhere & " and userCheckPassed = 'WaitPassed'"
end if
strWhere = strWhere & " and roleId = 2 "
strSQL="select * from vUser"
If strWhere <> "" Then
strWhere = Mid(strWhere, 5)
strSQL = strSQL & " where " & strWhere
End if
strSQL = strSQL & " order by userCheckPassed desc, roleName,userName "
'Response.Write(strSQL)
Set rsC_summaryShow=server.CreateObject("adodb.recordset")
rsC_summaryShow.CursorLocation = 3
rsC_summaryShow.Open strSQL,conn,3,3
Dim theCount
Dim sum
sum=0
Do until rsC_summaryShow.eof
theCount = theCount + 1
。。。。。
rsC_summaryShow.MoveNext
Loop
' 获取最大Id + 1
Function getNewID(TableName,theID,conn)
dim ntID
set rsNewID=server.CreateObject("Adodb.recordset")
Dim theMaxId
Dim theSql
theSql = " select isnull(max(" & theID & "),0) as theMaxId from " & TableName
theMaxId = conn.execute(theSql)(0)
getNewID = theMaxId + 1
End Function
================================
==================================================ASP.Net
protected System.Web.UI.WebControls.DataGrid DataGrid1;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
this.RunByDataSet();
}
private void RunByDataReader()
{
SqlConnection conn = DB.getConn();
conn.Open();
SqlCommand cmd = new SqlCommand("select * from employees",conn);
SqlDataReader sdr = cmd.ExecuteReader();
this.DataGrid1.DataSource = sdr;
this.DataGrid1.DataBind();
}
private void RunByDataSet()
{
SqlConnection conn = DB.getConn();
SqlDataAdapter sda = new SqlDataAdapter("select * from employees",conn);
DataSet ds = new DataSet();
sda.Fill(ds,"tEmployee");
this.DataGrid1.DataSource = ds.Tables[0].DefaultView;
this.DataGrid1.DataBind();
}
/*===============================================Begin
<appSettings>
<add key = "connStr" value="server=.;database=northwind;uId=sa;Pwd="></add>
</appSettings>
**************/
//DB.Class
public static SqlConnection getConn()
{
string conStr = System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();
SqlConnection conn = new SqlConnection(conStr);
return conn;
}
//=============================================== End