1.查詢
<html><br /><head><br /><title>表格顯示資料表記錄</title><br /></head><br /><body><br /><h2>表格顯示資料表記錄</h2><br /><hr><br /><mce:script language="JavaScript"><!--<br />// 建立資料庫物件<br />var objdbConn = new ActiveXObject("ADODB.Connection");<br />// DSN字串<br />var strdsn = "Driver={SQL Server};SERVER=.;UID=sa;PWD=haiweisoft;DATABASE=PointCard";<br />// 開啟資料來源<br />objdbConn.Open(strdsn);<br />// 執行SQL的資料庫查詢<br />var objrs = objdbConn.Execute("SELECT * FROM news");//Execute也可以執行增刪改<br />// 擷取欄位數目<br />var fdCount = objrs.Fields.Count - 1;<br />// 檢查是否有記錄<br />if (!objrs.EOF){<br /> document.write("<table border=1><tr>");<br /> // 顯示資料庫的欄位名稱<br /> for (var i=0; i <= fdCount; i++)<br /> document.write("<td><b>" + objrs.Fields(i).Name + "</b></td>");<br /> document.write("</tr>");<br /> // 顯示資料庫內容<br /> while (!objrs.EOF){<br /> document.write("<tr>");<br /> // 顯示每筆記錄的欄位<br /> for (i=0; i <= fdCount; i++)<br /> document.write("<td valign='top'>" + objrs.Fields(i).Value + "</td>");<br /> document.write("</tr>");<br /> objrs.moveNext(); // 移到下一筆記錄<br /> }<br /> document.write("</table>");<br />}<br />else<br /> document.write("資料庫內沒有記錄!<br>");<br />objrs.Close(); // 關閉記錄集合<br />objdbConn.Close(); // 關閉資料庫連結<br />// --></mce:script><br /></body><br /></html><br />
2.增加操作
<HTML><br /><HEAD><br /><TITLE>增加操作</TITLE><br /><mce:script language="javascript"><!--</p><p>function addUser(id,stuName)<br />{<br />//用 JavaScript 寫伺服器端串連資料庫的程式碼範例<br />var conn = new ActiveXObject("ADODB.Connection");<br />conn.Open("DBQ=F://abc.mdb;DRIVER={Microsoft Access Driver (*.mdb)};");<br />var sql="insert into Student(ID,stuName) values("+id+",'"+stuName+"')";<br />try{<br /> conn.execute(sql);<br /> alert("添加成功");<br />}<br />catch(e){<br /> document.write(e.description);<br /> alert("添加失敗~~~");<br />}<br />conn.close();</p><p>}<br />// --></mce:script><br /></HEAD></p><p><BODY><br /><table width=100 border=1><br /> <tr bgcolor='#f4f4f4'><br /> <td>編號</td><br /> <td>姓名</td><br /> </tr><br /> <tr><br /> <td><input id="stuId" /></td><br /> <td><input id="stuName"/></td><br /> </tr><br /></table><br /><input name="1" type="button" value="添加" onclick="addUser(stuId.value,stuName.value)"/><br /></BODY><br /></HTML></p><p>
3.刪除操作
<HTML><br /><HEAD><br /><TITLE>刪除操作</TITLE><br /><mce:script language="javascript"><!--</p><p>function delStu(id)<br />{<br /> var conn = new ActiveXObject("ADODB.Connection");<br /> conn.Open("DBQ=F://abc.mdb;DRIVER={Microsoft Access Driver (*.mdb)};");<br /> var sql="delete from Student where Id=2";<br /> conn.execute(sql);<br /> conn.close();<br /> conn = null;<br /> alert("修改成功");<br />}<br />// --></mce:script><br /></HEAD></p><p><BODY><br /><input name="1" type="button" value="刪除" onclick="delStu(1)"/><br /></BODY><br /></HTML><br />
4.修改操作
<HTML><br /><HEAD><br /><TITLE>修改操作 </TITLE><br /></HEAD><br /><mce:script type="text/javascript"><!--</p><p>function updateUser(userId,userName)<br />{<br /> var conn = new ActiveXObject("ADODB.Connection");<br /> conn.Open("DBQ=F://abc.mdb;DRIVER={Microsoft Access Driver (*.mdb)};");<br /> var rs = new ActiveXObject("ADODB.Recordset");<br /> var sql="update Student set stuName='" + userName + "' where Id=" + userId + "";<br /> conn.execute(sql);<br /> conn.close();<br /> conn = null;<br /> alert("修改成功");<br />}<br />// --></mce:script><br /><BODY><br /> <table width=100 border=1><br /> <tr bgcolor='#f4f4f4'><br /> <td>編號</td><br /> <td>姓名</td><br /> </tr><br /> <tr><br /> <td><input id="stuId" /></td><br /> <td><input id="stuName"/></td><br /> </tr><br /></table><br /><input name="1" type="button" value="修改" onclick="updateUser(stuId.value,stuName.value)"/><br /></BODY><br /></HTML><br />
另外,JS也可以操作SQL Server資料庫
資料庫名為:MySchool,表名為Student,StudentId為int類型,自增列,studentName為學生姓名,為varchar類型。資料庫使用者名稱為sa,密碼是ok。
<HTML><br /><HEAD><br /><TITLE>SQL資料查詢</TITLE><br /><Script ><br />var conn = new ActiveXObject("ADODB.Connection");<br />conn.Open("Driver={SQL server};Server=.;DataBase=MySchool;UID=sa;Password=ok;");//開啟資料庫<br />var rs = new ActiveXObject("ADODB.Recordset");<br />var sql="select * from Student";<br />rs.open(sql, conn);<br />var html="";<br />while(!rs.EOF)<br />{<br /> html=html+rs.Fields("StudentId")+" "+rs.Fields("studentName")+"<br/>";<br /> rs.moveNext();<br />}<br />document.write(html);<br />rs.close();<br />rs = null;<br />conn.close();<br />conn = null;<br /></script><br /></HEAD></p><p><BODY><br /></BODY><br /></HTML><br />
//添加外鍵應用
CREATE TABLE [dbo].[RoleRight](
[RoleRightId] [int] IDENTITY(1,1) NOT NULL,
[sid] [int] NOT NULL,
[qid] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[RoleRightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RoleRight] WITH CHECK ADD CONSTRAINT [fk_2] FOREIGN KEY([sid])
REFERENCES [dbo].[shenfen] ([sid])
GO
ALTER TABLE [dbo].[RoleRight] CHECK CONSTRAINT [fk_2]
GO
ALTER TABLE [dbo].[RoleRight] WITH CHECK ADD CONSTRAINT [fk_3] FOREIGN KEY([qid])
REFERENCES [dbo].[quanxian] ([qid])
GO
ALTER TABLE [dbo].[RoleRight] CHECK CONSTRAINT [fk_3]