Image insertion

Question

Picture Insertion:

How to insert a picture into a SQL Server 2005 database by using vb.net2008? I need code with explanation, for example I have a database table where I want to store a picture ..

I have no idea to store image in SQL Server 2005, how can I do that?

Please help me ...

My current code is not right plz check it and tell e new simple code for insert image:

My code

Imports System.Data.SqlClient
Imports System.IO

Public Class Form1
    Dim path As String = (Microsoft.VisualBasic.Left(Application.StartupPath, Len(Application.StartupPath) - 9))
    Dim con As New SqlConnection("Data Source=nida-PC\SQLEXPRESS;AttachDbFilename=" & path & "Database1.mdf;Integrated Security=True;User Instance=True")
    'Dim con As New SqlConnection= ("Data Source=NIDA-PC\SQLEXPRESS;Initial Catalog=Finaldb;Integrated Security=True")
    Dim cmd As SqlCommand

    ' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    ' If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
    'PictureBox1.BackgroundImage = Image.FromFile(OpenFileDialog1.FileName)
    'Label1.Visible = True
    'TextBox1.Visible = True
    'Label1.Text = "Name"
    'TextBox1.Text = ("Save image")
    ' End If


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.InformationTableAdapter.Fill(Me.Database1DataSet.Information)
        con.Open()

    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            PictureBox1.BackgroundImage = Image.FromFile(OpenFileDialog1.FileName)
            Label1.Visible = True
            TextBox1.Visible = True
            Label1.Text = "Name"
            TextBox1.Clear()
            PictureBox1.SizeMode = PictureBoxSizeMode.AutoSize
        End If
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            If TextBox1.Text = "" Then
                MsgBox("Fill the Name Field")
            Else
                Dim sql As String = "Insert into tbl_image(@Image_Name,@photo)"
                Dim cmd As New SqlCommand(sql, con)
                con.Open()
                cmd.Parameters.AddWithValue("@Image_Name", TextBox1.Text)
                cmd.Parameters.AddWithValue("@Photo", SqlDbType.Image)
                Dim ms As New MemoryStream()
                PictureBox1.BackgroundImage.Save(ms, PictureBox1.BackgroundImage.RawFormat)
                Dim data As Byte() = ms.GetBuffer()
                Dim p As New SqlParameter("@photo", SqlDbType.Image)
                p.Value = data
                cmd.Parameters.Add(p)
                cmd.ExecuteNonQuery()
                'Form1_Load(sender, e)
                MessageBox.Show("Name & Image has been saved", "Save", MessageBoxButtons.OK)
                Label1.Visible = False
                TextBox1.Visible = False
            End If
        Catch ex As Exception
            'MsgBox("")
            Return
        End Try
        con.Close()
    End Sub

    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub
End Class

Answer 1

You can insert a image in database by using following query:

SELECT BulkColumn AS Img FROM OPENROWSET(BULK N'D:\Desktop\xyz.jpg', SINGLE_BLOB) AS CategoryImage

"D:\Desktop\xyz" replace it with your file path.

Answer 2

I think this would do, though I forgot how to edit it, so just review it...

Private Sub btnStudSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStudSave.Click

    If filenamepicturebox1 = "" Then
        MessageBox.Show("Browse a Picture to continue!", "Left Over", MessageBoxButtons.OK, MessageBoxIcon.Information)
    Else
        Dim savenewstudent As Boolean = True
        Dim filled_data As Boolean = False
        Dim studId As Boolean = False
        Dim studName As Boolean = False
        Dim studCourse As Boolean = False
        Dim studGender As Boolean = False

        Try
            If txtStudName.Text = String.Empty AndAlso txtStudId.Text = String.Empty _
            AndAlso cmbStudCourse.Text = String.Empty AndAlso UPYLvl.Text = String.Empty _
            AndAlso cmbStudGender.Text = String.Empty Then
                MessageBox.Show("All the Information is BLANK!")
                txtStudName.Focus()
            ElseIf txtStudName.Text = String.Empty Then
                MessageBox.Show("Student Name Information is BLANK!")
                txtStudName.Focus()
            ElseIf txtStudId.Text = String.Empty Then
                MessageBox.Show("Student ID Information is BLANK!")
                txtStudId.Focus()
            ElseIf cmbStudCourse.Text = String.Empty Then
                MessageBox.Show("Student Course Information is BLANK!")
                cmbStudCourse.Focus()
            ElseIf cmbStudGender.Text = String.Empty Then
                MessageBox.Show("Student Gender Information is BLANK!")
                cmbStudGender.Focus()
            Else
                studName = True
                studId = True
                studCourse = True
                studGender = True
            End If

            If studId AndAlso studName AndAlso studCourse AndAlso studGender Then
                If ifExistsStudentName(txtStudName.Text) Then
                    MessageBox.Show("Duplication of Name has Found!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    txtStudName.Focus()
                ElseIf ifExistsStudentId(txtStudId.Text) Then
                    MessageBox.Show("Duplication of ID has Found!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    txtStudId.Focus()
                Else
                    filled_data = True
                    OdbcConnection1.Open()
                    Dim newstudent As OdbcCommand = New OdbcCommand _
                    ("INSERT INTO `Student`(`Id`, `Name`, `Gender`, `Course`, `Year`, `Picture`) VALUES ('" _
                     & Me.txtStudId.Text & "','" _
                     & Me.txtStudName.Text & "','" _
                     & Me.cmbStudGender.Text & "','" _
                     & Me.cmbStudCourse.Text & "','" _
                     & Me.UPYLvl.Value & "','" _
                     & Replace(filenamepicturebox1, "\", "\\") & "')", OdbcConnection1)

                    newstudent.ExecuteNonQuery()
                    newstudent = Nothing
                    OdbcConnection1.Close()
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message & "" & ex.StackTrace)
            savenewstudent = False
            OdbcConnection1.Close()

        End Try

        If savenewstudent AndAlso filled_data Then
            MessageBox.Show("New Student data were succssfully saved!", "DATA SAVE", _
                            MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtStudName.Clear()
            txtStudId.Clear()
            cmbStudCourse.Text = ""
            UPYLvl.Value = 1
            cmbStudGender.Text = ""
            txtStudId.Enabled = False
            txtStudName.Enabled = False
            cmbStudCourse.Enabled = False
            UPYLvl.Enabled = False
            cmbStudGender.Enabled = False
            btnStudBrowse.Enabled = False
            PictureBox1.Image = Nothing
            filled_data = False
            btnStudDelete.Enabled = False
            btnStudUpdate.Enabled = False
            btnStudAdd.Visible = True
            btnStudSave.Visible = False
        End If
    End If

End Sub