表C:向数据库中插入数据
<%
on Error resume next
' 设置本脚本可以运行的时间
server.scripttimeout = 1000
' 调入一个带定界符的文本文件，参数如下
' dbName – 数据库名称
' tableName – 表名称
' delimiter – 分离域的定界字符
' sourceText – 实际的带定界符的文本文件
' descriptors – 含有目标表中各个域的说明信息的文件
' errorLog – 存放错误日志的文件
' tCon – 与数据导入到的表的连接
function loadDelimitedTextFile( dbName, _
tableName, delimiter, sourceText, _
descriptors, errorLog, tCon)
recsLoaded = 0
totalRecs = 0
' 保存描述文件中的信息
dim descInfo()
' SQL insert语句的开始和结束部分
' 注意我们创建的语句把所有域都插
' 入到新的记录中
iStart = "insert into " & tableName & "values (" _iEnd = ")"
' 设置使用的数据库
' 并非所有的数据库系统都需要这一参数
' 因此，如果没有dbName参数，我们忽略这一步骤。
if (dbName <> "") then
tCon.execute("use " & dbName)end if
' 打开数据库描述文件
set descFile = server.createObject _
( "Scripting.FileSystemObject")
descPath = server.mappath( descriptors)
set descData = descFile.OpenTextFile ( descPath)
' 文件首行表明需要读取的域的个数
numFields = cInt(descData.readline)
' 下面是域的信息
' 第一列是名称
' 第二列是类型
' 第三列是允许的长度
reDim descInfo( numFields - 1, 2)
' 调入变量的名称极其类型Load the variable names and their types.
for x = 0 to numFields - 1
thisLine = descData.readline
descInfo( x, 0) = trim(left(thisLine, _
inStr(thisLine, ",") - 1))
thisLine = trim(mid(thisLine, _
inStr(thisLine, ",") + 1))
descInfo( x, 1) = trim(left(thisLine, _
inStr(thisLine, ",") - 1))
descInfo( x, 2) = cInt(trim(mid(thisLine, _
inStr(thisLine, ",") + 1)))
' response.write("name: " & descInfo(x,0) & ", Type: " _& descInfo(x,1) & ", Size: " & descInfo(x,2) & "<BR>")
next
' 创建错误日志文件
set errorFile = server.createObject _
( "Scripting.FileSystemObject")
errorPath = server.mappath( errorLog)
set errorData = errorFile.CreateTextFile _
( errorPath, true)
' 打开数据文件
set sourceFile = server.createObject _
( "Scripting.FileSystemObject")
sourcePath = server.mappath( sourceText)
set sourceData = sourceFile.OpenTextFile _
( sourcePath)
' 开始调入数据
response.write( "<font color=red size=+1>Now _
loading " & sourcePath & "<BR></font>")
do while sourceData.atendofstream = false
' 读入一行并且再日志中记录
' 可能的错误
thisLine = sourceData.readline
errLogLine = thisLine
' 清除insert语句并且
' 寻找下一个定界符
iStr = ""
nextDelimiter = inStr( thisLine, delimiter)
if nextDelimiter = 0 then
nextDelimiter = len(thisLine)
end if
' 将thisLine插入到insert语句中
for x = 0 to (numFields - 1)
' 获取数据值。注意我们将所有单引号替换为
' 两个单引号，以便进行SQL命令的解析（使用
' 单引号分割字符串）
thisData = left( thisLine, nextDelimiter)
thisData = replace(thisData, "''", "")
' 下面是可以添加特殊情况下的转换代码的地方
' 比如，如果一个域是数字型的，
' 而所导入的数据写成了"One"、"Three"或者"Five"
' 可以将这些值转换为"1", "3"或者"5"
if (thisData = "") then
' special case null values
iStr = iStr & "NULL, "
else
' response.write( thisData & "<BR>")
select case descInfo(x, 1)
case "n"
' 此处应是一个数字值
if not isNumeric(thisData) then
errorData.writeLine ( errLogLine)
errorData.writeLine _
( " Field '" & descInfo(x, 0) _
& "' contains an invalid number: '"
& thisData & "'")
thisData = "NULL"
end if
iStr = iStr & thisData & ", "
case "c"
' 此处应是一个字符串
' 注意检查长度
if len(thisData) > descInfo(x, 2) then
errorData.writeLine ( errLogLine)
errorData.writeLine _
( " Field '" & descInfo(x, 0) _
& "' contains a sting that is too long _
(max allowed is " & descInfo(x, 2) _
& "): '" & thisData & "'")
thisData = left( thisData, _
descInfo(x, 2))
end if
iStr = iStr & "'" & thisData & "', "
case "d"
' 这里应是一个日期域
if not isDate(thisData) then
iStr = iStr & "NULL, "
errorData.writeLine ( errLogLine)
errorData.writeLine _
( " Field '" & descInfo(x, 0) _
& "' contains an invalid date: '" _
& thisData & "'")
else
iStr = iStr & "'" & thisData & "', "
end if
case else
' 有错误
errorData.writeLine( errLogLine)
errorData.writeLine _
( " Field '" & descInfo(x, 0) _
& "' is an unknown type: '" _
& thisData & "'")
end select
end if
' 截断其余数据
thisLine = mid( thisLine, nextDelimiter + 1)
nextDelimiter = inStr ( thisLine, delimiter)
if nextDelimiter = 0 then
nextDelimiter = len(thisLine)
end if
next
' 将我们创建的字符串的最后两个字符删去
iStr = left(iStr, len(iStr) - 2)
' response.write( iStart & iStr & iEnd & "<p>")
' 将数据加入表中
tCon.errors.clear
tCon.execute(iStart & iStr & iEnd)
' 输出错误行
if tCon.errors.count > 0 then
errorData.writeLine( errLogLine)
for j = 0 to tCon.errors.count - 1
errorData.writeLine (tCon.errors(j).description)
next
else
recsLoaded = recsLoaded + 1
end if
totalRecs = totalRecs + 1
loop
response.write( "<P><font color=red size=+1>" & _
recsLoaded & " of " & totalRecs & _
" records loaded. Check error logs for details on _
bad records.<BR></font>")
loadDelimitedTextFile = recsLoaded
end function
' 建立到服务器的连接
Set mainCon = Server.CreateObject ( "ADODB.Connection")
mainCon.Open "driver={SQL Server};uid=sa;pwd=;SERVER=NT2"numRecs = loadDelimitedTextFile ( "Articles", "People", chr(9), _
"people.txt", "fieldInfo.txt","errors.txt", mainCon)
%>
