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

SQL Statement works in SQL Manager but not ASP

Hi all. Can anyone tell me why this script works in SQL but not when I run it from an ASP page? The error I get is: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#Style_Recall'. Any help would be very much appreciated!

Thanks.
Jon
CREATE TABLE #Style_Recall (Auto varchar(5), Pantone1Num varchar(20), Pantone2Num varchar(20), Pantone3Num varchar(20), Pantone4Num varchar(20), Pantone5Num varchar(20), Pantone6Num varchar(20), RGB1 varchar(15), RGB2 varchar(15), RGB3 varchar(15), RGB4 varchar(15), RGB5 varchar(15), RGB6 varchar(15)); INSERT INTO #Style_Recall(Auto, Pantone1Num, Pantone2Num, Pantone3Num, Pantone4Num, Pantone5Num, Pantone6Num) SELECT Style.Auto, Style.Pantone1Num, Style.Pantone2Num, Style.Pantone3Num, Style.Pantone4Num, Style.Pantone5Num, Style.Pantone6Num FROM Style JOIN Catalogs ON Catalogs.Auto=Style.Auto AND Catalogs.License=? AND Catalogs.Season=? AND Catalogs.Year=?; UPDATE #Style_Recall SET RGB1=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone1Num), RGB2=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone2Num), RGB3=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone3Num), RGB4=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone4Num), RGB5=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone5Num), RGB6=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone6Num); SELECT Style.Auto, Style.AL_Style, Style.Description, Style.Color1, Style.Color1Cat, Style.Color2, Style.Color2Cat, Style.Color3, Style.Color3Cat, Style.Color4, Style.Color4Cat, Style.Color5, Style.Color5Cat, Style.Color6, Style.Color6Cat, Style.MSRP, Style.Size1, Style.Size1Approved, Style.Size2, Style.Size2Approved, Style.Size3, Style.Size3Approved, Style.Size4, Style.Size4Approved, Style.Size5, Style.Size5Approved, Style.Size6, Style.Size6Approved, Style.File_Name, Catalogs.License, Catalogs.Season, Catalogs.Year, Catalogs.SortOrder, #Style_Recall.Auto, #Style_Recall.RGB1, #Style_Recall.RGB2, #Style_Recall.RGB3, #Style_Recall.RGB4, #Style_Recall.RGB5, #Style_Recall.RGB6 FROM Style JOIN (Catalogs JOIN #Style_Recall ON #Style_Recall.Auto=Catalogs.Auto) ON Catalogs.Auto=Style.Auto AND Catalogs.License=? AND Catalogs.Season=? AND Catalogs.Year=? ORDER BY Catalogs.SortOrder

Open in new window

0
Jon DeVito
Asked:
Jon DeVito
6 Solutions
 
Jon DeVitoAuthor Commented:
Sorry that pasted on 1 line, This may be easier to read.

