Passing Date Variables

I am a newbie and need help. I am creating an intranet site for my company
and my SQL2000 database has a column named transdate. I want to provide the
user with the ability to run a canned report depending on a date range
entered in a web form (Start Date / End Date).
How would I pass these form entries to my SQL select statement? I am using
ASP VBScript
I'm thinking...
Select *
from DB where transdate >= ' and transdate <= '
Your help is greatly appreciated!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
You can use a parameterized SQL statement, specifying '?' as parameter
markers:
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = "2006-05-01"
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = "2006-05-31"
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615867ef9c88a@.uwe...
>I am a newbie and need help. I am creating an intranet site for my company
> and my SQL2000 database has a column named transdate. I want to provide
> the
> user with the ability to run a canned report depending on a date range
> entered in a web form (Start Date / End Date).
> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
> I'm thinking...
> Select *
> from DB where transdate >= ' and transdate <= '
> Your help is greatly appreciated!!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Thanks Dan, I'll give it a shot
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||Obviously I am not advanced enough to get this? I need to pass the dates
from my Web form to the embedded SQL statement in Dreamweaver. I am using
multiple recordsets that require these same date ranges. In ACCESS it is eas
y
because you can create the form and reference it. Is there anything like thi
s
in SQL?
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
SQL Server is basically just the back-end database engine. Unlike SQL
Server, Access also includes an IDE so that you can develop a 'rich client'
GUI for your users. The Access database engine isn't a client/server DBMS
because Access runs in the client process and, in the case of a multi-user
application, the Access database file is shared among multiple Access
instances. With SQL Server, it is the database engine is shared and only
that SQL Server instance accesses the database files.
I know next to nothing about Dreamweaver so I can't provide detailed help.
I don't know what an 'embedded SQL statement in Dreamweaver' is. I assume
this part of server-side code (ASP or ASP.NET) that is generated by the
Dreamweaver IDE. I would expect that the IDE would provide some method to
parameterize the SQL statement, map to your form variables and associate
with a SQL Server database connection.
don't know if this will help but below is an ASP VBScript snippet that can
execute a SQL statement based on the date range. I would expect Dreamweaver
would generate something similar.
<!-- include ADO constants -->
<!-- METADATA
TYPE="typelib"
UUID="00000200-0000-0010-8000-00AA006D2EA4"
-->
<%
Set connection = CreateObject("ADODB.Connection")
connection,Open "Provider=SQLOLEDB;Data Source=MyDbServer;Integrated
Security=SSPI"
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable " & _
"WHERE transdate >= ? AND transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = Request("fromDate")
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = Request("toDate")
Set results = command.Execute
While results.EOF = False
'process row here
results.MoveNext
Loop
results.Close
connection,Close
&>
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:61bd9dcc71568@.uwe...
> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
> Dan Guzman wrote:
> --
> Message posted via http://www.webservertalk.comsql