엑셀파일 읽어서 디비에 인서트 하기

<!-- #include virtual="/admin/common/asp/lib/DBConnectionMgr.asp" -->

<!-- #include virtual="/admin/common/asp/lib/CookieUtil.asp" -->
<!-- #include virtual="/admin/common/asp/lib/PathConfig.asp" -->
<!-- #include virtual="/admin/common/asp/lib/StringUtil.asp" -->
<%

  Dim DirectoryPath,theForm,FileWholePath ,rsE,objRS
  Dim path,pProdImgS,sFile,ConnE,strConn,pSerialmastId

 
  DirectoryPath = Server.MapPath(uploadProductExcel) '파일이 저장될 로컬폴더 경로
  Set theForm = Server.CreateObject("ABCUpload4.XForm")
  theForm.AbsolutePath = True '절대경로 가능하게 함
 
  Set sFile  = theForm("xlsFile")(1)     ' 엑셀파일 object
 

 
  If sFile.FileExists Then
   pProdImgS = sFile.SafeFileName
   FileWholePath = GetUniqueName(pProdImgS, DirectoryPath)
   sFile.Save FileWholePath '실제로 지정된 경로에 파일을 업로드한다.
  End If

  'Response.write DirectoryPath
  'Response.write pProdImgS

  Set ConnE = Server.CreateObject("ADODB.Connection")
 ' strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath&"attach_temp\"&FileName & ";Persist Security Info=False"
  strConn = "Driver={Microsoft Excel Driver (*.xls)}; DBQ="+DirectoryPath+"\"+pProdImgS 'this is the file address
  ConnE.Open strConn
 
  Set objRS = ConnE.OpenSchema(4)
 
 
  'Response.write "11:"&objRS("Table_name")

 
 
  sqlstr="select count(*) from ["+objRS("Table_name")+"]"
  set rsE=ConnE.Execute(sqlstr)
  rscount=rsE(0)
  rsE.close
  set rsE=nothing
 
  sqlstr="select * from ["+objRS("Table_name")+"]"

  objRS.close

  set rsE=ConnE.Execute(sqlstr)
  count=0
 
  DBConnect()     '데이터베이스 연결 설정
  do while not rsE.eof
   count=count+1
   SaveData()
   rsE.movenext
  loop
  DBDisconnect()    '데이터베이스 연결 해제
  rsE.close
  set rsE=nothing
  ConnE.Close
  Set ConnE = nothing

 
%>

<%
'/**************************************************************
'// 저장 처리
'//-------------------------------------------------------------
Sub SaveData()
Dim objConnection, objCommand, objNameParam ,pMode
Dim i, err_no, err_msg,fRetNo,fRetCount
err_msg = "처리중 에러가 발생하였습니다."
Dim errLoop, strError

pMode="I"
'On Error Resume Next ' Error 발생해도 다음 실행.
 
err_no = 0 :fRetNo=0 : fRetCount=0

DbCon.BeginTrans ' Connection 객체의 Transaction 실행.

Set objCommand = Server.CreateObject("ADODB.Command") '  Command 객체 선언 및 생성.

With objCommand
 Set .ActiveConnection=DbCon     '  Command 객체의 DB 연결.
 .CommandText="UDSP_SERIALMAST_SAVE"  
 .CommandType = adCmdStoredProc
 .Prepared = True

 'response.write rsE(1) &"---<br>"
 'response.write pMode &"---<br>"
 ' CommandText에서 지시한 Static SQL의 Parameter 생성.
 Set objNameParam = .CreateParameter("@i_MODE",adBStr,adParamInput,1,pMode)
  .Parameters.Append objNameParam
 Set objNameParam = .CreateParameter("@i_SERIALMASTID",adInteger,adParamInput,,0)
  .Parameters.Append objNameParam
 Set objNameParam = .CreateParameter("@i_PRODCODE",adInteger,adParamInput,,rsE(0))
  .Parameters.Append objNameParam
 Set objNameParam = .CreateParameter("@i_PRODSERIAL",adVarWChar,adParamInput,50,"")
  .Parameters.Append objNameParam
 Set objNameParam = .CreateParameter("@i_CDKEYSERIAL",adVarWChar,adParamInput,50,"")
  .Parameters.Append objNameParam
 Set objNameParam = .CreateParameter("@i_PRODSTATUS",adBStr,adParamInput,1,"I")
  .Parameters.Append objNameParam

 Set objNameParam = .CreateParameter("@i_D_NUM",adInteger,adParamInput,,0)
  .Parameters.Append objNameParam
 
 Set objNameParam = .CreateParameter("@o_SERIALMAST_ID",adInteger,adParamOutput,,0)
  .Parameters.Append objNameParam
 Set objNameParam = .CreateParameter("@o_ERRNO",adInteger,adParamOutput,,0)
  .Parameters.Append objNameParam

 ' 실행
 .Execute
 
 pSerialmastId = .Parameters("@o_SERIALMAST_ID").Value
 
 err_no = .Parameters("@o_ERRNO").Value

 'response.write "pSerialmastId"&pSerialmastId

 'response.write "err_no"&err_no
 