Thanks.
Jon
CREATE TABLE #Style_Recall (Auto varchar(5), Pantone1Num varchar(20), Pantone2Num varchar(20), Pantone3Num varchar(20), Pantone4Num varchar(20), Pantone5Num varchar(20), Pantone6Num varchar(20), RGB1 varchar(15), RGB2 varchar(15), RGB3 varchar(15), RGB4 varchar(15), RGB5 varchar(15), RGB6 varchar(15));
INSERT INTO #Style_Recall(Auto, Pantone1Num, Pantone2Num, Pantone3Num, Pantone4Num, Pantone5Num, Pantone6Num) SELECT Style.Auto, Style.Pantone1Num, Style.Pantone2Num, Style.Pantone3Num, Style.Pantone4Num, Style.Pantone5Num, Style.Pantone6Num FROM Style JOIN Catalogs ON Catalogs.Auto=Style.Auto AND Catalogs.License=? AND Catalogs.Season=? AND Catalogs.Year=?; 
UPDATE #Style_Recall SET RGB1=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone1Num), RGB2=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone2Num), RGB3=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone3Num), RGB4=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone4Num), RGB5=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone5Num), RGB6=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone6Num); SELECT Style.Auto, Style.AL_Style, Style.Description, Style.Color1, Style.Color1Cat, Style.Color2, Style.Color2Cat, Style.Color3, Style.Color3Cat, Style.Color4, Style.Color4Cat, Style.Color5, Style.Color5Cat, Style.Color6, Style.Color6Cat, Style.MSRP, Style.Size1, Style.Size1Approved, Style.Size2, Style.Size2Approved, Style.Size3, Style.Size3Approved, Style.Size4, Style.Size4Approved, Style.Size5, Style.Size5Approved, Style.Size6, Style.Size6Approved, Style.File_Name, Catalogs.License, Catalogs.Season, Catalogs.Year, Catalogs.SortOrder, #Style_Recall.Auto, #Style_Recall.RGB1, #Style_Recall.RGB2, #Style_Recall.RGB3, #Style_Recall.RGB4, #Style_Recall.RGB5, #Style_Recall.RGB6 FROM Style JOIN (Catalogs JOIN #Style_Recall ON #Style_Recall.Auto=Catalogs.Auto) ON Catalogs.Auto=Style.Auto AND Catalogs.License=? AND Catalogs.Season=? AND Catalogs.Year=? ORDER BY Catalogs.SortOrder"

Open in new window

0
 
sshah254Commented:
I believe that "#" in front of a table name makes it a temp table in memory.

This may explain why it does not work with ASP pages - http://support.microsoft.com/kb/280134

Ss
0
 
Jon DeVitoAuthor Commented:
Thanks, I don't think this is the case as I'm calling it in the same statement, not after the .execute. I'm also using this exactly this was on other pages (without the second JOIN) & it works.

Thanks.
Jon
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Paul JacksonCommented:
Try changing your temp table to a global temp table by using ## instead of # i.e.

##Style_Recall
0
 
Jon DeVitoAuthor Commented:
Thanks Jacko, same error though.
0
 
Scott Fell, EE MVEDeveloperCommented:
Can you past the entire code exactly as you have it on your page using example variables?  It could be something with how you handle your single/double quotes or the code itself.

I would start with something like this first

' 1st create the table  http://www.asp101.com/tips/index.asp?id=89
strSQL = strSQL & "CREATE TABLE #Style_Recall " & vbCrLf
strSQL = strSQL & "(" & vbCrLf
strSQL = strSQL & "Auto varchar(5), Pantone1Num varchar(20), Pantone2Num varchar(20)," & vbCrLf
strSQL = strSQL & " Pantone2Num varchar(20), Pantone3Num varchar(20),  " & vbCrLf
strSQL = strSQL & "Pantone4Num varchar(20), Pantone5Num varchar(20), Pantone6Num varchar(20), " & vbCrLf
strSQL = strSQL & "RGB1 varchar(15), RGB2 varchar(15), RGB3 varchar(15), RGB4 varchar(15), RGB5 varchar(15), RGB6 varchar(15)" & vbCrLf
strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
strSQL = strSQL & ")" & vbCrLf

Set cnnTest = Server.CreateObject("ADODB.Connection")
cnnTest.Open "your connection string"
cnnTest.Execute strSQL
cnnTest.Close
Set cnnTest = Nothing

Next run a script to insert data.   Can you repost the insert info just using a few variables to make it easier to go through?
0
 
Anthony PerkinsCommented:
You cannot use the same syntax in ASP for concatenating strings as you do in T-SQL, they are different languages:
ASP uses & to concatenate strings, T-SQL +.
ASP use " for strings, T-SQL uses '
And the list goes on...
0
 
Jon DeVitoAuthor Commented:
Thanks Padas, here is the entire thing:

Thanks!
Jon
<%
Dim Catalog__MMColParam1
Catalog__MMColParam1 = "1"
If (Request.QueryString("license") <> "") Then 
  Catalog__MMColParam1 = Request.QueryString("license")
End If
%>

