asp+access 读取数据库返回json/jsonp

修改下数据库名称即可使用，包括：列表，增加，修改，删除 四项功能。

json.asp  ----  json化数据

function.asp   ----  数据验证和其他函数

如果需要返回jsonp数据，只需要增加一个 callback 即可

' 输出数据
Response.Write("callback("& json.jsString &")")

// json
$.ajax({
  url: '/api/e.asp',
  data: {
    action: 'list',
    formid: 1001
  },
  type: 'post',
  dataType: 'json',
  success: function(res){
    console.log(res);
  },
  error: function(){
    console.log('ERROR: 出现错误');
  }
})


// jsonp
$.ajax({
  url: '/api/e.asp',
  data: {
    action: 'list',
    formid: 1001
  },
  type: 'post',
  dataType: 'jsonp',
  jsonpCallback: 'callback',
  success: function(res){
    console.log(res);
  },
  error: function(){
    console.log('ERROR: 出现错误');
  }
})

 

<!--#include file="./json.asp" -->
<!--#include file="./function.asp" -->

<%
' 申明文档
Response.ContentType = "text/json"

' 全局变量
dim data
dim json : set json = jsObject()
dim list : set list = jsArray()

' 连接数据库
set conn = Server.CreateObject("adodb.connection")
set rs = Server.CreateObject("adodb.recordset")
DBPath = Server.MapPath("./#data.mdb")
conn.Open "driver={Microsoft Access Driver (*.mdb)}; dbq=" & DBPath

' ------------------------------------------------------
' 读取数据
' ------------------------------------------------------
if Request("action") = "list" then
	formid = Request("formid")
	id = Request("id")

	' 合法性
	if formid <> "" or id <> "" then
		if formid <> "" then
			sql = "select * from feedback where formid = " & formid
		end if
		if id <> "" then
			sql = "select * from feedback where id = " & id
		end if

		rs.open sql,conn,1,3

		' 列表数据
		if (rs.eof or rs.bof) then
			json("code") = 201
			json("count") = 0
			json("msg") = "没有找到相关数据"
		else
			json("code") = 200
			set json("data") = list

			Do while not (rs.eof or rs.bof)
				set list(null) = jsObject()
				for each item in rs.fields
					list(null)(item.Name) = item.Value
				next
				rs.movenext
			Loop

			json("count") = rs.recordcount
			json("msg") = ""
		end if

		' 关闭数据库
		rs.close
		conn.close
		set rs = nothing
		set conn = nothing
	else
		json("code") = 301
		json("msg") = "关键词缺失"
	end if

	' 输出数据
	Response.Write(json.jsString)

' ------------------------------------------------------
' 添加数据
' ------------------------------------------------------
elseif Request("action") = "add" then
	formid = Request("formid")
	name = Request("name")
	phone = Request("phone")
	company = Request("company")
	dateTime = Request("dateTime")
	postUrl = Request("postUrl")
	ip = Request("ip")
	extend = Request("extend")

	' 默认值
	if IsEmpty(dateTime) then
		dateTime = toUnixTime(Now)
	end if
	
	' 合法性
	if blacklist(phone) then
		json("code") = 202
		json("msg") = "手机黑名单"
		json("url") = ""
	elseif not checkPhone(phone) then
		json("code") = 201
		json("msg") = "请输入正确的手机号码"
		json("url") = ""
	else
		' 写入数据
		sql = "select * from feedback"
		rs.open sql,conn,1,3

		rs.addNew
		rs("formid") = formid
		rs("name") = name
		rs("phone") = phone
		rs("company") = company
		rs("dateTime") = dateTime
		rs("postUrl") = postUrl
		rs("ip") = ip
		rs("extend") = extend
		rs.update
		
		json("code") = 200
		json("msg") = "添加成功"
		json("url") = ""

		' 关闭数据库
		rs.close
		conn.close
		set rs = nothing
		set conn = nothing
	end if
	
	' 输出数据
	Response.Write(json.jsString)
	
' ------------------------------------------------------
' 修改数据
' ------------------------------------------------------
elseif Request("action") = "modify" then
	id = Request("id")
	formid = Request("formid")
	name = Request("name")
	phone = Request("phone")
	company = Request("company")
	dateTime = Request("dateTime")
	ip = Request("ip")
	postUrl = Request("postUrl")
	extend = Request("extend")

	' 合法性
	if id <> "" then
		' 更新数据
		sql = "select * from feedback where id = " & id
		rs.open sql,conn,1,3

		if (rs.eof or rs.bof) then
			json("code") = 201
			json("msg") = "未找到数据"
		else
			if formid <> "" then
				rs("formid") = formid
			end if	
			if name <> "" then
				rs("name") = name
			end if
			if phone <> "" then
				rs("phone") = phone
			end if
			if company <> "" then
				rs("company") = company
			end if
			if dateTime <> "" then
				rs("dateTime") = dateTime
			end if
			if postUrl <> "" then
				rs("postUrl") = postUrl
			end if
			if ip <> "" then
				rs("ip") = ip
			end if
			if extend <> "" then
				rs("extend") = extend
			end if

			rs.update

			json("code") = 200
			json("msg") = "修改成功"
		end if

		' 关闭数据库
		rs.close
		conn.close
		set rs = nothing
		set conn = nothing
	else
		json("code") = 300
		json("msg") = "关键词缺失"
	end if

	' 输出数据
	Response.Write(json.jsString)
	
' ------------------------------------------------------
' 删除数据
' ------------------------------------------------------
elseif Request("action") = "delete" then
	id = Request("id")

	' 合法性
	if id <> "" then	
		' 删除数据
		sql = "delete from feedback where id = " & id
		' rs.open sql,conn,1,3
		conn.execute sql

		json("code") = 200
		json("msg") = "删除成功"

		' 关闭数据库
		conn.close
		set rs = nothing
		set conn = nothing
	else
		json("code") = 300
		json("msg") = "关键词缺失"
	end if

	' 输出数据
	Response.Write(json.jsString)
	
end if
%>

<%
' -------------------------------
' 手机号码验证
' checkPhone(15800002222)
' -------------------------------
function checkPhone(str)
	set reg = new RegExp
	reg.Pattern = "^1[3456789]\d{9}$"
	
	if reg.test(str) then
		result = true
	else
		result = false
	end if
	
	checkPhone = result
end function

' -------------------------------
' 手机号码黑名单
' blacklist(15800002222)
' -------------------------------
function blacklist(str)
	dim list
	list = array()

	for i=0 to ubound(list)
		if list(i) = cstr(str) then
			blacklist = true
			exit function
		else
			blacklist = false
		end if
	next
end function

' ----------------------------------
' 标准时间转化为时间戳(东八区)
' toUnixTime(Now)
' toUnixTime("2018-05-05 15:05:00")
' ----------------------------------
function toUnixTime(strTime)
	if IsEmpty(strTime) or not IsDate(strTime) then
		strTime = Now
	end if

	toUnixTime = DateAdd("h", -8, strTime)
	toUnixTime = DateDiff("s", "1970-01-01 00:00:00", toUnixTime)
end function

' ------------------------------------
' 时间戳转化成标准时间(东八区)
' fromUnixTime(1567415514)
' ------------------------------------
function fromUnixTime(intTime)
	if isEmpty(intTime) or not isNumeric(intTime) then
		fromUnixTime = Now()
		exit function
	end if

	fromUnixTime = DateAdd("s", intTime, "1970-01-01 00:00:00")
	fromUnixTime = DateAdd("h", +8, fromUnixTime)
end function

%>

相关下载：

https://github.com/Aithena/asp.api