none
How to insert image into sql table without stored procedure?

    Question

  • Hi:

    Does any one knows how to insert images into sql table without using a stored procedure?

    I am working on a windows form for employees profiles (vb.net) and i have a combobox and a few text boxes.

    My compobox is manually conected to a database and also the other controls.

    When i fill the blanks and hit save, automatically the data is saved in my db and when i look into my combobox i see the new employee name. When i select a name, every control get filled.

    I have a picture box where the selected picture profile will be display. The picture will be display also when i select a name from a combobox. It will be retrieve from db table GeneralInfo Column Foto.

    What i need is to include in my sql statements the code to be able to save, update and delete the image from and to db.

    This is part of my insert code:

    Private Sub savenewprofileonload(ByVal sender As System.ObjectByVal e As System.EventArgsHandles btn_saveprofile_onnewprofile_tap1.Click
     
            Dim mconn As New SqlConnection("Data Source=(local);Initial Catalog=mydb;Integrated Security=true;")
            Dim cmd As New SqlCommand
            cmd.Connection = mconn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "insert into GeneralInfo (Name,LastName)values('" & txt_nombre.Text & "','" & txt_lname.Text & "')"
     
            Try
                mconn.Open()
                cmd.ExecuteNonQuery()
                MessageBox.Show("Profile Saved Successfully")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                mconn.Close()
            End Try
     
     
        End Sub

    The image column name is Foto datatype varbinary(MAX)...may be it should be image datatype....

    This is my select code:

    Private Sub cbox_profiles_SelectedIndexChanged(ByVal sender As System.ObjectByVal e As System.EventArgsHandles cbox_profiles.SelectedIndexChanged
            Try
                Dim mconn As New SqlConnection("Data Source=(local);Initial Catalog=mydb;Integrated Security=true;")
                Dim cmd As New SqlCommand
                cmd.Connection = mconn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT Name,LastName FROM GeneralInfo where RecordId =  " + cbox_profiles.SelectedValue.ToString()
                Dim formda As New SqlDataAdapter(cmd)
                Dim formds As New DataSet
                formda.Fill(formds, "GeneralInfo")
     
                txt_name.Text = formds.Tables("GeneralInfo").Rows(0)("Name").ToString()
                txt_lname.Text = formds.Tables("GeneralInfo").Rows(0)("LastName").ToString()  

    Catch
     ex As Exception         End Try         btn_newprofile_onload_tap1.Visible = False         btn_editprofile_oncboxselectiontap1.Visible = True         btn_newprofile_onload_tap1.Visible = True         If cbox_profiles.SelectedIndex = 0 Then             btn_editprofile_oncboxselectiontap1.Visible = False         End If               End Sub

    If any one can give me a hand with this i will appreciated very much.

    Thanks!


    • Edited by pantered Wednesday, November 23, 2011 2:29 PM
    Wednesday, November 23, 2011 2:27 PM

Answers

All replies

  • Hi,

    How do you upload the picture on the employee to your program?

    As I understand you want to save the picture on a separate server place and link the data back to your program when you find a corresponding person to that picture? Correct?

    If you have the link to the employees picture you just create in your database table GeneralInfo say PicLink and save the link there!

    So when you hit the employee you just relate the database information to that pic link and show it in the picturebox:

    PictureBox1.Image = New System.Drawing.Bitmap(PicLink)

    Hope this can help you!


    Leif
    Wednesday, November 23, 2011 2:55 PM
  • Thanks for reply;

    At this moment i have not include the Foto db table column because dont know how.

    I will include for example "insert into GeneralInfo (Name, LastName,Foto)values('" & txt_name.text & "','" & txt_lname.text & "'......

    then the Foto column.

    The idea is not to have a path. I want the form to save a copy of that image to the db.

    May be there is a better way to do this.

    Any suggestions are welcome.

    Wednesday, November 23, 2011 3:06 PM
  • See the below MS KB article:

    HOW TO: Copy a Picture from a Database Directly to a PictureBox Control with Visual Basic .NET

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, November 23, 2011 3:28 PM
  • Hi Paul, thanks for the reply;

    This is the scenario:

    You fill the blanks, you choose the photo corresponding to the employee then you hit save.

    Every data is saved in the GeneralInfo table, including the selected employee photo.

    Then when i choose any name from the comobox, every control get filled including the picturebox with the corresponding employee photo.

    Then, if i want to update the profile with another photo, i repeat the same steps.

    That is my idea. I have every thing working, but i don't know how to include the photo insertion and retrieve into and from sql table using the code i posted.

    Wednesday, November 23, 2011 3:52 PM
  • There should be code in the article that will do that (last code block). It copies from the PictureBox to a MemoryStream and then from the MemoryStream to Command Parameter.

    The code block above the INSERT code block demonstrates how to read the BLOB data from the database and display it in the PictureBox.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, November 23, 2011 4:06 PM
  • Take a look at this sample.

    http://www.vb-tips.com/datasetimage.aspx


    Success
    Cor
    Wednesday, November 23, 2011 5:47 PM
  • I need a sample code using the code i post to be able to understand the logic.

    I am new to programming and trying...

    Wednesday, November 23, 2011 8:54 PM
  • Hi Pantered,

    Welcome to the MSDN forum.

    Cor and Paul gave you the perfect sample code to solve your issue. I’m afraid that there is no image datatype in SQL database. There are two ways to store the pictures in database: store the path of picture or store the binary data of the picture. So there is no difference in logic to store a picture or text into database.

    If you have any additional questions, please feel free to let we know.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us
    Friday, November 25, 2011 5:56 AM
  • Just short example hope u understand:

    Insert image to SQL

     

     cn.Open()

            cmd = New SqlCommand("Insert into tblCustomer(Code,CustomerName,Picture) Values (@Code,@CustomerName,@Picture)", cn) 

            With cmd.Parameters

                    .AddWithValue("@Code", txtCode.Text.Trim)

                    .AddWithValue("@CustomerName", txtCustomerName.Text.Trim)

                     Dim b() As Byte

                     b = IO.File.ReadAllBytes("Picture path")

                     .AddWithValue("@Picture", b)

              End With

            cmd.ExecuteNonQuery()

     


    Saturday, November 26, 2011 2:20 AM