在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