How to create an image column in a SQL Server table and retrieve it in ASP.NET using VB

Question

I want to know how to create an image column in a SQL Server table and retrieve it in ASP.NET using VB.NET

create table Tbl(ID int primary key,Name varchar(20),Image ....)

score 4 · Accepted Answer · answered Mar 22 '12 at 01:51

4

You want to use the VARBINARY(MAX) to store this data.

As to the code side, here is a good, brief answer.

From the answer:

FileStream st = new FileStream(@"C:\filename.jpg", FileMode.Open);
byte[] buffer = new byte[st.Length];
st.Read(buffer, 0, (int)st.Length);
st.Close();

SqlConnection conn = new SqlConnection("...");
SqlCommand cmd = new SqlCommand(
    "UPDATE SomeTable SET image=@image WHERE ID = 1", conn);
cmd.Parameters.AddWithValue("@image", buffer);
conn.Open();
int i = cmd.ExecuteNonQuery();
conn.Close();

answered Mar 22 '12 at 01:51

Justin Pihony

66,056
18
147
180

pllz Justin how can i directly add an image in sql column without usin any VB method ? – Yassine Kira Mar 22 '12 at 02:30
http://weblogs.sqlteam.com/peterl/archive/2007/09/26/Insert-binary-data-like-images-into-SQL-Server-without-front-end.aspx – Justin Pihony Mar 22 '12 at 03:55
i want to insert the image in a column of a table alredy have a 5 columns i try this but it does not work insert into dbo.Produit values('Pc portable','HP EliteBook série p','Un ordinateur professionnel robuste de 35,5 et 39,6 cm (14.0" et 15.6") à fonctions multiples, hautes performances et longue autonomie',SELECT * FROM OPENROWSET(BULK N'C:\Users\Yassine-Kira\Desktop\Templates\ProductImg\elite-book_tcm_133_1096796.png', SINGLE_BLOB) ,20,4999,0); – Yassine Kira Mar 22 '12 at 23:26
Is this what you are looking for: http://stackoverflow.com/questions/416881/insert-picture-into-sql-server-2005-image-field-using-only-sql – Shrieks Mar 28 '12 at 21:08

score 1 · Answer 2 · answered Mar 22 '12 at 01:51

1

Save and Retrieve Files from SQL Server Database using ASP.Net

Storing and Retrieving Images/Files In Sql Server - VB.NET

answered Mar 22 '12 at 01:51

JotaBe

38,030
8
98
117

pllz JotaBa how can i directly add an image in sql without usin any VB method ? – Yassine Kira Mar 22 '12 at 02:30

score 0 · Answer 3 · answered Mar 22 '12 at 06:34

in the sql server create the column with type: image. and in the asp, use this example code: (it's in c#)

    string con = "your connection string"
    var Image =  (from A in YourTblImage
                  select A).First();//to get one record of the table use the `first`
    FileStream fs = new FileStream(@"yourPath to save the image", FileMode.Create);
    try
    {

        System.Data.Linq.Binary img = image.imageColumnName;
        byte[] imageK = img.ToArray();
        fs.Write(imageK, 0, imageK.Length);
    }
    catch (Exception ex)
    {
        string str = ex.Message;
    }
    finally
    {
        fs.Close();
    }

tnkks my bro ^^ i want to insert the image in a column of a table alredy have a 5 columns i try this but it does not work insert into dbo.Produit values('Pc portable','HP EliteBook série p','Un ordinateur professionnel robuste de 35,5 et 39,6 cm (14.0" et 15.6") à fonctions multiples, hautes performances et longue autonomie',SELECT * FROM OPENROWSET(BULK N'C:\Users\Yassine-Kira\Desktop\Templates\ProductImg\elite-book_tcm_133_1096796.png', SINGLE_BLOB) ,20,4999,0); — Yassine Kira, Mar 22 '12 at 23:26

How to create an image column in a SQL Server table and retrieve it in ASP.NET using VB

3 Answers3