Update MySQL field wilth NULL from web form

Question

I have a form on an (classic) ASP webpage which I want to use to remove the data held in a records field. The field itself has NULL as default.

I have tried a couple of ways to to do this. If I use <input value="">, it inserts a blank. If I use <input value="NULL">, it inserts the word NULL but this is not a NULL value.

Any ideas?

The SQL (from Dreamweaver):

<%
If (CStr(Request("MM_update")) = "deleteImage") Then
  If (Not MM_abortEdit) Then
    ' execute the update
    Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_ENG_STRING
    MM_editCmd.CommandText = "UPDATE tblContent SET ContentImage = ?, ContentImageCaption = ? WHERE ContentID = ?" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 255, Request.Form("ContentImage")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 105, Request.Form("ContentImageCaption")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "delete-images-updated.asp"
    If (Request.QueryString <> "") Then
      If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
        MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
      Else
        MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
      End If
    End If
    Response.Redirect(MM_editRedirectUrl)
  End If
End If
%>

and the form:

<form ACTION="<%=MM_editAction%>" METHOD="POST" name="deleteImage" id="deleteImage">
<input name="ContentID" type="hidden" 
 value="<%=(rsContent.Fields.Item("ContentID").Value)%>">
<input name="ContentImage" type="hidden" value="">
<input name="ContentImageCaption" type="hidden" value="">
<h3>Are you sure you want to delete this image?</h3>
<input type="submit" class="submitButton" value="Yes! Delete Image">
<input type="reset" class="resetButton" 
 value="No. Cancel Delete" onClick="javascript:history.go(-1)">
<input type="hidden" name="MM_update" value="deleteImage">
<input type="hidden" name="MM_recordId" 
 value="<%= rsContent.Fields.Item("ContentID").Value %>">
</form>

http://stackoverflow.com/questions/9314353/set-value-to-null-in-mysql — Nathan Rice, Feb 24 '14 at 22:32
Thanks for the link Nathan. It took me a few attempts to get it to work, but it's all sorted now. Fab! — , Feb 24 '14 at 23:14
@MartySmartyPants Could you post your updated code as an answer and accept it to help others who come across this issue? Has the added benefit of not showing up in the unanswered queue - see [Can I answer my own question?](http://stackoverflow.com/help/self-answer) — user692942, Feb 25 '14 at 11:13
@Lankymart Sorry, I should have done this before being asked. Thanks for the prod. — , Feb 25 '14 at 15:32

score 1 · Accepted Answer · answered Feb 25 '14 at 15:26

Inside the section:

Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_ENG_STRING
MM_editCmd.CommandText = "UPDATE tblContent SET ContentImage = ?, ContentImageCaption = ? WHERE ContentID = ?" 
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 255, Request.Form("ContentImage")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 105, Request.Form("ContentImageCaption")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

I changed the line regarding the update of the database from

MM_editCmd.CommandText = "UPDATE tblContent SET ContentImage = ?, ContentImageCaption = ? WHERE ContentID = ?"

to

MM_editCmd.CommandText = "UPDATE tblContent SET ContentImage = NULL, ContentImageCaption = NULL WHERE ContentID = ?"

and then deleted the (in this case, two) lines referring to the fields to be NULL

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 255, Request.Form("ContentImage")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 105, Request.Form("ContentImageCaption")) ' adVarWChar

This, when the form is submitted, enteres a NULL into the required fields. The form details remained unchanged.

Update MySQL field wilth NULL from web form

1 Answers1