Date problem between ActiveX input box and SQL query

[wisa64]

Hello All,
I am new to MS SQL Server 2000. I come from a Unix/Oracle/sqlplus environment. I am having problems in my where clause when comparing an ActiveX user input date to a date from the database. Or, I think that's what my problem stems from. I have a DTS package that pulls info from a table to an Excel speadsheet. I added an ActiveX input box under Workflow Properties to prompt the user for a date. I want to pass this date into the query on the where clause to pull only those rows where the updatedate = the input date. The DTS package executes just fine but it doesn't select any rows. Can someone tell me what I'm missing. I have tried inputting the date several different ways in combination with different convert/case in the where clause. Thanks in advance for any guidance......wisa64

My ActiveX script is:
Function Main()
dim tamdate
tamdate = inputbox("Enter Date")

Main = DTSStepScriptResult_ExecuteTask
End Function


My Query is:
select consumerid, servicedatefrom, updatedate
from tbl_cs_sv_service
where convert(char(10),updatedate,101) = ?

P.S. I have a Global Variable setup under Package Properties called tamdate of type string. And, I have a parameter setup under Transform Data Task Properties called tamdate.

[Stephen]

You don't assign the local (to the activeX script task) variable tamdate to the global variable tamdate. This is probably the cause of the problem.

Try adding the following to the ActiveX task after the input box
DTSGlobalVariables("tamdate") = tamdate

Or maybe

DTSGlobalVariables("tamdate") = inputbox("Enter Date")

[wisa64]

Hi Stephen,
Thanks for the info. I added the global variable/parameter based on some info I found on groups.google.com under a link called 'DTS - Can dts detect user input'.

I removed the Global Variable that I created under Package Properties. Which in turn removed the Parameter Mapping under 'Transform Data Task Properties'. Now, I am getting a new error. After I key in the date on the input box and select ok, the 'Copy Data from Results to Results Task' fails. If I double click to see the error it says 'Invalid Step Script Result Value'.

In the Oracle world, to accomplish this all I had to do was use an Accept statement in the query(sqlplus). Not so sure I'm liking MS SQL Server 2000. I'm searching the Internet for what this error might mean. But, if you have any ideas that would be wonderful.

Thanks for the help....Tammi

[wisa64]

Oooops my bad. I had the Main function line rem'd out in the ActiveX script leftover from something I was trying yesterday. So, now the 'Invalid Step Script Result Value' error is gone.

By removing the global/parameter info and adding the DTSGlobalVariables line to the ActiveX script seems to have done nothing. It's still not selecting any rows. Works of course if I hard code the date in the where clause.

Just feel like the text date keyed in input box doesn't match the date that I'm comparing against in the database therefore no rows are selected. But, not sure how to case/convert/trim to get them to match. I have tried several versions of these and still nothing gets selected.

If anyone knows how to compare ActiveX inputbox date entry to MS SQL Server 2000 date in database, please offer a suggestion.

Thanks....Tammi