Solved

Add pictues (.bmp) to SQL Server Image field

Posted on 2002-07-21
23
314 Views
Last Modified: 2010-08-05
I need to insert a couple of pictures (.bmp) into an Image field in a SQL Server 2000 table, this is just a test table to demonstrate to someone what can be done.

How can I do this?
0
Comment
Question by:awilson1111
  • 10
  • 9
  • 2
  • +2
23 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7168156
You can do this with the ADO Stream object in VB as follows:

Sub AddImage(rs As ADODB.Recordset, ByVal FileName As String)
Dim stm As ADODB.Stream

Set stm = New ADODB.Stream
With stm
  .Type = adTypeBinary
  .Open
  .LoadFromFile FileName
 
  'Insert the binary object into the table.
  rs.AddNew
  rs.Fields("Image").Value = .Read
  rs.Update
  .Close
End With
Set stm = Nothing

End Sub

Anthony
0
 

Author Comment

by:awilson1111
ID: 7168160
I don't yet have a visual basic interface. I want to add the pictures right to tables with the existing data.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7168164
Also, Uou may find more experts more receptive if you took the time to maintain your open questions:
Periodically loosing internet access on NT Server 4 PDC Date: 04/12/2002 07:57AM PST  
http://www.experts-exchange.com/winnt/Q_20288043.html
Deleting files on Win2000 takes a long time. Date: 02/25/2001 07:13PM PST  
http://www.experts-exchange.com/win2k/Q_20083416.html
Excel - will not format cell  Date: 06/26/2002 08:37AM PST
http://www.experts-exchange.com/msoffice/Q_20316490.html
How to Ghost (or Drive Image) with no floppy Date: 06/28/2002 01:28PM PST  
http://www.experts-exchange.com/osgen/Q_20317515.html
Database comparison site Date: 04/26/2002 07:22AM PST
http://www.experts-exchange.com/dbgen/Q_20293984.html
HP3000 Turbo Image to Access connection Date: 10/14/2000 08:08AM PST
http://www.experts-exchange.com/dbgen/Q_11552258.html
HP3000 Turbo Image to MS Access 2000 Date: 10/14/2000 08:05AM PST
http://www.experts-exchange.com/dbgen/Q_11552238.html
Looking for "GOOD" SQL Server 2000 tutorial in prep for MCDBA Date: 06/24/2002 11:39AM PST  
http://www.experts-exchange.com/mssql/Q_20315636.html
SQL Server Agent Mail Profile fails Date: 02/17/2001 07:08PM PST
http://www.experts-exchange.com/mssql/Q_20079498.html
Stop/trace huge volume of spam to our network Date: 05/14/2002 08:37AM PST  
http://www.experts-exchange.com/emailgen/Q_20300470.html

Thanks,
Anthony
0
 

Author Comment

by:awilson1111
ID: 7168185
Anthony

Several of the above I am the last one to post, nobody has provided what I was seeking. The others I either have close or want to but there is no delete button.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7168226
You can always contact Community Support for help in maintaining your open questions.  Just remember that your questions are your resposibility, it you do not wish to follow up on them, that is your choice, just do not expect Experts to be very receptive, when they see your record.

I wish you the very best of luck.

Anthony
0
 

Author Comment

