asked on

Provider error '8002000a' - Out of present range.

Hello,

I'm trying to call an Insert / Update Stored Procedure in ASP VBScript, I'm using Dates and have tried to define default dates (01-01-1900) so that the SP can take the necessary action if the date value is (01-01-1900); my code looks like --

<%
If Request("PID") <> "" AND Request("Update") = "Yes" then
Dim SPEnews__PartnerID
SPEnews__PartnerID = "0"
if(Request("PartnerID") <> "") then SPEnews__PartnerID = Request("PartnerID")

Dim SPEnews__uname
SPEnews__uname = ""
if(Request("uname") <> "") then SPEnews__uname = Request("uname")

Dim SPEnews__pword
SPEnews__pword = ""
if(Request("pword") <> "") then SPEnews__pword = Request("pword")

Dim SPEnews__package
SPEnews__package = "0"
if(Request("package") <> "") then SPEnews__package = Request("package")

Dim SPEnews__startdate
SPEnews__startdate = ""
if isdate(Request("startdate") <> "") then SPEnews__startdate = Request("startdate")
if not isdate(SPEnews__startdate) then SPEnews__startdate= (19000101)

Dim SPEnews__enddate
SPEnews__enddate = ""
if isdate(Request("enddate") <> "") then SPEnews__enddate = Request("enddate")
if not isdate(SPEnews__enddate) then SPEnews__enddate= (19000101)


set SPEnews = Server.CreateObject("ADODB.Command")
SPEnews.ActiveConnection = MM_Connection_STRING
SPEnews.CommandText = "dbo.usp_AdminPartnerEnews"
SPEnews.CommandType = 4
SPEnews.CommandTimeout = 0
SPEnews.Prepared = true
SPEnews.Parameters.Append SPEnews.CreateParameter("@RETURN_VALUE", 3, 4)
SPEnews.Parameters.Append SPEnews.CreateParameter("@PartnerID", 3, 1,4,SPEnews__PartnerID)
SPEnews.Parameters.Append SPEnews.CreateParameter("@uname", 200, 1,250,SPEnews__uname)
SPEnews.Parameters.Append SPEnews.CreateParameter("@pword", 200, 1,25,SPEnews__pword)
SPEnews.Parameters.Append SPEnews.CreateParameter("@package", 3, 1,4,SPEnews__package)
SPEnews.Parameters.Append SPEnews.CreateParameter("@startdate", 133, 1,50,SPEnews__startdate)
SPEnews.Parameters.Append SPEnews.CreateParameter("@enddate", 133, 1,50,SPEnews__enddate)
SPEnews.Execute()
Response.Redirect("/partners/detail.asp?ID=" & (Request("PID")) & "")
End if
%>

Open in new window

However when i test the page i get --

"Provider error '8002000a'
Out of present range.
/partners/update/enewsletter.asp, line 44"

Where line 44 is

SPEnews.Parameters.Append SPEnews.CreateParameter("@startdate", 133, 1,50,SPEnews__startdate)

I'm assuming it doesn't like the default value I have set on line 25 -

if not isdate(SPEnews__startdate) then SPEnews__startdate= (19000101)

Appreciate any assistance -

Thank you

G_H

Hi,

This code has an error, and should probably be simplified to make it more readable

if isdate(Request("startdate") <> "") then SPEnews__startdate = Request("startdate")
if not isdate(SPEnews__startdate) then SPEnews__startdate= (19000101)

'' ## Should become

if IsDate(Request("startdate")) then
    SPEnews__startdate = Request("startdate")
else
    SPEnews__startdate = ("01/01/1900") '' ## Adjust to your date format
end if

Open in new window

I think you will need to do the same with "End Date"

GH

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

Thank you both,

Now that I have set the dafault date for both parameters, I need to use the default date in my SP, currently I have

IF @startdate <> '' AND @enddate = ''

Where what i really want is

IF @startdate <> the default date whilst @enddate = the default date

do something --

How is the best way to do this?

Thank you

SOLUTION

G_H

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

That looks good to me, thank you

Patrick Matthews

garethtnash,

With respect, your original question has been answered. If you need help writing your sproc, I suggest that you open a new question for it.

In that new question, be sure to include the Topic Area for the database product you are using, and drop the Visual Basic Programming topic--this question is relevant to VBScript and ASP, but not to VB proper.

Patrick

garethtnash

ASKER

Although within the Stored Procedure, would it still be the same? or has the VBScript tirned these into datetime variables?

the code in question is

IF @startdate <> '' AND @enddate = ''
BEGIN
Update dbo.[Member-Login]
set Username = @uname, Password = @pword,  Access = 'Y'
Where ID = @EnewsLogin
END

Open in new window

G_H

I think I agree with matthewspatrick, we have sorted the ASP issue, and I am not an expert in SP, so you may be better off asking a new question (in the correct zone(s)) for that...

That is if you need to do that in the SP. If you want to do the checking in ASP, let us knoe what you are trying to do...

GH

garethtnash

ASKER

Thanks Both