none
null value to image in norwind sample database category table RRS feed

  • Question

  • hi

    i insert data in the category of norwind sample database.  My requirement is via vb.net i insert null value in image

    when i code, i got this error message

    my code is

     Dim photoParam As New SqlParameter("@photo", SqlDbType.Image)
            photoParam.Value = DBNull.Value
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True")
                Using command As New SqlCommand("INSERT INTO [Northwind].[dbo].[Categories] ([CategoryName],[Description],[Picture]) VALUES(@CategoryName,  @Description,   @Picture)", connection)
                    command.Parameters.AddWithValue("@CategoryName", TextBox1.Text)
                    command.Parameters.AddWithValue("@Description", TextBox1.Text)
                    command.Parameters.AddWithValue("@Picture", photoParam)
                    Try
                        connection.Open()
                        If command.ExecuteNonQuery() = 1 Then
                            MessageBox.Show("Data  Save in Database", "Read Message Carefully", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2)
                            ShowData()
                        End If
                        connection.Close()
                    Catch ex As Exception
                        MsgBox(ex.Message)
                    Finally
    
                    End Try
                End Using
            End Using

    thanks


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Wednesday, May 6, 2020 1:28 PM

Answers

  • Hello,

    Picture is nullable so do not add it if no photo.

    Public Function InsertImage(categoryName As String, categoryDescription As String) As Boolean
        Dim success as Boolean = False
        Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True")
            Using command As New SqlCommand("INSERT INTO Categories (CategoryName,[Description]) VALUES(@CategoryName,  @Description)", connection)
                command.Parameters.AddWithValue("@CategoryName", categoryName)
                command.Parameters.AddWithValue("@Description", categoryDescription)
                Try
                    connection.Open()
                    If command.ExecuteNonQuery() = 1 Then
                        success = True
                    End If
                Catch ex As Exception
                    success = False
                End Try
            End Using
        End Using
    
        Return success
    End Function


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by muhammadanzar Wednesday, May 6, 2020 8:15 PM
    Wednesday, May 6, 2020 2:02 PM
    Moderator

All replies

  • Hello,

    Picture is nullable so do not add it if no photo.

    Public Function InsertImage(categoryName As String, categoryDescription As String) As Boolean
        Dim success as Boolean = False
        Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True")
            Using command As New SqlCommand("INSERT INTO Categories (CategoryName,[Description]) VALUES(@CategoryName,  @Description)", connection)
                command.Parameters.AddWithValue("@CategoryName", categoryName)
                command.Parameters.AddWithValue("@Description", categoryDescription)
                Try
                    connection.Open()
                    If command.ExecuteNonQuery() = 1 Then
                        success = True
                    End If
                Catch ex As Exception
                    success = False
                End Try
            End Using
        End Using
    
        Return success
    End Function


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by muhammadanzar Wednesday, May 6, 2020 8:15 PM
    Wednesday, May 6, 2020 2:02 PM
    Moderator
  •  command.Parameters.AddWithValue("@Picture", DBNull.Value)

    However, I guess your connectionstring is not as it has to be. 

    But we don't know where your database server and database itself is located


    Success
    Cor

    Wednesday, May 6, 2020 2:40 PM
  •  command.Parameters.AddWithValue("@Picture", DBNull.Value)

    However, I guess your connectionstring is not as it has to be. 

    But we don't know where your database server and database itself is located


    Success
    Cor

    Cor, so why would you do this in regards to DBNull.Value? If a image is never going to be inserted it makes more sense to not even have the image in the statement or in the parameter list.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 6, 2020 3:12 PM
    Moderator
  • Karen,

    If you look at his code, he does this in a strange way. Creates a stand alone SQL parameter and uses that as a property value voor his SQL insert string.

    Probably that goes with Add, but I did not test that, but he uses AddWithValue to let the Value be a property object. 

    I tried to use an easy way to show that, because everything about non initialized fields is very confusing as we see in time what people have made from it. 

    :-) 

    Of course it is not wise to insert a DBNull.Value in a database (it can be used to clean a column nulls allowed column with an update)

    I took for you some more time to tell this than for the OP assuming you would understand this

    :-)


    Success
    Cor



    Wednesday, May 6, 2020 5:51 PM