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
sl1ngerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.