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

Recommended Answers

All 4 Replies

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

commented: perfect suggestion +5

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

Not working

still error is the same.

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

Perfect

Thanks Jim

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.