ASP / Asp.Net 常用操作总结

--'数据库的连接字符串  --  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
--更改操作
 １、＝＝＝＝＝＝＝＝＝＝＝＝
  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
 　　＝＝＝＝＝＝＝＝＝＝＝
 ２。 
 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

 

 

 

 ' 获取最大Ｉｄ　＋　１
 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