Solved

OraSession Object

Posted on 2003-11-16
5
1,078 Views
Last Modified: 2008-02-01
A user will upload an Excel file and file.asp will parse the excel records and inserts the data in an ORACLE table.  I use the OraSession object that I defined in global.asa (see source codes below). Is there any other way of doing this without using the OraSession object.

--Global.asa
<OBJECT RUNAT=Server SCOPE=Application ID=OraSession PROGID="OracleInProcServer.XOraSession"></OBJECT>

--file.asp
                  Set rec1 = Server.CreateObject("ADODB.Recordset")
                  Set rec2 = Server.CreateObject("ADODB.Recordset")
                  Set rec3 = Server.CreateObject("ADODB.Recordset")
                  Set recC = Server.CreateObject("ADODB.Recordset")
                        
                  dim adoCn
                  dim adoRs
                        
                  Set adoCn = Server.CreateObject("ADODB.Connection")
                  Set adoRs = Server.CreateObject("ADODB.Recordset")

                  strExcelPath = strServer & "\" & strFileDir & "\" & sFilename
                  With adoCn
                        .Provider = "Microsoft.Jet.OLEDB.4.0"
                        .ConnectionString = _
                              "Data Source=" & strExcelPath & ";Extended Properties=Excel 8.0;Persist Security Info=False;"
                        .Open _
                              "Data Source=" & strExcelPath & ";Extended Properties=Excel 8.0;Persist Security Info=False;"
                  End With

                  if sheet_name <> "sheet1" then
                        strQuery = "SELECT * FROM [" & sheet_name & "$] "
                  else
                        strQuery = "SELECT * FROM [Sheet1$] "
                  end if
                        
                  With adoRs
                        Set .ActiveConnection = adoCn
                  End With

                  adoRs.Open strQuery, adoCn, adOpenKeyset

                  MyDb = "devdb01.sin.infineon.com"
                  MyUser = "user/password"
                  MyTb = "str_x_meeting"

                  dim OraDynaset
                        
                  'Create the OraDatabase Object by opening a connection to Oracle.
                  Set OraDatabase = OraSession.OpenDatabase(MyDb, MyUser, CInt(0))

                  'Create the OraDynaset Object.
                  StrSQL = "SELECT * FROM " & MyTb & " "
                  Set OraDynaset = OraDatabase.CreateDynaset(StrSQL, CInt(0))

                  FldCnt = OraDynaset.Fields.Count
                  intcnt = 1

                  sSql = "Select str_meeting_id_seq.nextval m_id from dual"
                  rec1.open sSql, con
                        meeting_id = rec1("m_id")
                  rec1.Close
                        
                  Do Until adoRs.EOF             
                        With OraDynaset
                              .AddNew
                              For Each adoFld in adoRs.Fields
                                    'Assigning value to the recNew Recordset for Insertion
                                    if adoFld.Name = "Issue/Information" then
                                          .Fields("Issue").value = checkUmlauts(adoRS("Issue/Information").value)
                                    elseif adoFld.Name = "Who" then
                                          .Fields("Assignee").value = checkUmlauts(adoRS("Who").value)
                                    elseif adoFld.Name = "Actions" then
                                          .Fields("Action").value = checkUmlauts(adoRS("Actions").value)
                                    elseif adoFld.Name = "Due Date" then
                                          if isdate(adoRS("Due Date").value) then
                                                .Fields("Due_Date").value = adoRS("Due Date").value
                                          else
                                                .Fields("Due_Date").value = ""
                                          end if
                                    else
                                          .Fields(adoFld.Name).value = adoRs(adoFld.Name).value
                                    end if
                              Next
                              .Update
                              sSqlUpd = "Update str_x_meeting set meeting_id =" & meeting_id & ",meeting_type_id=" & meeting_type_id
                              sSqlUpd = sSqlUpd & " ,subject='" & subject & "',meeting_date='" & formatdatetime(meeting_date,1)
                              sSqlUpd = sSqlUpd & "',organiser='" & users_id & "',users_id='" & users_id & "'"
                              if not(subtopic ="" or IsNull(subtopic)) then
                                    sSqlUpd = sSqlUpd & ",subtopic='" & subtopic & "'"
                              end if
                              sSqlUpd = sSqlUpd & ",item_id=" & intcnt & " where meeting_id is null and meeting_type_id is null"
                              cmd.commandtext = sSqlUpd
                              cmd.execute

                              sSql = "select upper(category) category from str_x_meeting where meeting_id=" & meeting_id
                              rec1.open sSql, con
                              if not( rec1.EOF or rec1.BOF) then
                                    catg = rec1("category")
                              end if
                              rec1.Close

                              sSql = "Select category_id from str_t_meeting_catg where meeting_type_id = " & meeting_type_id & " and UPPER(category)='" & ucase(checkUmlauts(catg)) & "'"
                              rec1.open sSql, con
                              if not rec1.EOF then
                                    sSqlUpd = "Update str_x_meeting set category_id = " & rec1("category_id") & " where meeting_id = " & meeting_id
                                    cmd.commandtext = sSqlUpd
                                    cmd.execute
                              end if
                              rec1.Close
                              intcnt = intcnt + 1
                              on error resume next
                        End With
                      'Moving to Next Record
                      adoRs.MoveNext
                  Loop
                  
0
Comment
Question by:shlikjohn
  • 2
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 250 total points
Comment Utility
I think that you can create the object in the page instead:

Dim objOraSession
Set objOraSession=Server.CreateObject("OracleInProcServer.XOraSession")
0
 

Author Comment

by:shlikjohn
Comment Utility

That was my first approach before but I got an error.
And that is the reason why I put the code in global.asa but anyways I will try it again and hope it will not get any error.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Totally unrelated, but are mixing DAO with ADO? And if so, out of curiosity, is that really necessary?

Anthony
0
 

Author Comment

by:shlikjohn
Comment Utility

so how can you reference the worksheet in excel and treat it as a table?

have any good ideas?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>have any good ideas? <<
Not sure if that question was directed at me, but I always have good ideas <g>

>>so how can you reference the worksheet in excel and treat it as a table?<<
I have no problem with the way you are opening the Excel spreadsheet, it is more with the way that you are opening the Oracle database.  I would use ADO, rather than DAO.  i suspect it would be far more efficient. ADO was designed for web based apps, DAO was not.

But again, this has nothing to do with your immediate problem and I would follow GreenGhost's suggestion.

Anthony
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to hide hide popup page refresh second time? 10 79
API not working 33 47
SP to delete duplicates 15 54
ASP Button to clear text 4 15
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now