ASP timing out on push from Excel to SQL

Actually, it doesn't work fine:

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

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

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.

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

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

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

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

I have a solution to that. Hang on for a minute.

FtB

That's what I was thinking... I'll give it a try.

Okay, good luck with that.

FtB

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

Thanks,
peh803

fritz - I'm still working on your solution.  When I run it as is, the script times out.

@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

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

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

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

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

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...

For timeouts place this

Server.ScriptTimeOut = 1000 '--seconds = 16 min

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.

Glad to have helped,

FtB