473,322 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to insert and retrieve images to/from Sqlserver database

Hi all!!

i am working on C#, ASP.Net with Sql server2000 and i am facing a
problem, my Question is:
"How to insert and retrieve images to/from Sqlserver2000 Database"
I created a table in database there are only two Columns:

ImageID( Datatype int) and ImageURL (Binary)

Thanks in advance!!

Ahuja;)

Feb 14 '07 #1
1 13166
On Feb 14, 7:09 am, "ahujasa...@gmail.com" <ahujasa...@gmail.com>
wrote:
Hi all!!

i am working on C#, ASP.Net with Sql server2000 and i am facing a
problem, my Question is:

"How to insert and retrieve images to/from Sqlserver2000 Database"

I created a table in database there are only two Columns:

ImageID( Datatype int) and ImageURL (Binary)

Thanks in advance!!

Ahuja;)
I wanted to store a JPG file a SQL2000 database. Here's how I
accomplished the task in Visual Basic:

The database PhotoLog has a PhotoImages table that has a column called
PhotoImage of datatype IMAGE.
I load the file into an ADODB.Stream object. Then I insert the stream
into the PhotoImage column using an ADODB.Recordset object. Next I
retrieve the image and save it as a file under a different name.
Finally, I display it in a Picture control.

There may be some differences between C# and VB, but the use of ADODB
objects should be the same. Note that this code borrows from some I
found when researching saving and retrieving scanned images.

Lou Phillips

PS: I hope this snippet has not lost anything in my cut-and-paste
preparation of this posting.

Global cn As ADODB.Connection

Sub Main()

Dim sConnect As String

sConnect = "Provider=SQLOLEDB;" _
& "Data Source=Gateway;" _
& "Integrated Security=SSPI;" _
& "Initial Catalog=PhotoLog"

'Create a new connection object
Set cn = New ADODB.Connection

With cn
.ConnectionTimeout = 60
.ConnectionString = sConnect
.Open
End With

Call DataLoadImages( "C:\TEMP\Picture.JPG" )

Call FileLoadImages( "C:\TEMP\Image.JPG" )

cn.close

set cn = Nothing

Form1.Picture1.LoadPicture("c:\temp\Image.jpg")

End Sub
Private Sub DataLoadImages( sFileToSave as String )

Dim rs As ADODB.Recordset
Dim st As ADODB.Stream

Dim sqlStmt As String
Dim szFullPath As String

Dim var As Variant

Set rs = New ADODB.Recordset

sqlStmt = "PhotoImages"
rs.Open sqlStmt, cn, adOpenKeyset, adLockOptimistic

'Initialize the stream object used to load the file.
Set st = New ADODB.Stream

st.Type = adTypeBinary

st.Open
st.LoadFromFile (sFileToSave)

var = st.Read

rs.AddNew
rs.Fields("PhotoImage").Value = var

rs.Update
st.Close
rs.Close

End Sub
Private Sub FileLoadImages( sFileToWrite as String )

Dim rs as ADODB.Recordset
Dim st as ADODB.Stream

Set rs = New ADODB.Recordset

rs.Open sqlStmt, cn, adOpenForwardOnly, adLockReadOnly

'Initialize the stream object used to persist to a file
Set st = New ADODB.Stream
st.Type = adTypeBinary
st.Open

rs.MoveFirst

var = rs.Fields(1).Value

'Write the value of the field to the stream
st.Write var

'Save the content of the stream to a file
st.SaveToFile ( sFileToWrite )

'Close the stream
st.Close

'Close recordset
rs.Close

'Free resources
Set rs = Nothing
Set st = Nothing

End Sub


Feb 14 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jan van Veldhuizen | last post by:
I try to use the Server Data on Insert property to return a column value swhich is set in a trigger. But it does not work. Why not? What am I doing wrong? (BTW I'm using Oracle version 10) My...
2
by: Santo Santis | last post by:
How can I make an .exe file that can insert data automatically in a SQLServer database, I can do it in C, but how can I connect to SQLServer and execute a query. All data that I have to insert...
4
by: Renato Vieira | last post by:
Is there a way of insert or update images (*.gif or *.jpg) in a image field of a SQLServer table without using code? Some sort of small application can do that? by supplying the filename maybe? ...
8
by: John Smith | last post by:
Hello all: Is there a way to determine the auto increment id from a newly inserted record in the database? Thanks, John
5
by: Roy Gourgi | last post by:
Hi, I am used to working in Visual FoxPro and I would like to be able to create a database and store and retrieve information from it. What is the simplest way to do it and what should I be...
13
by: moondaddy | last post by:
Happy Holidays All! I want to load a datagrid with images and text (Image, text, image, text, etc...). The data for this datagrid will be coming from sql server and the images are stored as files...
0
by: m31hu1 | last post by:
Hi all, I'm new to this forum... I'm trying to let sql store data from a text file, but the data won't display... what am i missing here? Your help is greatly appreciated. create table <?php...
1
by: mark mestrom | last post by:
Hi all, i have a problem. In my access-application i am trying to insert a record in a linked table with resides in a sqlserver 2000 database. However, the primary key in that table is an INT with...
7
by: anu b | last post by:
Hi I need to use Clr trigger for insert command My code is as below I am using SQL server 2005 and VS 2008.... but after running this code i didnt get the result as i expexted it shows the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.