[RESOLVED] UPDATE image field with NULL value in VB.NET

Hi, do you know how can I update in SQL Server an image field with a NULL value.

I tried vbnull.value, but I get an error like " Fail of the conversion from vraiantType to Byte[]" :ehh:
(I just search how to assign the NULL value, not how to connect to the SQL Database :D)

I already did it on another project with Varbinary(MAX) and it works well, but I'm affraid about losing data during the conversion from image to varbinary(max). So I would prefer to do it with image type.

Have you already done something like that ? Thank you for your help!

If you just google "sql server set image null" ... you get back a lot of results.
https://www.google.com/search?q=sql+...set+image+null


-tg

We can't be sure without seeing your code, but there is a good chance that you want DBNull.Value

If that doesn't work for you, show us the relevant section of the code.

Yep.
vbnull.value how does this even compile?

Small basic sample:

Code:

---


Imports System.Data.SqlClient

Public Class Form1

    Private connectionstring As String = "Data Source=Mydatasource\SQLEXPRESS;Initial Catalog=tests;Integrated Security=True;"

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim connection = New SqlConnection(connectionstring)


        connection.Open()

        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandType = CommandType.Text

        command.Parameters.Add("@im", SqlDbType.Image).Value = DBNull.Value

        command.CommandText = "update mytable set imgx = @im"

        Try
            command.ExecuteNonQuery()
        Catch ex As Exception

        End Try

        connection.Close()
    End Sub
End Class

---

My code is :

VB.NET Code:

Connexion.Open()
            Dim Num_MET$ = Mid(Demande.Lab_Num_MET.Text, 8, 2) & Mid(Demande.Lab_Num_MET.Text, 11, 13)  'Récupère le Numéro MET
            Dim Rq As String = "UPDATE Donnees_MET SET PlanST = @File_Field WHERE Num_MET = '" & Num_MET & "'"
 
            Dim Cmd As New SqlCommand(Rq, Connexion)
            Cmd.Parameters.Add("@File_Field", SqlDbType.VarBinary, -1)
            Cmd.Parameters("@File_Field").Value = DBNull.Value
 
            Try
                Cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message & " Catégorie d'erreur N° 20")
            End Try
            Cmd.Dispose()
            Connexion.Close()

Sorry @tg, I looked before but I maybe have a vocabulary problem when I googled it.

I will try some things tomorrow and let you know soon (I let the thread open for now ;) )

Have you read my code?
"SqlDbType.Image"

You also should not store images in the database unless there is some special reason or the images are very small or limited.
Store them in a folder and put the folder path or link in the database.
Also it's not good practice to use "$" or there signs in a variable unless in special cases . If you've asked me I would have thought that .net will project this as an exception but apparently it does not.... Ah it does when you set the variable type. Good, I was getting ready for a badmouth on .net :)

Quote:

---

Originally Posted by sapator

Have you read my code?
"SqlDbType.Image"

---

Yes I red it, but I put here my code which do not work without update, I will try your code this morning and let you know ;)

Quote:

---

Originally Posted by sapator

You also should not store images in the database unless there is some special reason or the images are very small or limited.
Store them in a folder and put the folder path or link in the database.

---

Thank you for the tip, if I could I prefer to convert image to binary(MAX) but I 'm afraid to loose data...

Quote:

---

Originally Posted by sapator

Also it's not good practice to use "$" or there signs in a variable unless in special cases . If you've asked me I would have thought that .net will project this as an exception but apparently it does not.... Ah it does when you set the variable type. Good, I was getting ready for a badmouth on .net

---

Sorry but here you did a mistake, the "$" is not a part of a variable name, this is just to indicate that the variable "Num_MET" is a string. ;)

VB.NET Code:

Dim Num_MET$ --> Dim Num_MET as string

(Just a time gain)

Quote:

---

Originally Posted by sapator

Good, I was getting ready for a badmouth on .net

---

I didn't understood this sentence, could you explain it to me ? :ehh:

Thank you !

Just replace SqlDbType.VarBinary by SqlDbType.Image works well for me, thank you all :D