Update Varbinary(max) column with Null value

[PM312]

I am trying to update Varbinary(Max) column data with Null value. But unable to do so.

Error "Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."

my code

Private Sub ButRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButRemove.Click

    Call Connect()
    Dim cmd As New SqlClient.SqlCommand
    cmd.Connection = CnCommon
    cmd.CommandText = "UPDATE PDC_CHQ_IN_HAND SET PDF = '" & vbNull & "' WHERE RefNo = '" & TxtRefNo.Text & "'"
    cmd.ExecuteNonQuery()
    MessageBox.Show("Image has been Deleted", "Delete", MessageBoxButtons.OK)
    CnCommon.Close()
    CnCommon.Dispose()

End Sub

[deceptikon]

Try using System.DbNull.Value instead of vbNull. vbNull doesn't represent what you think it represents. ;)

[PM312]

cmd.CommandText = "UPDATE PDC_CHQ_IN_HAND SET PDF = '" & System.DBNull.Value & "' WHERE RefNo = '" & TxtRefNo.Text & "'"

Not working

still error is the same.

[Reverend Jim]

Try

cmd.CommandText = "UPDATE PDC_CHQ_IN_HAND SET PDF = NULL " &
                  " WHERE RefNo = '" & TxtRefNo.Text & "'"

but if RefNo is defined as a numeric you should use

cmd.CommandText = "UPDATE PDC_CHQ_IN_HAND SET PDF = NULL " &
                  " WHERE RefNo = " & TxtRefNo.Text

[PM312]

Perfect

Thanks Jim