• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

ASP timing out on push from Excel to SQL

I have an asp that is timing out as a result of my code.  Can anyone critique this?  The code takes a excel file on the server and reads it into a SQL table.

This works fine:
Dim fs, tmpFile, oExcelConn, adoExcelRS, xSQL, mSQL, sConn

tmpFile = Application("SITE_PATH") & "\uploads\import.xls"

Set oExcelConn = Server.CreateObject("ADODB.Connection")
Set adoExcelRS = Server.CreateObject("ADODB.Recordset")
Set sConn = Server.CreateObject("ADODB.Connection")

oExcelConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
                        "DBQ=" & tmpFile & ";"
xSQL = "Select * from [Sheet1$]"

adoExcelRS.open xSQL, oExcelConn, sConn
While not adoExcelRS.eof
      mSQL = "insert into tblUser(firstName, lastName)" &_
      "Values("& adoExcelRS("FirstName") &", "& adoExcelRS("LastName") &")"
                sConn.Execute(mSQL)
          adoExcelRS.moveNext
WEnd
0
sl1nger
Asked:
sl1nger
  • 10
  • 7
  • 6
  • +1
2 Solutions
 
sl1ngerAuthor Commented:
Actually, it doesn't work fine:
0
 
peh803Commented:
this looks a bit fishy:

adoExcelRS.open xSQL, oExcelConn, sConn

perhaps it should be something like this:
adoExcelRS.open xSQL, oExcelConn, 1, 3, 1

Basically, you should only include one connection object in your rs.open statement.  Here, you only want to grab the records out of the excel file, so that's the only connection you need to reference.

When you to this:
sConn.execute(mSQL)

You will be referencing the other data source you've defined, and everyone should be happy :)

HTH,
peh803
0
 
fritz_the_blankCommented:
I think that you do need two connections: one for the Excel file and another for the SQL table. I don't see, however, where you are setting the properties for your connections to do that.

FtB
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sl1ngerAuthor Commented:
Yes.. you're probably correct.  I also found a small error in the sql query.  I didn't put '' around my strings on insert.
I'll be back after I make the changes.
0
 
peh803Commented:
>>I think that you do need two connections: one for the Excel file and another for the SQL table.

Sorry for the misinformation here; obviously I haven't done this kind of thing before and I would have approached it differently (and probably less efficiently).  As always, I continue to learn!

peh803
0
 
fritz_the_blankCommented:
It is really too bad that we couldn't use only one connection or else it would be possilble to use the SELECT INTO method; that would allow us to do this in one step!


FtB
0
 
fritz_the_blankCommented:
The only other thing that I could think of here is that the connection to Excel will never  be very good or robust. I wonder if it might be smarter to use the .GetRows() method to dump the excel data into an array. That way, you can close the excel file quickly and then iterate through the array which might be substantially faster.

FtB
0
 
sl1ngerAuthor Commented:
ok..

This works, however; I need to write only one record per email.  Basically, an if statement -  checking to see if we already have the email address in the SQL table before adding a new record. How do you think the If comparison should be?

This example works:

Dim fs, tmpFile, oExcelConn, adoExcelRS, xSQL, mSQL
                                                
tmpFile = Application("SITE_PATH") & "\uploads\import.xls"
Set oExcelConn = Server.CreateObject("ADODB.Connection")
Set adoExcelRS = Server.CreateObject("ADODB.Recordset")
                        
oExcelConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
                          "DBQ=" & tmpFile & ";"
xSQL = "Select * from [Sheet1$]"
            
adoExcelRS.open xSQL, oExcelConn
While not adoExcelRS.eof
  mSQL = "insert into tblUsers(firstName, lastName, email)" &_                                          "Values('"& adoExcelRS("FirstName") &"', '"& adoExcelRS("LastName") &"', '"& adoExcelRS("email") &"'')"
   Call execSQL(mSQL, adoConnection)
adoExcelRS.moveNext
WEnd
0
 
sl1ngerAuthor Commented:
Somehow that wrapped -

Dim fs, tmpFile, oExcelConn, adoExcelRS, xSQL, mSQL
                                       
tmpFile = Application("SITE_PATH") & "\uploads\import.xls"
Set oExcelConn = Server.CreateObject("ADODB.Connection")
Set adoExcelRS = Server.CreateObject("ADODB.Recordset")
                   
oExcelConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
                          "DBQ=" & tmpFile & ";"
xSQL = "Select * from [Sheet1$]"
         
adoExcelRS.open xSQL, oExcelConn
While not adoExcelRS.eof
  mSQL = "insert into tblUsers(firstName, lastName, email)" &_                                  
               "Values('"& adoExcelRS("FirstName") &"', '"& adoExcelRS("LastName") &"', '"& adoExcelRS("email") &"'')"
   Call execSQL(mSQL, adoConnection)
adoExcelRS.moveNext
WEnd
0
 
fritz_the_blankCommented:
I have a solution to that. Hang on for a minute.

FtB
0
 
fritz_the_blankCommented:
Like this?

Dim fs, tmpFile, oExcelConn, adoExcelRS, xSQL, mSQL,objRS, strSQL
                                       
tmpFile = Application("SITE_PATH") & "\uploads\import.xls"
Set oExcelConn = Server.CreateObject("ADODB.Connection")
Set adoExcelRS = Server.CreateObject("ADODB.Recordset")
Set objRS = Server.CreateObject("ADODB.Recordset")
                   
oExcelConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
                          "DBQ=" & tmpFile & ";"
xSQL = "Select * from [Sheet1$]"
         
adoExcelRS.open xSQL, oExcelConn
While not adoExcelRS.eof
      strSQL = "SELECT firstName, lastName, email FROM tblUsers WHERE email = '" & adoExcelRS("email") & "'"
      objRS.Open strSQL, adoConnection,3,3
      if objRS.EOF then
            objRS.AddNew()
                  objRS("firstName")  = adoExcelRS("FirstName")
                  objRS("lastName")  = adoExcelRS("lastName")
                  objRS("email")  = adoExcelRS("email")
            objRS.Update()
      end if
      objRS.Close()
adoExcelRS.moveNext
WEnd
0
 
sl1ngerAuthor Commented:
That's what I was thinking... I'll give it a try.
0
 
fritz_the_blankCommented:
Okay, good luck with that.

FtB
0
 
peh803Commented:
okay, so the consensus is only one connection per open statement?  Just wondering...?

Thanks,
peh803
0
 
sl1ngerAuthor Commented:
fritz - I'm still working on your solution.  When I run it as is, the script times out.
0
 
fritz_the_blankCommented:
@peh803--

One connection object per data source, i.e. one for the Excel file, the other for the database.

@sl1nger:

How many items are there in the Excel file? Do you have the range set correctly? Finally, just for troubleshooting, why don't you start by just printing out the Excel items on the page to make certain that the first part of this problem works the way that it is supposed. to.

FtB
0
 
peh803Commented:
Yeah, that's what I thought the solution would have been...

That's what I thought this particular statement looked strange:
  adoExcelRS.open xSQL, oExcelConn, sConn

There are two connections referenced in 1 recordset open statement, no?  That's what I thought was strange -- not the fact that you needed two connections...

Certanily, when you're copying data from one data source to another, I understand that you need two connections! :)  But my initial point was that the above code was probably problematic.  No matter either way; I guess I just wasn't as far off with it as I thought.

Thanks,
peh803
0
 
fritz_the_blankCommented:
Sorry, I missed that. I was distracted  by the other issues. Nonetheless, you are absolutely correct--you can't have two connection objects on a single recordset.

FtB
0
 
peh803Commented:
No problem at all; there are plenty of issues for distraction, so I definitely know the feeling.

At any rate, it seems that a solution is close, so I'll but out and let you take care of polishing it off :)

Regards,
peh803
0
 
fritz_the_blankCommented:
I am not so sure that it is close at all as the time out issue is still happening. Perhapas it is because of the error that you are talking about (although I didn't duplicate that error on the code sample that I provided above).

FtB
0
 
peh803Commented:
Yeah -- I liked your idea of using getRows() / iterating through the array rather than maintaining a connection to the excel file, which will likely never be that great...
0
 
mk_bCommented:
For timeouts place this

Server.ScriptTimeOut = 1000 '--seconds = 16 min
0
 
sl1ngerAuthor Commented:
My bad..  I haven't been back to this question in some time.  I did resolve the script time out issue.  It was user error on my coding example.  Fritz your answer rocked and  peh appreciate your comments.
0
 
fritz_the_blankCommented:
Glad to have helped,

FtB
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now