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 numbers from a text box to a Sql database table column’s type numeric?

Hi,

I am getting an error when I try to insert a number typed in a text box control into a Sql database table column's type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:

System.FormatException: Input string was not in a correct format.
Line 428: CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.decimal, "Measure"))

A piece of the SP inside Sql server:

USE market26
GO
ALTER PROC new_offer
@.Offer_id bigint, @.Measure numeric(6,2) = null, …

What is wrong? Why it doesn' t accept the number typed in my text box?

Thank you,
CesarThis is not correct:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

This would probably work (not tested):

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal);
mParm.Size = 13; // Max size in Bytes of the Decimal number
mParm.Precision = 8; // Total number of digits allowed (right + left of decimal point
mParm.Scale = 2; // Set the number of decimal places the Parameter value is resolved
mParm.set_IsNullable(true);
mParm.Value = decimal.Parse(myTextBox.Text.Trim());
CmdInsert.Parameters.Add(mParm)

|||Hi,

I always write my ASP.NET commands to talk with Sql Stored Procedures thus:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

I think that it is correct, and it works fine.

I have never used a text field to insert numeric data into Sql database, but, I have to define all these parameter properties in order to pass a numeric data type to the database?

Thanks|||That's interesting. There is no Constructor attributed to the SqlParameter Class which accepts a Constructor looking like this:

New SqlParameter(<string>,<SqlDbType Enum>, <string>)

as far as I know...But hey, go for it!|||Take a look at this piece of an article and let me know what do you think about it:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' Set up parameter for stored procedure
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@.CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"
cmdOrders.Parameters.Add(prmCustomerID)
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This example makes all the parameter settings explicit. Some developers like this style, and it's good for instructional purposes. However, some developers prefer an equivalent alternative that has fewer lines of code:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
cmdOrders.Parameters.Add(New _
 SqlParameter("@.CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@.CustomerID").Value = "ALFKI"
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This code behaves exactly the same as the previous example. However, it only needs two lines of code for each parameter rather than six. When a stored procedure has a lot of parameters (as some of our later examples do), this can be quite a difference on lines of code required, so we'll use that form from this point onward.

The complete article is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp|||Hey! Perhaps you have misunderstood me. I meant that I always use this structure:
(Which I knew that something was wrong)

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

Instead of this:

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal)
mParm.SourceColumn = "Measure"
CmdInsert.Parameters.Add(mParm)

I knew that something was wrong, I suspected that something it lacked in my SqlParameter, and you helped me with your example!, it lacked only the size property which in my case is 5. I am sorry, the only thing that I saw strange is the structure you wrote, and the amount of parameters which I don' t need to use (in this case). I always use the shorter alternative ;-).

So, the correct SqlParameter in my case is:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal,5, "Measure"))

And now works fine.
Thank you very much!
Cesar

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 an image to sql db?

Hi there! how to insert an image to sql db using FileUpload Control? I saw a data type named "image", so it means to say you are able to insert an image and not the path, am I right? I spent a lot of time to do this but still, I don't know how to store the image to the db. Please post your example code if you want... I will appreciate all your suggestions or comments about this. Thanks!

Yes, you are on the right track, you want to get the file into a btye array then you can assign it to a sql image parameter. It works with any file not just images, it really means file image.

There is some stuff in this thread that may help

http://forums.asp.net/thread/1322372.aspx

Hope it helps,

Joe

|||http://forums.asp.net/thread/1405280.aspx

how to insert a NULL in a table

hello,
in a table i have a field of type bit which allows NULL.
if a new record is created and the field is not set a NULL value is created.
Now my question: if the field was set to the value 0 or 1 how can i reset
that field manualy to NULL. Can i use some key STRG + ...?
thanksUsing the Enterprise manager you can use STRG + 0 to insert a NULL,
using an update statement it would be:
UPDATE Sometable SET SomeColumn = NULL Where is = SomeIdValue
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||thanks for your help
"Jens" wrote:

> Using the Enterprise manager you can use STRG + 0 to insert a NULL,
> using an update statement it would be:
> UPDATE Sometable SET SomeColumn = NULL Where is = SomeIdValue
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Through the GUI its usually CTRL 0 together.
With command line its
UPDATE <table>
SET yourcol = NULL
WHERE ...
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:B7AB5BA4-8AAB-425C-9668-1C435E96A613@.microsoft.com...
> hello,
> in a table i have a field of type bit which allows NULL.
> if a new record is created and the field is not set a NULL value is
> created.
> Now my question: if the field was set to the value 0 or 1 how can i reset
> that field manualy to NULL. Can i use some key STRG + ...?
> thanks
>|||thanks Toni
"Tony Rogerson" wrote:

> Through the GUI its usually CTRL 0 together.
> With command line its
> UPDATE <table>
> SET yourcol = NULL
> WHERE ...
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:B7AB5BA4-8AAB-425C-9668-1C435E96A613@.microsoft.com...
>
>

How to insert a datetime into sql sever

Hi everyone,

How do you insert this string value lable1.text="2006-08-30 09:00:00" into a data column like startdate (type: datetime) in sql sever?

How do I convert this string value before I insert it into sql sever?

Thank you very much.

a123.

Here is a quick sample:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn2000"].ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO tbl_dt (dt) SELECT @.dt", conn);
cmd.Parameters.Add("@.dt", SqlDbType.DateTime).Value = DateTime.Parse(TextBox1.Text);
cmd.ExecuteNonQuery();

}