<%
Dim Catalog__MMColParam2
Catalog__MMColParam2 = "1"
If (Request.QueryString("season") <> "") Then 
  Catalog__MMColParam2 = Request.QueryString("season")
End If
%>

<%
Dim Catalog__MMColParam3
Catalog__MMColParam3 = "1"
If (Request.QueryString("year") <> "") Then 
  Catalog__MMColParam3 = Request.QueryString("year")
End If
%>

<%
Dim Catalog
Dim Catalog_cmd
Dim Catalog_numRows

Set Catalog_cmd = Server.CreateObject ("ADODB.Command")
Catalog_cmd.ActiveConnection = MM_VendorVB_STRING

Catalog_cmd.CommandText = "CREATE TABLE #Style_Recall (Auto varchar(5), Pantone1Num varchar(20), Pantone2Num varchar(20), Pantone3Num varchar(20), Pantone4Num varchar(20), Pantone5Num varchar(20), Pantone6Num varchar(20), RGB1 varchar(15), RGB2 varchar(15), RGB3 varchar(15), RGB4 varchar(15), RGB5 varchar(15), RGB6 varchar(15));" 

Catalog_cmd.CommandText = Catalog_cmd.CommandText + " INSERT INTO #Style_Recall(Auto, Pantone1Num, Pantone2Num, Pantone3Num, Pantone4Num, Pantone5Num, Pantone6Num) SELECT Style.Auto, Pantone1Num, Pantone2Num, Pantone3Num, Pantone4Num, Pantone5Num, Pantone6Num FROM Style JOIN Catalogs ON Catalogs.Auto=Style.Auto AND Catalogs.License=? AND Catalogs.Season=? AND Catalogs.Year=?;"

Catalog_cmd.CommandText = Catalog_cmd.CommandText + " UPDATE #Style_Recall SET RGB1=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone1Num), RGB2=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone2Num), RGB3=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone3Num), RGB4=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone4Num), RGB5=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone5Num), RGB6=(SELECT Red + ',' + green + ',' + blue FROM ColorChart WHERE Color = #Style_Recall.Pantone6Num);"

Catalog_cmd.CommandText = Catalog_cmd.CommandText + " SELECT Style.Auto, Style.AL_Style, Style.Description, Style.Color1, Style.Color1Cat, Style.Color2, Style.Color2Cat, Style.Color3, Style.Color3Cat, Style.Color4, Style.Color4Cat, Style.Color5, Style.Color5Cat, Style.Color6, Style.Color6Cat, Style.MSRP, Style.Size1, Style.Size1Approved, Style.Size2, Style.Size2Approved, Style.Size3, Style.Size3Approved, Style.Size4, Style.Size4Approved, Style.Size5, Style.Size5Approved, Style.Size6, Style.Size6Approved, Style.File_Name, Catalogs.License, Catalogs.Season, Catalogs.Year, Catalogs.SortOrder, #Style_Recall.Auto, #Style_Recall.RGB1, #Style_Recall.RGB2, #Style_Recall.RGB3, #Style_Recall.RGB4, #Style_Recall.RGB5, #Style_Recall.RGB6 FROM Style JOIN (Catalogs JOIN #Style_Recall ON #Style_Recall.Auto=Catalogs.Auto) ON Catalogs.Auto=Style.Auto AND Catalogs.License=? AND Catalogs.Season=? AND Catalogs.Year=? ORDER BY Catalogs.SortOrder" 

Catalog_cmd.Prepared = true
Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param1", 200, 1, 25, Catalog__MMColParam1) ' adVarChar
Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param2", 200, 1, 15, Catalog__MMColParam2) ' adVarChar
Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param3", 200, 1, 15, Catalog__MMColParam3) ' adVarChar
Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param4", 200, 1, 25, Catalog__MMColParam1) ' adVarChar
Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param5", 200, 1, 15, Catalog__MMColParam2) ' adVarChar
Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param6", 200, 1, 15, Catalog__MMColParam3) ' adVarChar
Set Catalog = Catalog_cmd.Execute
Catalog_numRows = 0
%>

