How to insert picture into Image column?

I'm using SQL Server 2000. What's the command to insert a picture (.bmp, .jp
g.
..) into an Image type column?
Regards,
Pedestrian, Penang.
Message posted via http://www.webservertalk.comi've had luck with BULK INSERT. Here's an example of how to use
it for binary files. The only awkwardness is that
you need to get the file size into the format file before each import,
and how best to automate that will depend on the particular situation.
set nocount on
go
create table DocFiles (
fileNum int identity(1,1),
fName varchar(40),
doc image
)
/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
with the byte length of the .doc file in place of 12755529:
8.0
1
1 SQLIMAGE 0 12755529 "" 1 c1
SQL_Latin1_General_Cp1_CI_AI
*/
-- Create the format file with T-SQL, if desired:
exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell
'echo 1 SQLIMAGE 0 12755529 "" 3 zf
SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
with (
FORMATFILE='e:\txtsrv\doc.fmt'
)
update DocFiles
set fName = 'e:\txtsrv\yourfile.doc'
where fName is NULL
select fileNum, fName as fileName, datalength(doc) as fileSize from DocFiles
GO
-- Steve Kass
-- Drew University
-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||here is another route using textcopy.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||Thanks for replying... Steve Kass... It's looks a bit complex here
I'll figure it out... since I don't see the purpose of the .fmt file...
Thanks for oj too...
Steve Kass wrote:
>i've had luck with BULK INSERT. Here's an example of how to use
>it for binary files. The only awkwardness is that
>you need to get the file size into the format file before each import,
>and how best to automate that will depend on the particular situation.
>set nocount on
>go
>create table DocFiles (
> fileNum int identity(1,1),
> fName varchar(40),
> doc image
> )
>/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
> with the byte length of the .doc file in place of 12755529:
>8.0
>1
>1 SQLIMAGE 0 12755529 "" 1 c1
>SQL_Latin1_General_Cp1_CI_AI
>*/
>-- Create the format file with T-SQL, if desired:
>exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell
>'echo 1 SQLIMAGE 0 12755529 "" 3 zf
>SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
>bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
>with (
> FORMATFILE='e:\txtsrv\doc.fmt'
> )
>update DocFiles
>set fName = 'e:\txtsrv\yourfile.doc'
>where fName is NULL
>select fileNum, fName as fileName, datalength(doc) as fileSize from DocFile
s
>GO
>-- Steve Kass
>-- Drew University
>-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1sql

how to insert picture into a table

how do I do that?
can I do this in query analyzer?Depends on your coding language, you should check in google for stream
+upload +sql server +<yourcodinglanguage>
Or just write your coding language here, perhaps we can provide you with
some interesting links.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
> how do I do that?
> can I do this in query analyzer?
>|||my table pictures is empty.
CREATE TABLE [dbo].[pictures] (
[pid] [int] NULL ,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I use textcopy.exe in dos environment, but I got error.
WHAT HAPPEN? I don't understand ERROR: Row retrieval failed.
I want to insert it, not update it. does that mean textcopy.exe only do
update?
TEXTCOPY Version 1.0
DB-Library version 8.00.2039
Type the SQL Server to connect to: LOCALW
Type your login: brit
Type your password: brit
Type the database: Northwind
Type the table: pictures
Type the text or image column: picture
Type the where clause: where 1=1
Type the file: C:\teaser_head2.jpg
Type the direction ('I' for in, 'O' for out): I
ERROR: Row retrieval failed.
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Depends on your coding language, you should check in google for stream
> +upload +sql server +<yourcodinglanguage>
> Or just write your coding language here, perhaps we can provide you with
> some interesting links.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Jen,
I don't want to use coding language such as C# or java to insert
picture,
I only want to use SQL build-in utility and/or straight SQL query to do
that. so I don't think textcopy is an option since it only does update.
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Depends on your coding language, you should check in google for stream
> +upload +sql server +<yourcodinglanguage>
> Or just write your coding language here, perhaps we can provide you with
> some interesting links.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||You can simply use DDL:
CREATE TABLE [dbo].[pictures] (
[pid] [int] NULL ,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into pictures
values (1,0x000000000101020201010)
So that part is easy. The hard bit is that you have to take your image and
turn it into the hexidecimal format. Can't help you there, I regret.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%238hiZfVtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Jen,
> I don't want to use coding language such as C# or java to insert
> picture,
> I only want to use SQL build-in utility and/or straight SQL query to do
> that. so I don't think textcopy is an option since it only does update.
>
>
>
> "Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote
> in message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
>|||One way to hack around it is to initialize row for textcopy and then update
picture in that row using textcopy.exe
so when you do select picture from the table, you 'll see hexidecimal code.
unfortunately, I can't set image to a local variable in sql server. I guess
I can't avoid application programming then.
thanks
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:efRiZ5VtFHA.1168@.TK2MSFTNGP10.phx.gbl...
> You can simply use DDL:
> CREATE TABLE [dbo].[pictures] (
> [pid] [int] NULL ,
> [picture] [image] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into pictures
> values (1,0x000000000101020201010)
> So that part is easy. The hard bit is that you have to take your image
> and turn it into the hexidecimal format. Can't help you there, I regret.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:%238hiZfVtFHA.3080@.TK2MSFTNGP15.phx.gbl...
>

how to insert images...

Complete SQL newbie here.......running SQL 2k.
I created a datatype called 'Picture' of type Image using Enterprise
Manager's Design Table function.
My question, how can I specify what the image is for a given table
row/entry? For text data types I can just type in what I want the
value to be.
Most answers I've seen talk about running scripts to do inserts.
There's got to be a drag 'n drop or File/Open type way of doing this.
I mean I remember watching people do this with a database app on a
NextStation way back when (drag n drop).....you'd think the mighty
SQL Server 2000 would have similiar abilities.
Any help appreciated.
J.Sorry, SQL Server isn't a WYSIWYG editor, and there is no drag 'n' drop
interface.
In most situations, it's probably not a good idea to store an image in your
database anyway... See for more info:
http://www.aspfaq.com/show.asp?id=2149
"James" <lee.james@.spartan.ab.ca> wrote in message
news:17084052.0403121132.1aa413a7@.posting.google.com...
> Complete SQL newbie here.......running SQL 2k.
> I created a datatype called 'Picture' of type Image using Enterprise
> Manager's Design Table function.
> My question, how can I specify what the image is for a given table
> row/entry? For text data types I can just type in what I want the
> value to be.
> Most answers I've seen talk about running scripts to do inserts.
> There's got to be a drag 'n drop or File/Open type way of doing this.
> I mean I remember watching people do this with a database app on a
> NextStation way back when (drag n drop).....you'd think the mighty
> SQL Server 2000 would have similiar abilities.
> Any help appreciated.
> J.|||Hi James,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if our community member's information is helpful
to your question. For any more question, please post your message here and
we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql

how to insert a picture in Crystal Report reading as binary

hi all,
how to insert a picture in Crystal Report reading as binary from database (SQL SERVER).isn't any guru here to solve my problem

i also used the follwing but not working


Picture1.SetoleLocation app.path & "\" & Field1.Value



i have a picture box in crystal report and field in table which stores path name of the picture file.