2

I am using CLASSIC ASP with a stored procedure, the stored procedure has a date field that is not required.

I am trying to insert when there is no date entered on my form.

I have tried "", Null, NULL, vbNull.

edit: I am setting DOB to "" in the function call for this:

DOB = ""

if DOB = "" then
    DOB = ????
End if

Set paramId = cmdStoredProc.CreateParameter("@DOB", adDBDate, adParamInput,0,DOB)
cmdStoredProc.Parameters.Append paramId

This with sql server 2008.

Any help is appreciated.

Primetime
  • 569
  • 4
  • 10
  • 24

5 Answers5

0

This is because SQL Server does not support the adDBDate datatype. To correct this problem, change the datatype of the @DOB parameter to adDBTimeStamp.

Set paramId = cmdStoredProc.CreateParameter("@DOB", adDBTimeStamp, adParamInput, ,DOB)
cmdStoredProc.Parameters.Append paramId

http://support.microsoft.com/kb/214459/en

Artemination
  • 703
  • 2
  • 10
  • 30
0

If DOB is empty try to add parameter but without a value. This will affect default value of sp parameter.

If DOB = "" Then
    Set paramId = cmdStoredProc.CreateParameter("@DOB", adDBDate, adParamInput)
Else
    Set paramId = cmdStoredProc.CreateParameter("@DOB", adDBDate, adParamInput, 0, DOB)
End If

cmdStoredProc.Parameters.Append paramId
Kul-Tigin
  • 16,728
  • 1
  • 35
  • 64
  • I still get "Parameter object is improperly defined. Inconsistent or incomplete information was provided." – Primetime Nov 11 '11 at 13:31
  • A value must be specified for the parameter, not supplying one will cause this error however `Null` ought to be an acceptable value. – AnthonyWJones Nov 11 '11 at 14:18
  • If the parameter is not required, must have a default value. As I said, passing parameter without a value affects default value defined in SP and this will not cause an error for an optional parameter (as far as I know?). In such cases, there are two ways I know of. This answer is the first one. The second is, using `NamedParameters` property of `ADODB.Command` object, setting it to `True` and passing parameters except optional ones. Of course NULL is acceptable for all but there may be a different default value other than NULL for the @DBO parameter? – Kul-Tigin Nov 11 '11 at 16:19
  • I assume that, there is a specified default value i.e. `.. As @DBO Date = '2011/11/11' ..`. I undersand that _"the stored procedure has a date field that is not required."_ – Kul-Tigin Nov 11 '11 at 16:19
0

Did you try

if DOB = "" then
    DOB = Empty
End if
tikotzky
  • 2,542
  • 1
  • 17
  • 20
  • Still get "Parameter object is improperly defined. Inconsistent or incomplete information was provided." Thanks. – Primetime Nov 11 '11 at 13:33
0

Make sure the DOB field is trimmed so a space character is not tripping you up.

If trim(DOB) = "" then ....
Dee
  • 1,432
  • 1
  • 9
  • 8
0

Null would normally be the correct value in this scenario.

Due lack of detail in the question I will guess.

Ulitmately you have a non-nullable DOB table field being assigned this null value at heart of the SP. That in turn is causing an error.

AnthonyWJones
  • 187,081
  • 35
  • 232
  • 306