Calling Stored Procedure on classic ASP page

Question

I've been struggling all day calling a Stored Procedure from a classic ASP page. I have a few basic noobie questions.

First, is this the best way to add a parameter to my command:

cmd.Parameters.Append cmd.CreateParameter("@SubmissionDate", adDBTimeStamp, adParamInput, , txtDate)

Second, is adDbTimeStamp the proper type to use when mapping to a smalldatetime parameter in my Stored Procedure?

Third, how do I pass a null date to a datetime stored procedure?

Also, what editors are popular for classic ASP development. I was told to use Dreamweaver (bought CS4) but I'm really having some performance issues and have downgraded to the mighty NotePad.

Thanks!

Answer 1

3

To pass a null parameter to a storred procedure, you simply do not pass it and provide a default.

CREATE PROCEDURE Demo
    @Test datetime = NULL
AS
BEGIN
    -- BLAH
END

Answer 2

Hmm, I've not used classic ASP for several years, however I can tell you that the thing you need to Google for your answers is "ADO" (not ADO.Net though)

The following link has an example of executing a stored procedure in VBScript with ADO, I would experiment with examples like this until you find something that works.

http://www.15seconds.com/issue/010718.htm

Also my text editor of choice at the moment is "Notepad++", not sure how it handles classic asp but its probably worth a try and its got to be better for you than Notepad.

Answer 3

The adDBTimeStamp is the correct data type for passing in a datetime and using Append/CreateParameter is the best way to create the parameter.

However would txtDate be a string? You should really convert this to a Date type first. Thats not a easy as it sounds unless you can be very sure of the date format used when entering the data on the form.

To pass in null just replace the parameter with the Null expression value:-

cmd.Parameters.Append cmd.CreateParameter("@SubmissionDate", adDBTimeStamp, adParamInput, , null)

I use VS2005/2008 to edit most ASP but I like to have Notepad++ hanging around when I want to tweak something quickly.