ASP代码用的 *** 作access数据库命令语法大全哪里有

《ASP编程大全》在此谁敢不从！

1.提取字段列表

//<%

Set cn=Server.CreateObject("ADODB.Connection")

cn.Open "Provider=SQLOLEDBData Source=localhost" &_

"Initial Catalog=pubsUser ID=saPassword="

Set rs=cn.Execute("Select * from authors")

While Not rs.EOF

Response.Write rs("au_fname")

rs.MoveNext

Wend

rs.Close

cn.Close

//%>

2.返回数据集函数

Public Function GetData() As ADODB.Recordset

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Set cn=CreateObject("ADODB.Connection")

cn.Open "Provider=SQLOLEDBData Source=localhost" &_

"Initial Catalog=pubsUser ID=saPassword="

Set rs=CreateObject("ADODB.Connection")

rs.LockType=adLockBatchOptimistic

rs.CursorLocation=adUseClient

rs.CursorType=adOpenStatic

rs.Open "Select * from authors",cn

Set rs.ActiveConnection=Nothing

cn.Close

Set cn=Nothing

Set GetData=rs

End Function

3.从XML文件中读取数据

<!-- Metadata type="typelib" file="c:\program files\common files\system\ado\msado15.dll"-->

<%

'response.write admoderead &" " &adopenstreamfromurl

'response.End()

set rs = server.CreateObject("adodb.recordset")

Set stmData = Server.CreateObject("ADODB.Stream")

stmData.Charset="gb2312"'这一句很重要，否则会报一个错误。Recordset cannot be created from the specified source. The source file or stream must contain Recordset data in XML or ADTG format.移山祝大家好运^_^

xmbm = request.QueryString("xmbm")

if xmbm = "" then xmbm = "110100001"

Dim result

dim xml, XMLlength

Set DCOMServer = CreateObject("RemObjects.SDK.COMServer")

DCOMServer.MessageType = "TROBINMessage"

DCOMServer.ChannelType = "TROWinInetHTTPChannel"

DCOMServer.SetChannelProperty "TargetURL", "http://192.168.1.119:8089/BIN"

Set DCOMService = DCOMServer.CreateService("DCOMService")

xml = cstr("<?xml version="&chr(34)&"1.0"&chr(34)&" encoding="&chr(34)&"gb2312"&chr(34)&" ?>" &chr(13)&chr(10))

xml = xml &cstr(DCOMService.GetItem(xmbm))'用于生成XML主体的DCOM服务，实际中请灵活运用。

Set MegaDemoService = nothing

'response.write xml

'response.End()

stmData.Open

stmData.WriteText xml

stmData.SetEOS

'stmData.SaveToFile server.MapPath("zzzz"&session.SessionID&".xml"),2

'response.End()

stmData.Position = 0

rs.open stmData

if not rs.eof then

response.write "<table border = 1 cellpadding=5 cellspacing=0 bordercolor=black style='border-collapse:collapse'><tr bgcolor='#ddddeee'>"

for each fld in rs.fields

response.write "<td>" &fld.name &"</td>"

next

response.write "</tr>"

do while not rs.eof

response.write "<tr>"

for each fld in rs.fields

response.write "<td>" &fld.value &"</td>"

next

response.write "</tr>"

rs.movenext

loop

end if

rs.close

set rs = nothing

%>

自动显示RecordSet的表格

<script language=jscript runat=server>

// ************************************************************************

// Script Compont Object Model

// Design for Active Server Pages

//

// Copyright 2003 Version 1.0

// Made by 尹曙光

// ************************************************************************

//用于显示一个recordset中的数据,可以指定颜色和样式

//接口函数

function createJDBList(){

var objJDBlist=new JDBlist

objJDBlist.create()

return objJDBlist

}

//类体定义

function JDBlist(){

this.AdoRecordSet

this.Width

this.Border

this.Cellspacing

this.Cellpadding

this.Class

this.TitleClass

this.FirstColor

this.SecondColor

this.create=_JDBlist_Create

this.display=_JDBlist_Display

}

//方法实现

function _JDBlist_Create(){

this.AdoRecordSet=""

this.Width="100%"

this.Border="1"

this.Cellspacing="0"

this.Cellpadding="1"

this.Class=""

this.TitleClass=''

this.FirstColor=''

this.SecondColor=''

}

