The complete code is as follows:
<%
'On error resume next
'Import Excel spreadsheet data to SQL Server database by Dicky 16:41:12
Function open_conn (sqldatabasename, sqlpassword, sqlusername, sqllocalname, sqlconn)
'Create a database connection object and open a database connection
Dim connstr
'SQL Server database connection parameters: Database Name, user password, user name, connection name (local for local use, IP for foreign use)
Connstr = "provider = sqloledb; user id =" & sqlusername & "; Password =" & sqlpassword & "; initial catalog =" & sqldatabasename &"; data Source = "& sqllocalname &";"
Set sqlconn = server. Createobject ("ADODB. Connection ")
Sqlconn. Open connstr
If err then
Err. Clear
Set sqlconn = nothing
Response. Write "data connection error! "
Response. End
End if
End Function
Function Close_Conn (SqlConn)
'Close the database connection and clear the database connection object
If IsObject (SqlConn) Then
SqlConn. Close
Set SqlConn = Nothing
End If
End Function
Call Open_Conn ("Shat_EDG", "", "sa", "(local)", SqlConn) 'to open the local SQL Server database connection
Call Open_Conn ("Shat_EDG", "", "sa", "ssh03", SqlConn1) 'to open a remote SQL Server database connection
Function Get_EMP_CnName (NTACCNT)
'Obtain the Chinese name of the user based on the user's NT account
Dim Sql1, Rs1
Sql1 = "Select EMP_CNAME From RF_EMPLOYEE Where EMP_NTACCNT = '" & NTACCNT &"'"
Set Rs1 = Server. CreateObject ("Adodb. RecordSet ")
Rs1.Open Sql1, SqlConn1, 1, 1
If Rs1.Eof Then
Get_EMP_CnName = ""
Else
Get_EMP_CnName = Rs1 ("EMP_CNAME ")
End If
Rs1.Close
Set Rs1 = Nothing
End Function %>
<Html>
<Head>
<Title> Import Excel spreadsheet data to the SQL Server database </title>
<Body bgcolor = "# ACD9AF">
<Center> <B> Import Excel spreadsheet data to SQL Server database </B> </center>
<Form method = "POST" name = "form1">
Select a data source (local database ):
<Select name = "Table" title = "SELECT the Table to import data">
<Option> </option>
<% Dim RsSqlDatabaseTable
Set RsSqlDatabaseTable = SqlConn. OpenSchema (20)
Do While Not RsSqlDatabaseTable. Eof %>
<Option <% If Trim (Request ("Table") = RsSqlDatabaseTable (2) Then Response. write "selected" % >><%= RsSqlDatabaseTable (2) %> </option>
<% RsSqlDatabaseTable. MoveNext: Loop
Set rssqldatabasetable = nothing %>
</SELECT> <input type = submit name = submit value = "Start Time Output">
<Br> select the target table (remote database ):
<Select name = "Table1" Title = "select the table to import data">
<Option> </option>
<% Dim rssqldatabasetable1
Set rssqldatabasetable1 = sqlconn1.openschema (20)
Do while not rssqldatabasetable1.eof %>
<Option <% if trim (Request ("Table1") = rssqldatabasetable1 (2) then response. write "selected" % >>< % = rssqldatabasetable1 (2) %> </option>
<% Rssqldatabasetable1.movenext: loop
Set rssqldatabasetable1 = nothing %>
</SELECT>
</Form>
<Font color = blue> do not refresh the hosts during the upload process! </Font> <br>
<%
If trim (Request ("Table1") <> "then
Dim SQL, Rs
SQL = "Select * From Sheet1 $" 'sheet1 $ is the name of the table obtained by using the data conversion function of SQL Server.
Set Rs = SqlConn. Execute (SQL)
If Rs. Eof And Rs. Bof Then
Response. write "the data you need is not found !! <Br>"
Else
Do While Not Rs. Eof
SqlInsert = "Insert Into" & Trim (Request ("Table1") & "(EDG_Project_Name, EDG_Project_No, EDG_Project_VM, metadata, EDG_Project_M, edg_project_cnname, edg_project_ctor ctor, role) values ('"& Trim (Rs (" project name ") &"', '"& Trim (Rs (" Project No "))&"', '"& Trim (Rs (" Project Assistant ") &"', '"& Trim (Rs (" Project Assistant ")) & "(" & Get_EMP_CnName (Trim (Rs ("Project Assistant") & ")" & "','" & Trim (Rs ("Project Manager ")) & "','" & Trim (Rs ("Project Manager") & "(" & Get_EMP_CnName (Trim (Rs ("Project Manager ")))&") "&" ',' "& Trim (Rs (" Project Director ") &" ',' "& Trim (Rs (" Project Director ")) & "(" & Get_EMP_CnName (Trim (Rs ("Project Director ")))&")"&"')"
'Response. Write sqlinsert
'The inserted value is adjusted based on the actual database structure.
Sqlconn1.execute (sqlinsert) 'insert an Excel table to show all things
Rs. movenext
Loop
Response. Write "<font color = Red> congratulations! data is successfully exported! ^ _ ^ </Font> <br>"
End if
Rs. Close
Set rs = nothing
End if
Call close_conn (sqlconn) 'to close the Excel database connection
Call close_conn (sqlconn1) 'Close SQL Server database connection %>
</Body>
</Html>