Strange Connection Behavior

[dmh4ab]

This is really strange and is confusing me. My asp page connects to a SQL database and pulls recordsets. For this I need a temporary table which I create like this....

strSQL = "SELECT tbl_RepairSummary.BasicPartNumber, tbl_RepairSummary.RoutingCode, tbl_RepairSummary.RevLevel, tbl_RepairSummary.RevDate, tbl_RepairSummary.PartName, tbl_RepairSummary.Summary, tbl_RtgDash.DashNo, tbl_RtgDash.ModToPN, tbl_RtgDash.ModToDash, tbl_RepairSummary.InProcess, tbl_RepairSummary.Filename, tbl_RepairSummary.Inactivated INTO #tmp FROM tbl_RepairSummary LEFT JOIN tbl_RtgDash ON (tbl_RepairSummary.BasicPartNumber = tbl_RtgDash.BasicPartNumber) AND (tbl_RepairSummary.RoutingCode = tbl_RtgDash.RoutingCode) AND (tbl_RepairSummary.RevLevel = tbl_RtgDash.RevLevel) WHERE tbl_RepairSummary.BasicPartNumber = '" & Number & "' OR tbl_RtgDash.ModToPN = '" & Number & "';"
Conn.Execute strSQL

'Number' comes from user input to a text box. This works fine for the page, except that the third time the user types in a number to search for, the page throws an error....
"Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
There is already an object named '#tmp' in the database."

I'm lost. Any help would be appreciated!
Thanks,
dmh4ab

 

[chmohan]

after Conn.Execute strSQL
drop #tmp

 

[chmohan]

oops DROP TABLE #tmp

 

[dmh4ab]

I recieve an error when trying this...

Conn.Execute strSQL
DROP TABLE #tmp

Expected end of statement
/myWeb/RoutingTrials.asp, line 24, column 11
DROP TABLE #tmp

Thanks again,
dmh4ab

 

[chmohan]

DROP TABLE #tmp
GO

works?
or u can try
strSQL="DROP TABLE #tmp"
Conn.Execute strSQL

 

[dmh4ab]

chmohan, thanks for all your help.

Using the strSQL and conn.Execute gives me another error...

"Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name '#tmp'"

Thanks again,
dmh4ab