How to increment a column with varchar data type

hi,
is there any method for incrementing the varchar field
i have an emp table with columns
create table emprecord(eno varchar(10), ename varchar(20))
i cant give eno column with identity field because identity column must
be of data type int, bigint, smallint, tinyint, or decimal or numeric
insert into emprecord values ('MH1001','satish')
insert into emprecord values ('MH1002','rehman')
my problem is there any method --when i pass only the ename the eno
field should be automatically incremented as MH1003 --each time it has
to check for the highest eno ie.., MH1002 is my highest eno in the
table emprecord
i think there is sequence option in MS Sql server-- if so how to use
sequence
pls help me
thanks
satishTry a search about "SQL Server identity values", but it supports integer
values only. You might need to make appropriate modifications on your table.
Martin C K Poon
Senior Analyst Programmer
====================================
"satish" <satishkumar.gourabathina@.gmail.com> ?
news:1145000321.252703.52230@.t31g2000cwb.googlegroups.com ?...
> hi,
> is there any method for incrementing the varchar field
> i have an emp table with columns
> create table emprecord(eno varchar(10), ename varchar(20))
> i cant give eno column with identity field because identity column must
> be of data type int, bigint, smallint, tinyint, or decimal or numeric
> insert into emprecord values ('MH1001','satish')
> insert into emprecord values ('MH1002','rehman')
> my problem is there any method --when i pass only the ename the eno
> field should be automatically incremented as MH1003 --each time it has
> to check for the highest eno ie.., MH1002 is my highest eno in the
> table emprecord
> i think there is sequence option in MS Sql server-- if so how to use
> sequence
>
> pls help me
> thanks
> satish
>|||You could use a computed column instead|||satish
create table #t
(
rowid int not null identity(1,1) primary key,
empno AS 'MH'+CAST(rowid AS VARCHAR(10)),
empname VARCHAR (50)
)
insert into #t(empname) VALUES ('Smith')
insert into #t (empname)VALUES ('Clinton')
insert into #t (empname) VALUES ('Brown')
select empno,empname from #t
drop table #t
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1145000321.252703.52230@.t31g2000cwb.googlegroups.com...
> hi,
> is there any method for incrementing the varchar field
> i have an emp table with columns
> create table emprecord(eno varchar(10), ename varchar(20))
> i cant give eno column with identity field because identity column must
> be of data type int, bigint, smallint, tinyint, or decimal or numeric
> insert into emprecord values ('MH1001','satish')
> insert into emprecord values ('MH1002','rehman')
> my problem is there any method --when i pass only the ename the eno
> field should be automatically incremented as MH1003 --each time it has
> to check for the highest eno ie.., MH1002 is my highest eno in the
> table emprecord
> i think there is sequence option in MS Sql server-- if so how to use
> sequence
>
> pls help me
> thanks
> satish
>|||there are no identity columns defined in my table in that case how to
do
this is my table
create table emprecord(eno varchar(10), ename varchar(20))
i always pass only ename|||It's better to break empno into empPrefix (varchar(2)) and empSerial (int).
or, if the table structure cannot be modified, I will opt to use stored
procedure (and/or other programming means).
create table #MyTempEmpRecord06041402
(
empno varchar(10),
empname VARCHAR (50)
)
go
-- Assumes that @.myEmpnoPrefix is always having 2 characters
create procedure #MyInsertEmpname06041402
@.myEmpnoPrefix varchar(2),
@.myEmpname varchar(50)
as
insert #MyTempEmpRecord06041402
select @.myEmpnoPrefix
+ (select convert(varchar(8), convert(int, isnull(max(substring(empno,
1 + len(@.myEmpnoPrefix), 8)), '0')) + 1) as MySerial
from #MyTempEmpRecord06041402
where left(empno, len(@.myEmpnoPrefix)) = @.myEmpnoPrefix)
as empno,
@.myEmpname as empname
go
exec #MyInsertEmpname06041402 'MH', 'Smith'
exec #MyInsertEmpname06041402 'MH', 'Clinton'
exec #MyInsertEmpname06041402 'MP', 'Smith'
exec #MyInsertEmpname06041402 'MP', 'Brown'
exec #MyInsertEmpname06041402 'MH', 'Brown'
go
select * from #MyTempEmpRecord06041402
go
drop procedure #MyInsertEmpname06041402
go
drop table #MyTempEmpRecord06041402
Martin C K Poon
Senior Analyst Programmer
====================================
"satish" <satishkumar.gourabathina@.gmail.com> ?
news:1145007066.106515.192790@.t31g2000cwb.googlegroups.com ?...
> there are no identity columns defined in my table in that case how to
> do
> this is my table
> create table emprecord(eno varchar(10), ename varchar(20))
> i always pass only ename
>

how to increase size of datatype...

Dear

I am using varchar data type in my table.it provides maximum 8000 character. but i want more than that.

how to increase size more than 8000

i want to use text datatype but when i type in length it doenst type.it displays only 16.i cant change to another value.

please help me out

Waiting for reply

Regards,
ASIFTEXT does not take a length the way VARCHAR does

it's just TEXT

how to implement unique key on multiple field

hello guys,

I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?

thank you.

Sure, i you wnt to check them only per column you can put a UNIQUE Constraint on those columns, that will check like the primary key for uniqueness.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de