help with portable SQL select statment

[plasma800]

I need some help.

I have a sql select statement that I would like to place in an include file.

The SQL select statement is fed variables from the page.

For Instance
<%
Dim manuID, CatID
manuID=6
CatID="'part','processor'"

Dim objRS
Set objRs = Server.Createobject("ADODB.Recordset")
objRs.Open "SELECT Brand, ProdID, ProdURL, ProdSKU, Category, ThumbImageURL, LinkText, ProdURL, ProdMiniDescription, manufacturer, ThumbAltText FROM TABLE WHERE Manufacturer='"& manuID & "' and Category in ("& catID &")", objCn, 1, 3
%>

The recordset is used later in the page via a sub procedure.

What I did was move the code from DIM objRS to the closing bracket to my connections include file and then the sub stopped working.

Any tips?

tried it as a sub and function

 

[DotNetGnat]

I am not sure what you are trying to do but any ways try this:

put this function in the include file:

Function ReturnSQL(manid, catid)
ReturnSQL =  " SELECT Brand, ProdID, ProdURL," &_
             " ProdSKU, Category, ThumbImageURL,"&_
             " LinkText, ProdURL, ProdMiniDescription,"&_
             " manufacturer, ThumbAltText FROM TABLE " &_
             " WHERE Manufacturer='"& manid & "' and"&_  
             " Category in ("& catid &") " 
End Function

and on the page you can say

<%
Dim manuID, CatID
manuID=6
CatID="'part','processor'"

Dim objRS, sql
Set objRs = Server.Createobject("ADODB.Recordset")
sql = ReturnSQL(manuID, CatID)
objRs.Open  sql, objCn, 1, 3
%>

-DNG

 

[plasma800]

The only reason I was trying to get the code into an include file simply for ease of adjustability.

I figure if I put this sql statement on over 100 product pages and then decide I want to add something, I didn't want to have to adjust every single sql select statement.

 

[plasma800]

Can I make this even worse?

I also wanted to infuse the stuff to select.

I defined a constant (maybe this is not the right thing) in my constants file for the selection

Const ProdDisplaySql = "Brand, ProdID, ProdURL, ProdSKU, Category, ThumbImageURL, LinkText, ProdURL, ProdMiniDescription, manufacturer, ThumbAltText"

and in the select statement i chagned it to read..

"SELECT" & ProdDisplaySql & "so forth ...

 

[DotNetGnat]

then i guess i already answered your question...if there are any changes to the sql statement then you need to only change it in the function included in the include file.

it wont be necessary to create another variable ProdDisplaySql...

-DNG

 

[plasma800]

good point

 

[plasma800]

Function DisplayProdConnect
DisplayProdConnect =  " SELECT Brand, ProdID, ProdURL," &_
             " ProdSKU, Category, ThumbImageURL,"&_
             " LinkText, ProdURL, ProdMiniDescription,"&_
             " manufacturer, ThumbAltText FROM bProducts " &_
             " WHERE Manufacturer='"& manuID & "' and" &_  
             " Category in ("& CatID &") " 
end function

on page

<%
Dim manuID, CatID, varCritBrand, varCritCategory, varCritTableTitle
manuID=6
CatID="'part','processor'"


Dim objRS, sql
Set objRs = Server.Createobject("ADODB.Recordset")

sql = DisplayProdConnect(manuID, CatID)
objRS.open sql, objcn, 1, 3
		
%>

error

Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'DisplayProdConnect'

/phonesystems/panasonic/parts2.asp, line 11

I really appreciate your help!!!

 

[DotNetGnat]

see the function definition...it should accept two variables...

Function DisplayProdConnect(manuID, CatID)

-DNG

 

[plasma800]

duh thanks!

 

[plasma800]

thank you so much!!!!!!!!!!!!!!!

 

[DotNetGnat]

no problem...glad to assist.

-DNG