'/ ********************************************************** ********************************** / '/ * Excel data certificate import program * /' / * 2003-6-13 Yinxiang www.ykce.com * / '/ ******************************************************* ************************************* / '/ * DATAINTOSQLSERVER_CERITIFICATE (STRFILENAME, STRSHEETNAME, MyConn, strKind) * /' / * Parameter Description: * / '/ * STRFILENAME --XLS file name * /' / * strsheetname " *************************************************** ********************* / sub dataIntoSqlServer_ceritificate (strFileName, strSheetName, myConn, strKind) 'defines dim myConnection dim strName dim rsXsl, rsSql dim str_Xsl, str_Sql dim myConn_Xsl dim CMD DIM I, J DIM STRKMID 'ID number DIM MAXID DIM MAXORDERID DIM MAXKM DIM STR_DATE DIM STR_KIND
strName = strFileName set myConnection = server.createobject ( "adodb.connection") set rsXsl = Server.Createobject ( "ADODB.Recordset") set rsSql = Server.CreateObject ( "ADODB.Recordset") set cmd = Server.CreateObject ( " Adodb.command ") SET cmd.activeconnection = myconn 'certificate type str_kind = split (strKind," - ")' Add to date time STR_DATE = formatdatetime (Date (), 2) &" & time () MyConn_xsl = "provike = Microsoft.Jet.OLEDB.4.0; Data Source = "& strName &"; Extended Properties = Excel 8.0 " 'open connection myconnection.open myConn_Xsl' open table str_Xsl =" select * from [ "& strSheetName &" $] "rsXsl. Open str_xsl, myconnection, 1, 1 '// Name, ID number, certificate number, issuing date, valid date j = 1 do while not rsxsl.eof' '' '' '' '' '' '' '' '' ' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' 'Removes the maximum str_sql = "SELECT MAX (ID) as maxid from ceritificate" rssql.open str_sql, myconn, 1, 3 if not rsSql.eof Then if not isnull (RSSQL ("maxid")) ")") 1 else maxid = 1 END IF ELSE MAXID = 1 end if rssql.close '// Close Object' joining transcripts STR_SQL = "INSERT INTO CERITICATE VALUES (" & Maxid & ", '" & RSXSL (0) & "
',' "& RSXSL (1) &" ',' "& RSXSL (2) &" ',' "& str_kind (0) &" ',' "& rsxsl (3) &" ',' "& rsXSL (4) & "'" "& str_date &") "cmd.commandtext = str_sql cmd.execute ()' '' '' '' '' '' '' '' '' '' '' ' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' J = J 1 RSXSL.MOVENEXT LOOP response.write "" & str_kind (1) & " certificate import success. < BR> "response.write" is imported into "& j &" certificate information.
"Response.write" Close window "SET RSXSL = NOTHING SET RSSQL = Nothing set myconnection = Nothing set cmd = nothing end Sub code description: 1) The above code is imported into SQL Server in SQL Server, strKind The parameter is the type of certificate; 2) Link Excel string: provider = microsoft.jet.OLEDB.4.0; data source = "& strname"; extended prot again = excel 8.0 "3) str_xsl =" select * from ["& strsheetname & "$]" This statement is to determine which one table of Excel, is a table