End With
Set objNameParam = Nothing
Set objCommand.ActiveConnection = Nothing
Set objCommand = Nothing  ' Command 객체 해제.

If err_no = 0 Then   '//성공
  'Transaction Commit
  'DbCon.CommitTrans
 
  SaveData_Detail()

 
else
 'Transaction Rollback
 DbCon.RollbackTrans
%>
<script>
 alert("Error No : "+ document.mainForm.err_no.value +" \nDescription : "+ document.mainForm.err_msg.value +"");
</script>
<%
End If
End Sub
'/**************************************************************

Sub SaveData_Detail()
Dim objConnection, objCommand, objNameParam,pgubun
Dim i, err_no, err_msg,fRetNo,fRetCount
err_msg = "처리중 에러가 발생하였습니다."
Dim errLoop, strError
pgubun="I"
err_no = 0 :fRetNo=0 : fRetCount=0


 'response.write "id:"&pSerialmastId
 serialsub_id = 0
 pUnitName  = rsE(1)  ' 단위제품명
 pProdSerial  = rsE(2)  ' 제품번호
 pCdkeySerial = rsE(3) ' CD KEY 일련번호
 pgubun   = "I"    ' 입력 구분.(등록,수정)
 
 Set objCommand = Server.CreateObject("ADODB.Command") '  Command 객체 선언 및 생성.
 
 With objCommand
  Set .ActiveConnection=DbCon     '  Command 객체의 DB 연결.
  .CommandText="UDSP_SERIALDETAIL_SAVE"  
  .CommandType = adCmdStoredProc
  .Prepared = True

  ' CommandText에서 지시한 Static SQL의 Parameter 생성.
  Set objNameParam = .CreateParameter("@i_MODE",adBStr,adParamInput,1,pgubun)
   .Parameters.Append objNameParam
  Set objNameParam = .CreateParameter("@i_SERIALSUBID",adInteger,adParamInput,,serialsub_id)
   .Parameters.Append objNameParam
  Set objNameParam = .CreateParameter("@i_SERIALMASTID",adInteger,adParamInput,,pSerialmastId)
   .Parameters.Append objNameParam
  Set objNameParam = .CreateParameter("@i_UNITNAME",adVarWChar,adParamInput,50,pUnitName)
   .Parameters.Append objNameParam
  Set objNameParam = .CreateParameter("@i_PRODSERIAL",adVarWChar,adParamInput,50,pProdSerial)
   .Parameters.Append objNameParam
  Set objNameParam = .CreateParameter("@i_CDKEYSERIAL",adVarWChar,adParamInput,50,pCdkeySerial)
   .Parameters.Append objNameParam  
  Set objNameParam = .CreateParameter("@o_ERRNO",adInteger,adParamOutput,,0)
   .Parameters.Append objNameParam

  ' 실행
  .Execute  
 
  err_no = .Parameters("@o_ERRNO").Value
 
 End With
 Set objNameParam = Nothing
 Set objCommand.ActiveConnection = Nothing
 Set objCommand = Nothing  ' Command 객체 해제.
 
 


%>
<form name='mainForm' action="ExcelUpload.asp" method='post' ID="Form1">
 <input type='hidden' name='err_no' value='<%=err_no%>' ID="Hidden4">
 <input type='hidden' name='err_msg' value='<%=err_msg%>' ID="Hidden5">
</form>
<%
If err_no = 0 Then   '//성공
  'Transaction Commit
  DbCon.CommitTrans
 Dim msg_txt : msg_txt="정상적으로 저장되었습니다."
 
%>
<script>
 alert("<%=msg_txt%>");
 //document.mainForm.submit();
 window.close();
 //opener.reload();
 opener.location.href='/admin/product/prodKeyMngList.asp';
 
 /*
 if(opener){ //오픈창에서 액션이 이뤄진경우
  opener.document.location.reload();
  this.close();
 }else{  //해당창에서 액션이 이뤄진경우
  document.mainForm.action = "prodKeyMngList.asp";
  document.mainForm.submit();
 }
 */
</script>
<%
Else                   
 'Transaction Rollback
 DbCon.RollbackTrans
%>
<script>
 alert("Error No : "+ document.mainForm.err_no.value +" \nDescription : "+ document.mainForm.err_msg.value +"");
</script>
<%
End If
End Sub
'/**************************************************************

%>