CODEWITHSUNDEEP
sqlstored-proceduresasp-classicvbscript
user2335705
user2335705

Reputation: 53

How to insert NULL value into datetime field via stored procedure

I've searched for answers to this, but none I've found have really helped. I have a stored procedure that writes data to a table. The insert includes a DateTime field, but this field can be empty depending on what data is entered. The code in my Classic ASP page is similar to the following:

If Request.Form("Status") = "Closed" Then
Status = "Closed"
DateClosed = Now()

Else

Status = "OPEN"
DateClosed = NULL

End If

SP_AddToTable = "MyTable '" & Status & "', '" & DateClosed & "'"

Set SQLQuery = objConn.Execute(SP_AddToTable)

The stored procedure looks like this:

USE [MyDatabase]
GO
/****** Object:  StoredProcedure [dbo].[SP_AddToTable] Script Date: 04/30/2013         10:00:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_AddToTable] 

@Status varchar(20),
@DateClosed datetime

AS

INSERT INTO MyTable (
[Status],
DateClosed,
)

VALUES (
@Status,
@DateClosed)

If I pass an actual date value, it works fine, but if the NULL value is passed, or if I don't pass any data to the DateClosed field then the field defaults to 1 Jan 1900. What do I need to do (either in my ASP code, or in the stored procedure) to leave the DateClosed field empty in the table?

I'm quite new to stored procedures, so please excuse any beginners errors. :)

Thanks.

Upvotes: 0

Views: 1735

Answers (1)

sgeddes
sgeddes

Reputation: 62831

First, you need to change your ASP code to call the sp using parameterized queries -- your query above is highly vulnerable to SQL Injection:

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = objConn
cmd.CommandText = "SP_AddToTable"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

cmd.Parameters(1) = Status 
cmd.Parameters(2) = DateClosed 
cmd.Execute

This should also fix your null issue as before you were inserting the value 'NULL' (with single quotes).

Upvotes: 1

Related Questions