PDA

View Full Version : Recordset Missing after SQL string input..


garybrett
03-31-2009, 12:03 PM
Hi,
Wonder if someone can assist in this query. Using DW MX connecting to SQL2005 db using vbscript. I create a recordset [rsTESTDB] inputting a sql query and this gets written to my code like this:

rsTESTDB.Source = "SELECT SLUsers.D2Custom1, SLUsers.D2ID, TblCommissionPayment.OWPaidDate, TblCommissionPayment.CommissionPaid, SLUsers.D2Username FROM TblCommissionPayment INNER JOIN SLUsers ON TblCommissionPayment.CRID COLLATE SQL_Latin1_General_CP1_CI_AS = SLUsers.D2Custom1"

If however I add some code to the end of this line to filter records on logged in user it still works but the recordset dissapears from bindings panel. After editing code the line looks like this?

rsTESTDB.Source = "SELECT SLUsers.D2Custom1, SLUsers.D2ID, TblCommissionPayment.OWPaidDate, TblCommissionPayment.CommissionPaid, SLUsers.D2Username FROM TblCommissionPayment INNER JOIN SLUsers ON TblCommissionPayment.CRID COLLATE SQL_Latin1_General_CP1_CI_AS = SLUsers.D2Custom1 WHERE D2ID= " & Session("SL_UserID")

If I remove the code it comes back!! I need this recordset as I would like to add server behaviours, am I doing something wrong?

Thanx for looking..

garybrett
04-02-2009, 01:42 PM
Just to update and hopefully resolve, is there any possible way I could add the variable in the advanced panel variables window rather than appending it to code is design view? I have tried but to no avail.

What I want to do to retain the recordset in DW for furture use is this:

Create Recordset in advanced window:
rsTESTDB.Source = "SELECT SLUsers.D2Custom1, SLUsers.D2ID, TblCommissionPayment.OWPaidDate, TblCommissionPayment.CommissionPaid, SLUsers.D2Username FROM TblCommissionPayment INNER JOIN SLUsers ON TblCommissionPayment.CRID COLLATE SQL_Latin1_General_CP1_CI_AS = SLUsers.D2Custom1

Then in variable section add:
Name = D2ID :: Default Value = 0 :: Run-Time Value = " & Session("SL_UserID")

I know thats wrong but can anyone see where/why or what should be entered into run time value to grab the session? I tried Request.QueryString("SL_UserID") but no joy..

Much appreciated and thankyou for your time.

mangofreak
04-02-2009, 04:31 PM
Shouldn't be something like:



rsTESTDB.Source = "SELECT SLUsers.D2Custom1, SLUsers.D2ID, TblCommissionPayment.OWPaidDate, TblCommissionPayment.CommissionPaid, SLUsers.D2Username FROM TblCommissionPayment INNER JOIN SLUsers ON TblCommissionPayment.CRID COLLATE SQL_Latin1_General_CP1_CI_AS = SLUsers.D2Custom1
WHERE SLUsers.D2ID = Param


your parameter name = Param
Default value= 0
Run-time val= Session("SL_UserID")

Try that. Hopefully it will work.

garybrett
04-02-2009, 04:59 PM
Mangofreak, you are a legend Ive been trying for a week with absolutley no joy!!

All appears to be working now so I can crack on with creating other tasks using that recordset again..Brilliant.

Thanks for your time

Gary

PS - In early testing of this it appears I need to refresh the page [F5] when it loads it is holds the previous session. For example if I log in as user 1 and view records I have 10 rows. I log out then back in as user 2 and user 1 records are displayed still until I F5 the page, then user 2 data is loaded. Quite odd...

garybrett
04-02-2009, 05:59 PM
Mangofreak,
Thanks again so much, have that bit sorted now too. Works a treat..

Kind regards

Gary

mangofreak
04-02-2009, 06:43 PM
Glad I could help. :)