关闭

菜鸟蹒跚学ASP-自动生成Insert语句的VBS

标签: insertfunctionsql数据库joinc
656人阅读 评论(0) 收藏 举报

在Coding的时候,发现最麻烦的是什么啊,是表单提交数据后,往数据库里面插入数据,所以我写了一个自动生成Insert语句的VBS,感觉很好用。节省大量时间啊。


' ----- ExeScript Options Begin -----
' ScriptType: window
' DestDirectory: temp
' Icon: default
' ----- ExeScript Options End -----
set cn = CreateObject("ADODB.Connection")
cnstr = "这里写对应的数据库的链接字符串"
cn.open cnstr
strInput = InputBox("请输入需要查询的表格名称")
sql = "Select a.name,a.length,b.name from syscolumns a, systypes b,sysobjects c where a.xtype=b.xtype and a.id = c.id and c.name = " & Quote(strInput)
set rs= CreateObject("ADODB.RecordSet")
rs.open sql,cn,1,3
set fso = CreateObject("Scripting.FileSystemObject")
set objShell=CreateObject("WScript.Shell")
set f = fso.OpenTextFile(objShell.SpecialFolders.Item("桌面") & "字段名.txt",2,true)

'msgbox objShell.SpecialFolders.Item("Desktop") & "字段名.txt"
Redim ss(rs.RecordCount)
for i = 0 to rs.RecordCount -1
 ss(i) = Bracket(rs(0))
 rs.MoveNext
next
sql = "INSERT INTO " & strInput & """" & " & _"& vbCrlf
Fields = join(ss,",")
Fields =  left(Fields,len(fields) - 1)
Fields = Quote2(" ( " & Fields & ")")
sql = sql & Fields & " & _" & vbCrlf & """" & " VALUES (  "
sql = Quote2(sql) & " & "
Fields = ""
for i = 0 to ubound(ss) -1
 if i <> ubound(ss) - 1 then
  Fields = Fields & "str" + ss(i) & " & " & Quote2(",") &  " & "
 else
  Fields = Fields & "str" + ss(i) & " & "
 end if
next

sql = sql & Fields & Quote2(")")
f.Write sql & vbCrlf
for i = 0 to ubound(ss) - 1
 f.WriteLine "str" & ss(i) & " = "
next
f.close
rs.close
cn.close
objShell.Run "Notepad.exe " & objShell.SpecialFolders.Item("桌面") & "字段名.txt"


function Quote(str)
Quote = "'" & str & "'"
end function
function Quote2(str)
 Quote2 = """" & str & """"
end function


function Bracket(str)
Bracket = "[" & str & "]"
end function 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:247733次
    • 积分:3378
    • 等级:
    • 排名:第9906名
    • 原创:79篇
    • 转载:43篇
    • 译文:0篇
    • 评论:30条
    最新评论