by:awilson1111
ID: 7168237
Acperkins - I have followed up on them, nobody has posted a solution and I would like to delete them but the screen does not give me the option to delete the question.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7168280
Post a message in Comminity Support (http://www.experts-exchange.com/commspt/) to this effect.

Anthony
0
 
LVL 2

Expert Comment

by:erde
ID: 7168902
You can use the TextCopy.exe to load a blob into the DB (see BOL for detailled information).

Or create this stored procedure to load the blob. This procedure uses the 'TextCopy.exe'.

CREATE PROCEDURE dbo.LoadBlob(
  @PrmSrvName        varchar (30)     = NULL,     -- Servername : when NULL then @@servername
  @PrmUserId             varchar (30)     = NULL,     -- Username
  @PrmPassword          varchar (30)     = NULL,     -- Password
  @PrmDBName            varchar (30)     = NULL,     -- Database : when NULL, then current databse.
  @PrmTable             varchar (30),           -- Tablename : of the table to load the data
  @PrmField             varchar (30),           -- Fieldname : of the field to load the data
  @PrmPath           varchar (255),        -- Path : toe locate the file
  @PrmFileName          varchar (255),        -- Filename : of the file to load
  @PrmWhereClause       varchar (255)     = NULL)     -- Where-clause : to define record to update with the blob.
AS
DECLARE
  @ExecString      varchar (4000)

IF @PrmSrvName IS NULL OR @PrmSrvName = '' SELECT @PrmSrvName = @@servername
IF @PrmDBName IS NULL SELECT @PrmDBName = DB_NAME()
IF @PrmPath NOT LIKE '%\' SELECT @PrmPath=@PrmPath+'\'
SELECT @ExecString =
  'UPDATE ' +@PrmDBName + '..' + @PrmTable + ' SET ' + @PrmField + ' = ''1'' ' + @PrmWhereClause
EXEC (@ExecString)
SELECT @ExecString =
         'textcopy /S ' + @PrmSrvName +
         ' /U ' + @PrmUserId +
         ' /P ' + @PrmPassword +
         ' /D ' + @PrmDBName +
         ' /T ' + @PrmTable +
         ' /C ' + @PrmField +
         ' /W "' + @PrmWhereClause +
         '" /F ' + @PrmPath+@PrmFileName +
         ' /I'
EXEC master..xp_cmdshell @ExecString, NO_OUTPUT
GO
0
 

Author Comment

by:awilson1111
ID: 7169328
There has got to be an easier way to get 2 .bmp's into a database. It is a piece of cake in Access. This database is only for a simple demonstration, it will never be used again.
0
 
LVL 2

Expert Comment

by:erde
ID: 7169349
Isn't it easy enough?

From the commandline type :
textcopy /S<ServerName> /U<UserName> /P<PassWord> /D<DBName> /T<TableName> /C<FieldName> /W<something like WHERE ID=1> /F<Path+FileName> /I

???
0
 

Author Comment

by:awilson1111
ID: 7169440
Erde,

That sounds simple enough. I will try it this evening - it is on my home computer. If that doesn't work I will export the table to Access then add the two pictures, then import it back into SQL Server.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 9

Expert Comment

by:miron
ID: 7173433
one more way. It does not place file into image field per se, but you still can access file data using a SQL Server view. For the sake of example wrote it as directions, but in reality, directory and file names ( except extension TXT ) are arbitrary.


place a BMP file pic.bmp under directory C:\pics,
rename file pic.bmp to pic.txt
create in this directory file with name "schema.ini" with content:
---------------
[pic.txt]
ColNameHeader=False
MaxScanRows=0
Col1=PIC Memo
---------------

run this sql script

execute sp_addlinkedserver
               @server   = 'txtsrv'
               , @srvproduct = 'Jet 4.0'
               , @provider = 'Microsoft.Jet.OLEDB.4.0'
               , @datasrc   = 'C:\pics'
               , @location = NULL
               , @provstr = 'Text'
GO
EXEC sp_addlinkedsrvlogin 'txtsrv'   , FALSE , 'sa', 'Admin' , NULL
GO

create view my_pic
as
select PIC from txtsrv...[pic#txt]
GO

at this moment picture can be accessed using
select PIC from my_pic

and bcp[ied] anywhere.
0
 

Author Comment

by:awilson1111
ID: 7174195
erde,

tried textcopy, the textcopy worked, but when I created a bound Visual Basic form and a picture field, even though I bound the picture field to the photo field in the database table it would not connect to display the picture, so I can't tell if the picture is actually in the database.

I also tried exporting the SQL Server table to Access, then inserting the pictures into the photo field then importing the table back into SS and it still wouldn't work.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7174536
>>I don't yet have a visual basic interface. I want to add the pictures right to tables with the existing data. <<

>>I don't yet have a visual basic interface. I want to add the pictures right to tables with the existing data. <<

I must be missing something here.

Loading from an image in SQL Server to a Picture control is equally trivial with the ADO Stream object using Visual Basic.

Since Access's OLE Object uses a different format, you will find it difficult to upload to SQL Server.

But as I have previously mentioned you will find Experts here more interested in following up your questions, if you took the time to maintain your open questions.  For the record:
Questions Asked 16
Last 10 Grades Given A B B A A A A A  
Question Grading Record 8 Answers Graded / 8 Answers Received

Thanks,
Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7174547
And it would help if I quoted the right lines:
>>I don't yet have a visual basic interface. I want to add the pictures right to tables with the existing data. <<
and
>>but when I created a bound Visual Basic form and a picture field<<

Anthony
0
 

Author Comment

by:awilson1111
ID: 7174641
acperkins

Most of those that have not been followed up on I was the last poster - and most of them I want to delete, and will when I get the time. But when someone does post I respond within a reasonable amount of time, and when someone DOES provide an answer I award them the points relatively
quickly.

In the meantime those questions still open and don't plan to delete - no solution has been provided to solve my question.

>>
And it would help if I quoted the right lines:
>>I don't yet have a visual basic interface. I want to add the pictures right to tables with the existing data. <<
and
>>but when I created a bound Visual Basic form and a picture field<<

<<

When I originally posted this question - Sat July 20, I did not have a VB form. After trying what erde suggested I then created a simple VB form and using bound controls linked it to the SQL Server table and attempted to display the pictures. It will not link to the field with the .bmp's to display in the picture field of the VB form.


>Loading from an image in SQL Server to a Picture control is equally trivial with the ADO Stream object using Visual Basic. <

It may be trivial for someone who is a SQL Server and/or a VB programmer but I am neither, I am a network engineer with a little experience with Access trying to convince my company to go to Client/Server computing now that HP is ending support of our mainframe HP3000. I only want to set up a simple client server demonstration to show how it is done and and what the advantages are. All of the coding examples are way over my head and since my presentation is tomorrow I don't have time to learn them (as I have to work on this on my off time) - I spent nearly 12 hours over the weekend putting together a presentation and wanted to enhance it with pictures - for a wow factor - most of our users work from dumb terminals or terminal emulation on PC's and having pictures would enhance the presentation and hopefully help sell the idea of going to client/server rather than migrating our code base to a new platform.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7174861
Here is some code you can use to display the image in a PictureBox:

Sub ShowImage(rs As ADODB.Recordset)
Dim stm As ADODB.Stream
Dim TempFileName As String

TempFileName = "c:\xyz.tmp"
If Not IsNull(rs.Fields("Image").Value) Then
  Set stm = New ADODB.Stream
  With stm
     .Type = adTypeBinary
     .Open
     .Write rs.Fields("Image").Value
     .SaveToFile TempFileName, adSaveCreateOverWrite
     .Close
  End With
  Set stm = Nothing
  Picture1.Picture = LoadPicture(TempFileName)
End If

End Sub

For simplicity and clarity, I am hardcoding a temporary filename.  In production you should use something like the GetTempFileNameA API to get a unique filename and the GetTempPathA API to put it in the Temp folder.

Anthony
0
 

Author Comment

by:awilson1111
ID: 7174952
The VB form I have has about a dozen other fields and I am using an ADO Data Control for a bound form. How would I use your code that appears to use Recordsets with this?

Any help is appreciated.

Allen
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 7175251
I do not use Data Controls of any type.  They are more trouble than they are worth.

But if you insist on using the ADO Data Control than the following should work:

Sub ShowImage()
Dim stm As ADODB.Stream
Dim TempFileName As String

TempFileName = "c:\xyz.tmp"
If Not IsNull(ADODC1.Recordset.Fields("Image").Value) Then
 Set stm = New ADODB.Stream
 With stm
    .Type = adTypeBinary
    .Open
    .Write ADODC1.Recordset.Fields("Image").Value
    .SaveToFile TempFileName, adSaveCreateOverWrite
    .Close
 End With
 Set stm = Nothing
 Picture1.Picture = LoadPicture(TempFileName)
End If

End Sub

Anthony
0
 

Author Comment

by:awilson1111
ID: 7175328
The reason I am using Data Controls is that all of the programmers here and to those I will be making the presentation to tomorrow are all COBOL/RPG programmers used to programming in a line editor on a dumb terminal. The whole idea of programming in an event driven visual environment is very confusing to them. So I am making the client/server presentation as simple as possible so they can get a feel of how VB works with SQL Server and how it would operate.

But since you brought it up do you know of any sites or sources where I can get a good overview/comparison of bound/unbound - data control/ADO?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7175465
Not really.  Most developers that have used a Data Control once in a non-trivial app, never do again.

There maybe one misconception on your part, and that is assuming that in order to use Bound controls you need to use a Data Control.  That is not true.

Good luck in your presentation,

Anthony
0
 

Author Comment

by:awilson1111
ID: 7182682
acperkins  put in a lot of effort on this question
0
 
LVL 13

Expert Comment

by:danblake
ID: 10506888
image import: I have posted a previous solution here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20501232


This allows import via DTS/Bulk Insert with a format file/(or even at a stretch bcp? -- untested).
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now