function _JDBlist_Display(){

var htmlDSO = ''

var nFields = this.AdoRecordSet.Fields.Count

if (nFields)

{

var n = 0

htmlDSO += '<' + 'table width="'+this.Width+'" id="'+this.Id+'" Cellspacing="'+this.Cellspacing+'" Cellpadding="'+this.Cellpadding+'" Border="'+this.Border+'" Class="'+this.Class+'">\n'

htmlDSO += '<' + 'tr>'

for (var i=0i <nFieldsi++)

{

htmlDSO += '<' + 'td class="'+this.TitleClass+'">' + this.AdoRecordSet(i).name + '<' + '/td>'

}

htmlDSO += '<' + '/tr>\n'

if (!(this.AdoRecordSet.EOF&&this.AdoRecordSet.Bof))

this.AdoRecordSet.MoveFirst()

else

htmlDSO += '<' + 'td colspan="'+nFields+'" align="center">没有数据！<' + '/td>'

var rcc=0

while (!this.AdoRecordSet.EOF)

{ // add each record to table

if (rcc%2==0)

htmlDSO += '<' + 'tr bgcolor="'+this.FirstColor+'">'

else

htmlDSO += '<' + 'tr bgcolor="'+this.SecondColor+'">'

for (var i=0i <nFieldsi++)

{

htmlDSO += '<' + 'td >' + this.AdoRecordSet(i).value + '<' + '/td>'

}

htmlDSO += '<' + '/tr>\n'

this.AdoRecordSet.MoveNext()

rcc++

}

htmlDSO += '<' + '/table>'

}

Response.Write(htmlDSO)

}

</script>

4.把数据写入XML文件

<%

class autoXML

Private xmldoc

Private xmlfile

Sub Class_Initialize()

set xmldoc = Server.CreateObject("Microsoft.XMLDOM")

xmldoc.async = fasle

End Sub

Sub Class_Terminate()

set xmldoc = Nothing

End Sub

Public Property Let filePath(str)

xmlfile = str

End Property

Sub getRs(rs)

dim xmlhead

dim pNode,sNode

dim aNode,tNode

dim tableName

dim rf

dim i

set xmlhead = xmldoc.createProcessingInstruction("xml","version=""1.0"" encoding=""GB2312""")

xmldoc.insertBefore xmlhead,xmldoc.childNodes(0)

tableName = split(rs.Source,chr(32))

set pNode = xmldoc.CreateElement(tableName(3))

xmldoc.appendChild pNode

redim aNode(rs.Fields.Count-1)

do while not rs.eof

set sNode = xmldoc.CreateElement(tableName(3)&"list")

pNode.appendChild sNode

for i=0 to rs.Fields.Count-1

if rs.Fields(i).Name=tableName(3) then

tNode = rs.Fields(i).Name&"Child"

else

tNode = rs.Fields(i).Name

end if

set aNode(i) = xmldoc.CreateElement(tNode)

sNode.appendChild aNode(i)

if rs.Fields(i).value<>"" then

aNode(i).text = rs.Fields(i).value

end if

next

rs.movenext

loop

End Sub

Function saveFile()

on error resume next

xmldoc.save xmlfile

saveFile = (err.number=0)

End Function

End Class

%>

调用，把调用recordset就可以了。

<%

dim conn

set conn = Server.CreateObject("adodb.connection")

conn.open "Provider=Microsoft.jet.oledb.4.0data source="&Server.MapPath("db.mdb")

dim rs,sql

set rs = Server.CreateObject("adodb.recordset")

sql = "select * from news"

rs.Open sql,conn,3,2

dim ixml

dim xf

set ixml = new autoXML

xf = "test.xml"

ixml.filePath = Server.MapPath(xf)

ixml.getRs(rs)

if ixml.saveFile() then

