asp code for upload data

Question

hello everyone i have this code for uploading an excel file and save the data into database.I m not able to write the code for database entry. someone please help

<%
if (Request("FileName") <> "") Then
Dim objUpload, lngLoop
Response.Write(server.MapPath("."))
If Request.TotalBytes > 0 Then
 Set objUpload = New vbsUpload

  For lngLoop = 0 to objUpload.Files.Count - 1
    'If accessing this page annonymously,
    'the internet guest account must have
    'write permission to the path below.
    objUpload.Files.Item(lngLoop).Save "D:\PrismUpdated\prism_latest\Prism\uploadxl\"

 Response.Write "File Uploaded"
 Next

 Dim FSYSObj, folderObj, process_folder
 process_folder = server.MapPath(".") & "\uploadxl"
 set FSYSObj =  server.CreateObject("Scripting.FileSystemObject")
 set folderObj =  FSYSObj.GetFolder(process_folder)

 set filCollection = folderObj.Files

 Dim SQLStr 
 SQLStr = "INSERT ALL INTO TABLENAME "
 for each file in filCollection
   file_name =  file.name 
    path = folderObj & "\" & file_name
    Set objExcel_chk = CreateObject("Excel.Application")
    Set ws1 = objExcel_chk.Workbooks.Open(path).Sheets(1)
     row_cnt = 1


      'for row_cnt = 6 to 7
      ' if ws1.Cells(row_cnt,col_cnt).Value <> "" then
      '  col = col_cnt 
      ' end if
      'next 
      While (ws1.Cells(row_cnt, 1).Value <> "") 
      for col_cnt = 1 to 10     

       SQLStr = SQLStr & "VALUES('" & ws1.Cells(row_cnt, 1).Value & "')"
      next
      row_cnt = row_cnt + 1
      WEnd


     'objExcel_chk.Quit
     objExcel_chk.Workbooks.Close()
     set ws1 = nothing
     objExcel_chk.Quit

     Response.Write(SQLStr)

      'set filobj = FSYSObj.GetFile (sub_fol_path & "\" & file_name)
      'filobj.Delete

    next
End if
End If

plz tell me how to save the following excel data to the oracle databse.any help would be appreciated

Answer 1

The basic idea of what you want to do is:

Fetch the data out of your spreadsheet row by row and for each row, execute an insert statement to the database. You probably can get by without the insert all syntax you are currently trying to use.

Your SQL should be in the format:
insert into <tablename> (<column_name1>, <column_name2>) values (<value1>, <value2>)

Your code is trying to generate the string for the SQL statement but doesn't ever do the insert. You should break it down and output the SQL string you are generating to make sure it is correct.

Here is an example of doing an insert to an Oracle database in classic ASP:
http://home.wlv.ac.uk/~cm1958/TextVersion/OracleASP/InsertingData.html

It references a getDBConnection function that you will have to change to be the code that uses your connection string for the database and creates a connection object.