Open in new window

0
 
Jon DeVitoAuthor Commented:
I understand ac, I just can't figure out where my coding is wrong.
I have most of this code working on another page. The only difference with this on is the extra JOIN.
0
 
Jon DeVitoAuthor Commented:
Even if I just use the 2 sections below I get the error: Invalid object name '#Style_Recall'.

Catalog_cmd.CommandText = "CREATE TABLE #Style_Recall (Auto varchar(5), Pantone1Num varchar(20), Pantone2Num varchar(20), Pantone3Num varchar(20), Pantone4Num varchar(20), Pantone5Num varchar(20), Pantone6Num varchar(20), RGB1 varchar(15), RGB2 varchar(15), RGB3 varchar(15), RGB4 varchar(15), RGB5 varchar(15), RGB6 varchar(15));" 

Catalog_cmd.CommandText = Catalog_cmd.CommandText + " INSERT INTO #Style_Recall(Auto, Pantone1Num, Pantone2Num, Pantone3Num, Pantone4Num, Pantone5Num, Pantone6Num) SELECT Style.Auto, Pantone1Num, Pantone2Num, Pantone3Num, Pantone4Num, Pantone5Num, Pantone6Num FROM Style JOIN Catalogs ON Catalogs.Auto=Style.Auto AND Catalogs.License=? AND Catalogs.Season=? AND Catalogs.Year=?;"

Open in new window

0
 
Jon DeVitoAuthor Commented:
It has something to do with the JOIN, can someone look at that for me?



Thanks again.
Catalog_cmd.CommandText = Catalog_cmd.CommandText + " INSERT INTO #Style_Recall(Auto, Pantone1Num, Pantone2Num, Pantone3Num, Pantone4Num, Pantone5Num, Pantone6Num) SELECT Style.Auto, Pantone1Num, Pantone2Num, Pantone3Num, Pantone4Num, Pantone5Num, Pantone6Num FROM Style JOIN Catalogs ON Catalogs.Auto=Style.Auto AND Catalogs.License=? AND Catalogs.Season=? AND Catalogs.Year=?;"

Open in new window

0
 
Scott Fell, EE MVEDeveloperCommented:
Take a look at http://www.w3schools.com/ADO/ado_datatypes.asp and make sure each of your License (Catalog__MMColParam1), Season (Catalog__MMColParam2) and Year (Catalog__MMColParam3) fields are the correct data type.

Where you have

Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param1", 200, 1, 25, Catalog__MMColParam1) ' adVarChar
Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param2", 200, 1, 15, Catalog__MMColParam2) ' adVarChar
Catalog_cmd.Parameters.Append Catalog_cmd.CreateParameter("param3", 200, 1, 15, Catalog__MMColParam3) ' adVarChar

Make sure the first number after, "param1" is correct and corresponds to the datatype.  

For your join, Should it look like, Join table1 on table2.id=table1.id

If that is not it, can you give smaller sample of the 2 data table structures similar to below and let us know which fields get joined and what the final view looks like.  

table1.id (int)
table1.field1 (varchar)
table1.field2 (varchar)

table2.id (int)
table2.field1(varchar)
table2.field2(varchar)

0
 
Jon DeVitoAuthor Commented:
I wound up creating a regular table & using a truncate command to clear it before each use. No idea how I should award this, any suggestions? I would like to be fair about it.

Thanks again for the help everyone.
Jon
0
 
GundogTrainerCommented:
JOIN Catalogs ON Catalogs.Auto=Style.Auto WHERE Catalogs.License=? and Catalogs.Season=? AND Catalogs.Year=?;"

I may be way off mark here but where you are joining the 2 tables shouldnt the AND be a WHERE as I cant see how you can join a row to any single char - but then my SQL is a bit rusty !

0
 
Jon DeVitoAuthor Commented:
I just split the points to be fair. I appreciate everyone trying to help even though I went in a different direction to fix the problem.

Thanks.
Jon
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now