response.write "<a href=""" &Server.URLEncode(xf) &""">保存完毕</a>"

else

response.write "保存文件错误"

end if

set ixml = Nothing

rs.close

set rs = Nothing

%>

4.1

方法二：

<%

dim strConn,conn,cmd,xmldoc

dim strSql,strQuery,stm

strTableName="test1"

strConn="Provider=SQLOLEDB.1Persist Security Info=FalseUser ID=saInitial Catalog=testData Source=SHISMpassword="

set conn=server.CreateObject("adodb.connection")

conn.CursorLocation = 3

conn.Open strConn

function getXml(strQuery,conn)

dim xmldoc,cmd,stm

set cmd=server.CreateObject("adodb.command")

cmd.ActiveConnection=conn

strQuery="<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>" & strQuery & "</sql:query></ROOT>"

set stm=server.CreateObject("adodb.stream")

stm.Open

stm.WriteText strQuery,0

stm.Position=0

cmd.CommandStream=stm

cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

set xmldoc=server.CreateObject("msxml2.domdocument")

cmd.Properties("Output Stream") = xmldoc

cmd.Execute ,,1024

getXml=xmldoc.xml

set cmd=nothing

set xmldoc=nothing

set stm=nothing

end function

strQuery="select * from test1 for xml auto"

Response.Write getXml(strQuery,conn)

%>

4.2

<script language="jscript" runat="server">

var conn = new ActiveXObject("adodb.connection")

conn.open("Provider=Microsoft.jet.oledb.4.0data source="+Server.MapPath("db.mdb"))

var sql = "select distinct province from allcity"

var rs = conn.Execute(sql)

var xmldoc = new ActiveXObject("Microsoft.XMLDOM")

xmldoc.async = false

var xmlhead = xmldoc.createProcessingInstruction('xml','version="1.0" encoding="GB2312"')

xmldoc.insertBefore(xmlhead,xmldoc.childNodes(0))

var root = xmldoc.createElement("root")

xmldoc.appendChild(root)

do{

var province = xmldoc.createElement("province")

province.text = rs("province")

root.appendChild(province)

var osql = "select distinct city from allcity where province='"+rs("province")+"'"

var ors = conn.Execute(osql)

do{

var city = xmldoc.createElement("city")

city.text = ors("city")

province.appendChild(city)

var mysql = "select college from allcity where city='"+ors("city")+"'"

var myrs = conn.Execute(mysql)

do{

var college = xmldoc.createElement("college")

college.text = myrs("college")

city.appendChild(college)

myrs.movenext()

}while(!(myrs.eof||myrs.bof))

myrs.Close()

ors.movenext()

}while(!(ors.eof||ors.bof))

ors.Close()

rs.movenext()

}while(!(rs.eof||rs.bof))

xmldoc.save(Server.MapPath("allcity.xml"))

Response.Write("创建成功！")

</script>

5.遍历XML文档

<%@ Language=VBScript %>

<% Option Explicit %>

<table border="1"><tr><th>Name</th><th>Number of Songs</th></tr>

<%

Dim objParser,objRoot,objNode,i

Set objParser=Server.CreateObject("MSXML.DOMDocument")

objParser.Load(Server.MapPath(%%1)) //"albums.xml"

Set objRoot=objParser.documentElement

For i=0 To (objRoot.childNodes.length-1)

If objRoot.childNodes.Item(i).baseName="album" Then

Set objNode=objRoot.childNodes.Item(i)

Response.Write "<tr><td>"

Response.Write objNode.getAttribute("name") &"</td><td align='center'>"

Response.Write objNode.childNodes.length

End if

Next

%>

</td></tr>

</table>

6.搜索XML元素

<%@ Language=VBScript %>

<% Option Explicit %>

//<%

Dim objDocument,objRoot,objNode,objNodeList

Set objDocument=Server.CreateObject("MSXML.DOMDocument")

objDocument.Load(Server.MapPath(%%1)) //"albums.xml"

Set objRoot=objDocument.docmentElement

Set objNodeList=objRoot.SelectNodes("//ablum[@artist='" &Request.Form("artist") &"']")

If objNodeList.length=0 Then

Response.Write "No matches"

Else

Response.Write "<table border='1'><tr><th>Album</th><th>Year</th></tr>"

For each objNode in objNodeList

Response.Write "<tr><td>" &objNode.getAttribute("name")

Response.Write "</td><td>" &objNode.getAttribute("releaseDate") &"</td></tr>"

Next

Response.Write "</table>"

End If

End If

//%>

7.下载文件

<%@ Language=VBScript %>

<% Option Explicit %>

//<%

call downloadFile(replace(Request("file"),"",""))

Function downloadFile(strFile)

' make sure you are on the latest MDAC version for this to work

' get full path of specified file

strFilename = server.MapPath(strFile)

' clear the buffer

Response.Buffer = True

Response.Clear

' create stream

Set s = Server.CreateObject("ADODB.Stream")

s.Open

' Set as binary

s.Type = 1

' load in the file

on error resume next

' check the file exists

Set fso = Server.CreateObject("Scripting.FileSystemObject")

if not fso.FileExists(strFilename) then

Response.Write("<h1>Error:</h1>" &strFilename &" does not exist<p>")

Response.End

end if

' get length of file

Set f = fso.GetFile(strFilename)

intFilelength = f.size

s.LoadFromFile(strFilename)

if err then

Response.Write("<h1>Error: </h1>" &err.Description &"<p>")

Response.End

end if

' send the headers to the users browser

Response.AddHeader "Content-Disposition", "attachmentfilename=" &f.name

Response.AddHeader "Content-Length", intFilelength

Response.CharSet = "UTF-8"

Response.ContentType = "application/octet-stream"

' output the file to the browser

Response.BinaryWrite s.Read

Response.Flush

' tidy up

s.Close

Set s = Nothing

End Function

//%>

8.调用存储过程查询

<%@ Language=VBScript %>

<% Option Explicit %>

//<%

dim db,conn,myconn

db="/database/product.mdb"

Set conn = Server.CreateObject("ADODB.Connection")

myconn="Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &Server.MapPath(""&db&"")

conn.Open MyConn

dim rs

dim sql

dim keyword

dim choose

keyword=request("keyword") '接收页面传送的值

choose=request("choose")

set rs=server.createobject("adodb.recordset")

sql="exec tse" '用exec执行tse存储过程，把keyword，choose给存储过程传递参数

rs.open sql,conn,1,1

if rs.eof and rs.bof then

response.write("没有任何记录！")

response.end

end if

response.write"搜索到的记录如下:<br><br>"

do until rs.eof

response.write""&rs("id")&":"&rs("title")&"" '打印出文章的ID和标题

response.write"<br><br>"

rs.movenext

loop

'打扫战场

rs.close

conn.close

set rs=nothing

set conn = nothing

//%>

9.参数存储过程查询

'存储过程：

'在SQL中建立dbo.tse存储过程

CREATE PROCEDURE [dbo].[tse]

@keyword varchar(20)=null, '定义查询的关键字

@choose int=null'定义查询的类型（1为查询列title,其他为content）

as

if @choose=1

select * from web where title like @keyword + '%'

else

select * from web where content like @keyword + '%'

return

GO

'list.asp页

<%@ Language=VBScript %>

<% Option Explicit %>

//<%

dim db,conn,myconn

db="/database/product.mdb"

Set conn = Server.CreateObject("ADODB.Connection")

myconn="Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &Server.MapPath(""&db&"")

conn.Open MyConn

dim rs

dim sql

dim keyword

dim choose

keyword=request("keyword") '接收页面传送的值

choose=request("choose")

set rs=server.createobject("adodb.recordset")

sql="exec tse '"&keyword&"',"&choose&"" '用exec执行tse存储过程，把keyword，choose给存储过程传递参数

rs.open sql,conn,1,1

if rs.eof and rs.bof then

response.write("没有任何记录！")

response.end

end if

response.write"搜索到的记录如下:<br><br>"

do until rs.eof

response.write""&rs("id")&":"&rs("title")&"" '打印出文章的ID和标题

response.write"<br><br>"

rs.movenext

loop

'打扫战场

rs.close

conn.close

set rs=nothing

set conn = nothing

//%>

1.首先双击打开数据库。

2.点击头部导航：创建-->查询设计-->关闭d出的窗口-->点击左侧的“SQL”-->进行sql命令。

3.首先查询整张表的数据。输入sql语句：select * from news 。

输sql语句后，点击左上角的红色“！”号执行语句。

会发现，表中的所有数据都被查询出来。

4.如果只想查询news表中的“title”字段为“SQL语句的运用“那又该如何写，这时就要用到sql语句的条件语句也就是where语句。

输入语句 select * from news where title='SQL语句的运用'

这样title为“SQL语句的运用“的这条数据就出来了

（其中title='SQL语句的运用' 的单引号一定是要在英文下的单引号，而不是中文下的单引号，切记！）。

5.如果只想查询news表中的前2条数据。

输入语句 select  top 2 * from news 。

这样一来前2条数据就会被查询出来。

6.熟练的运用sql语句可以简单快捷的对数据库进行 *** 作，有效的提高工作效率。

找开企业管理器 先在sql里建一数据库 选择所有任务 导入数据 选择数据源中选microsoft access 在把access数据库导入到mssql数据库时，应该注意的几个问题 在使用MSSQL SERVER的时候，和ACCESS有很多区别的，即使是用SQL SERVER导入ACCESS数据库，也有很多地方需要注意，这里，捡几个例子来说说。 1、在ACCESS中经常使用的自动编号字段，导入到MSSQL后，他并不是自增型的INT，需要手工设置，把导入后的自动编号字段的标识的“否”改为“是”，“种子”和“递增量”都为“1”，才能成为自动编号； 2、在MSSQL SERVER中，有许多保留字，在ACCESS中是没有的，当你把数据导入到MSSQL的时候，问题就出来了。MSSQL在导入的时候，会自动给这些字段（包括数据库中的表名）加上“[字段名]”，因此，你必须修改你的脚本，把相应的字段名字（或者表名字）加上中括号，或改变字段名字为不是MSSQL的保留字。（具体是那些，请参考有关资料）

---