asked on

Add pictues (.bmp) to SQL Server Image field

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?

Anthony Perkins

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

awilson1111

ASKER

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

Anthony Perkins

awilson1111

ASKER

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.

Anthony Perkins

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

awilson1111

ASKER

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.

Anthony Perkins

Post a message in Comminity Support (https://www.experts-exchange.com/commspt/) to this effect.

Anthony

erde

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

awilson1111

ASKER

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.

erde

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

???

awilson1111

ASKER

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.

miron

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.

awilson1111

ASKER

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.

Anthony Perkins

>>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

Anthony Perkins

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

awilson1111

ASKER

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.

Anthony Perkins

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

awilson1111

ASKER

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

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awilson1111

ASKER

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?

Anthony Perkins

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

awilson1111

ASKER

acperkins put in a lot of effort on this question

danblake

image import: I have posted a previous solution here:
https://www.experts-exchange.com/questions/20501232/how-can-i-import-jpg-file-to-sql-server-2000.html

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