Results 1 to 8 of 8

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

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Resolved [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[]"
    (I just search how to assign the NULL value, not how to connect to the SQL Database )

    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!

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: UPDATE image field with NULL value in VB.NET

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: UPDATE image field with NULL value in VB.NET

    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.

  4. #4
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: UPDATE image field with NULL value in VB.NET

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Re: UPDATE image field with NULL value in VB.NET

    My code is :

    VB.NET Code:
    1. Connexion.Open()
    2.             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
    3.             Dim Rq As String = "UPDATE Donnees_MET SET PlanST = @File_Field WHERE Num_MET = '" & Num_MET & "'"
    4.  
    5.             Dim Cmd As New SqlCommand(Rq, Connexion)
    6.             Cmd.Parameters.Add("@File_Field", SqlDbType.VarBinary, -1)
    7.             Cmd.Parameters("@File_Field").Value = DBNull.Value
    8.  
    9.             Try
    10.                 Cmd.ExecuteNonQuery()
    11.             Catch ex As Exception
    12.                 MsgBox(ex.Message & " Catégorie d'erreur N° 20")
    13.             End Try
    14.             Cmd.Dispose()
    15.             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 )

  6. #6
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: UPDATE image field with NULL value in VB.NET

    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
    Last edited by sapator; Apr 15th, 2019 at 09:41 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  7. #7

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Re: UPDATE image field with NULL value in VB.NET

    Quote Originally Posted by sapator View Post
    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 View Post
    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 View Post
    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:
    1. Dim Num_MET$ --> Dim Num_MET as string
    (Just a time gain)

    Quote Originally Posted by sapator View Post
    Good, I was getting ready for a badmouth on .net
    I didn't understood this sentence, could you explain it to me ?

    Thank you !

  8. #8

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Re: UPDATE image field with NULL value in VB.NET

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width