help setting params in a recordset DTC

[nahla]

I am trying to put a placeholder where the table name should go in my select statement and it won't let me. It will accept a placeholder for the where clause but not in the select part of the SQL statement. I can do it using ADO but my whole ASP page is built using DTCs. In ADO the statement is
"select * from tbl_" & txtdept.value & "where empid=' " & txtempid & " ' "

This will not work as is if I plug it into the DTC recordset SQL statement and it will not accept select * from ? where empid = ?

Any suggestions. Thanks!!

 

[Metka]

Hi,
did you check the value of your SQL statement, before you open the recordset to see if it is correct. Try debugging it or put an additional textbox on the page and asign it your SQL before you open the recordset. Your SQL seems OK,

"select * from tbl_" & txtdept.value & " where empid='" & txtempid.value & "'"

so I don't see why you're having problems with it, it works for me.
"Defeat is not the worst of failures. Not to have tried is the true failure."
-George E. Woodberry

 

[nahla]

Thanks.
I will try setting a textbox but does that SQL statement work for you inside recordset control?

 

[Metka]

SQL statement works fine, but I don't use it inside recordset control. I use it in code, let's say in thisPage_onenter event

strSQL="select * from tbl_" & txtdept.value & " where empid='" & txtempid.value & "'"

rsTest.setSQLText(strSQL)
rsTest.open

this also means that you must not automatically open your recordset.

I'm not sure if that's what you're asking, if not try again. "Defeat is not the worst of failures. Not to have tried is the true failure."
-George E. Woodberry

 

[MerlinB]

The DTC is hard wired to get the most information from the entered SQL. The ? is a placeholder that SQLServer understands and will not necessarily work with other databases. No database will accept a ? for a table name - as it cannot then validate any of the SQL.

So as soon as you key in your statement to a DTC at design time, it is sent to SQLServer to validate and return any metadata (like how many parameters, and their types - and how many columns, and their types/size etc).

Your best bet is to put a valid sql clause into the DTC (ie pick one of the table names) - at least the DTC will be happy. Then use the technique used by Metca above to swap this with the actual SQL at runtime.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)