How to trim off the time part of a DateTime field

I have a tabls of SQL 2000, about the data of student.
The data is migrate from another databse,
There is a field called birthday, it stores the data with the formate like:
1994/11/03 AM 02:09:00
When I bind the field to datagrid, how can I suppress the time part , or
how can I trim the time part form database?Hi,
You can try as
select convert(varchar(10), getdate(), 102)
thanks and regards
Chandra
"ad" wrote:

> I have a tabls of SQL 2000, about the data of student.
> The data is migrate from another databse,
> There is a field called birthday, it stores the data with the formate like
:
> 1994/11/03 AM 02:09:00
> When I bind the field to datagrid, how can I suppress the time part , or
> how can I trim the time part form database?
>
>|||You can also check for available types on
2f3o.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"Chandra" wrote:
[vbcol=seagreen]
> Hi,
> You can try as
> select convert(varchar(10), getdate(), 102)
> thanks and regards
> Chandra
>
> "ad" wrote:
>|||I have study the article you mentioned, but I still cann't figure out how to
do.
If the field name is Birthday, could you give me a exmaple?
"Chandra" <Chandra@.discussions.microsoft.com> glsD
:BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
> You can also check for available types on
>
http://msdn.microsoft.com/library/d..._ca-co_2f3o.asp[v
bcol=seagreen]
> thanks and regards
> Chandra
> "Chandra" wrote:
>
like:[vbcol=seagreen]
or[vbcol=seagreen]|||E.g.
SELECT convert(varchar(10), birthday, 102) FROM YourTableName
Regards
Steen
ad wrote:[vbcol=seagreen]
> I have study the article you mentioned, but I still cann't figure out
> how to do.
> If the field name is Birthday, could you give me a exmaple?
>
> "Chandra" <Chandra@.discussions.microsoft.com> glsD
> o_2f3o.asp" target="_blank">http://msdn.microsoft.com/library/d... />
o_2f3o.asp

How to trim off the time part of a DateTime field

I have a tabls of SQL 2000, about the data of student.
The data is migrate from another databse,
There is a field called birthday, it stores the data with the formate like:
1994/11/03 AM 02:09:00
When I bind the field to datagrid, how can I suppress the time part , or
how can I trim the time part form database?
Hi,
You can try as
select convert(varchar(10), getdate(), 102)
thanks and regards
Chandra
"ad" wrote:

> I have a tabls of SQL 2000, about the data of student.
> The data is migrate from another databse,
> There is a field called birthday, it stores the data with the formate like:
> 1994/11/03 AM 02:09:00
> When I bind the field to datagrid, how can I suppress the time part , or
> how can I trim the time part form database?
>
>
|||You can also check for available types on
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp
thanks and regards
Chandra
"Chandra" wrote:
[vbcol=seagreen]
> Hi,
> You can try as
> select convert(varchar(10), getdate(), 102)
> thanks and regards
> Chandra
>
> "ad" wrote:
|||I have study the article you mentioned, but I still cann't figure out how to
do.
If the field name is Birthday, could you give me a exmaple?
"Chandra" <Chandra@.discussions.microsoft.com> glsD
:BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
> You can also check for available types on
>
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp[vbcol=seagreen]
> thanks and regards
> Chandra
> "Chandra" wrote:
like:[vbcol=seagreen]
or[vbcol=seagreen]
|||E.g.
SELECT convert(varchar(10), birthday, 102) FROM YourTableName
Regards
Steen
ad wrote:[vbcol=seagreen]
> I have study the article you mentioned, but I still cann't figure out
> how to do.
> If the field name is Birthday, could you give me a exmaple?
>
> "Chandra" <Chandra@.discussions.microsoft.com> glsD
> http://msdn.microsoft.com/library/de...ca-co_2f3o.asp

How to trim off the time part of a DateTime field

I have a tabls of SQL 2000, about the data of student.
The data is migrate from another databse,
There is a field called birthday, it stores the data with the formate like:
1994/11/03 AM 02:09:00
When I bind the field to datagrid, how can I suppress the time part , or
how can I trim the time part form database?Hi,
You can try as
select convert(varchar(10), getdate(), 102)
thanks and regards
Chandra
"ad" wrote:
> I have a tabls of SQL 2000, about the data of student.
> The data is migrate from another databse,
> There is a field called birthday, it stores the data with the formate like:
> 1994/11/03 AM 02:09:00
> When I bind the field to datagrid, how can I suppress the time part , or
> how can I trim the time part form database?
>
>|||You can also check for available types o
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
thanks and regards
Chandra
"Chandra" wrote:
> Hi,
> You can try as
> select convert(varchar(10), getdate(), 102)
> thanks and regards
> Chandra
>
> "ad" wrote:
> >
> > I have a tabls of SQL 2000, about the data of student.
> > The data is migrate from another databse,
> > There is a field called birthday, it stores the data with the formate like:
> > 1994/11/03 AM 02:09:00
> >
> > When I bind the field to datagrid, how can I suppress the time part , or
> > how can I trim the time part form database?
> >
> >
> >|||I have study the article you mentioned, but I still cann't figure out how to
do.
If the field name is Birthday, could you give me a exmaple?
"Chandra" <Chandra@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
> You can also check for available types on
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> thanks and regards
> Chandra
> "Chandra" wrote:
> > Hi,
> > You can try as
> > select convert(varchar(10), getdate(), 102)
> >
> > thanks and regards
> > Chandra
> >
> >
> > "ad" wrote:
> >
> > >
> > > I have a tabls of SQL 2000, about the data of student.
> > > The data is migrate from another databse,
> > > There is a field called birthday, it stores the data with the formate
like:
> > > 1994/11/03 AM 02:09:00
> > >
> > > When I bind the field to datagrid, how can I suppress the time part ,
or
> > > how can I trim the time part form database?
> > >
> > >
> > >|||E.g.
SELECT convert(varchar(10), birthday, 102) FROM YourTableName
Regards
Steen
ad wrote:
> I have study the article you mentioned, but I still cann't figure out
> how to do.
> If the field name is Birthday, could you give me a exmaple?
>
> "Chandra" <Chandra@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
>> BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
>> You can also check for available types on
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
>> thanks and regards
>> Chandra
>> "Chandra" wrote:
>> Hi,
>> You can try as
>> select convert(varchar(10), getdate(), 102)
>> thanks and regards
>> Chandra
>>
>> "ad" wrote:
>>
>> I have a tabls of SQL 2000, about the data of student.
>> The data is migrate from another databse,
>> There is a field called birthday, it stores the data with the
>> formate like: 1994/11/03 AM 02:09:00
>> When I bind the field to datagrid, how can I suppress the time
>> part , or how can I trim the time part form database?

how to trim a field value in rss

can any one explain how to trim a value on bothside in rss

for eg:

'asd '

' asd'

i wanna this output 'asd'............

can i give this command

=Format(Fields!company.Value.Trim)

You can use Trim function like this:

Trim(Fields!company.Value)

This will trim both leading and trailing spaces.

Pls mark this post as answer if your problem is solved.

Shyam

How to trap the results of constraints in SQL Server 2005from Visual studio C# Code?

Hi all,

Suppose I have set a CHECK constraint to a column where Salary field is not permitted to be less than 1000 or greater than 10000.

In this situation, when I insert a new record with salary as 10, using a stored procedure from Visual Studio, how will I trap the error from C# Code?

Thanks

Tomy

Your best bet would be to have layered constraints. Your business logic should also make sure that the data being entered is between 1000 and 10000, so that the violation would never reach the database. Database constraints should be left in place for people who like to edit the database directly.

While you can get a violated constraint error from the database, I don't believe it would be phrased properly to display to your users, which would mean lots of parsing in order to make the error presentable and user-friendly. It would be better to use a range validator to prevent the user from entering incorrect information.

As for trapping the error, use Try/Catch blocks

try{}catch (System.Data.SqlClient.SqlException ex){// Handle SQL Exceptions here; // all sql exceptions fall under this exception type, // but the errorcode/errors will be different.}catch (Exception ex){// handle all other errors here.}

|||

Thanks a lot.

How to translate varchar into varbinary?

Hi everyone,

We're trying to migrate a varchar field from Sql2k to varbinary in a sql25k through a dtsx package. We get an error which tell us: "data will be lost".

Does anyone have any idea about that?

Thanks for your time and inputs,

Do it in the extract query, T-SQL supports explicit casts between those two types

DECLARE @.v varchar(10)

SET @.v = '0123456789'

SELECT CAST(@.v AS varbinary(10))

How to tell if there is something in a binary field

I tried to upload a pdf file into a binary field using asp upload. I
cannot tell if there is anything in the field using query analyzer, and I wa
s
wondering what shows in the grid when something exists there. It is just
showing as <binary>Don't use a grid for this kind of thing.
SELECT DATALENGTH(col) FROM Table
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Linda Lalewicz" <LindaLalewicz@.discussions.microsoft.com> wrote in message
news:3208A836-96ED-406C-BF8A-305946E6BFE7@.microsoft.com...
>I tried to upload a pdf file into a binary field using asp upload. I
> cannot tell if there is anything in the field using query analyzer, and I
> was
> wondering what shows in the grid when something exists there. It is just
> showing as <binary>|||I also found that a select for that field would return something if there wa
s
something in there, if not it came back null.
Now I need to figure out why the clues that you gave me yesterday don't seem
to show me my pdf file. I just see a blank page with no errors, no pdf. :
(
wahhhh
"Aaron [SQL Server MVP]" wrote:

> Don't use a grid for this kind of thing.
> SELECT DATALENGTH(col) FROM Table
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Linda Lalewicz" <LindaLalewicz@.discussions.microsoft.com> wrote in messag
e
> news:3208A836-96ED-406C-BF8A-305946E6BFE7@.microsoft.com...
>
>sql

How to Take a Column Value to Make it a Field ?

if a customer can have up to 10 acounts and each account has a balance,

in customer_account table, the same custID repeats and relates to an

accountID and each account has its own balance.

How to display each customer in ONE row and list his account one next to another ?

for example:

custID, acct#1, acct#1Balance, acct#2, acct#2Balance, acct#3, acct#3Balance

how to do write SQl to achive it ?

This is chanllenging to me...

Your advise is greatly appreciated !

Most likely, you will find value in the new PIVOT keyword.

This previous thread may help direct you.

|||

just to add what Arnie has said.. PIVIOT is only available in SQL Server 2005.

Madhu

|||

Since you have to pivot more than one column, I recommend to use the Case..When (legacy approach).

Here the sample, (this sample is for SQL Server 2005 only – let me know if you SQL Server 2000)

Code Snippet

Create Table #accounts (

[CustomerId] int ,

[AccountNo] int ,

[Balance] money

);

Insert Into #accounts Values('1','272837282','100000');

Insert Into #accounts Values('1','272837298','20000');

Insert Into #accounts Values('1','272837390','1000');

Insert Into #accounts Values('1','272937282','100000');

Insert Into #accounts Values('2','272437282','10000');

Insert Into #accounts Values('3','572837282','83734937');

Insert Into #accounts Values('3','572837285','28292');

;With Grouped

as

(

Select * ,Row_Number() Over(Partition By CustomerId Order BY CustomerId) [Acct#] from #accounts

)

Select

CustomerId

,Max(Case When [Acct#] =1 Then [AccountNo] End) [acct#1]

,Max(Case When [Acct#] =1 Then [Balance] End) [acct#1Balance]

,Max(Case When [Acct#] =2 Then [AccountNo] End) [acct#2]

,Max(Case When [Acct#] =2 Then [Balance] End) [acct#2Balance]

,Max(Case When [Acct#] =3 Then [AccountNo] End) [acct#3]

,Max(Case When [Acct#] =3 Then [Balance] End) [acct#3Balance]

,Max(Case When [Acct#] =4 Then [AccountNo] End) [acct#4]

,Max(Case When [Acct#] =4 Then [Balance] End) [acct#4Balance]

,Max(Case When [Acct#] =5 Then [AccountNo] End) [acct#5]

,Max(Case When [Acct#] =5 Then [Balance] End) [acct#5Balance]

--You can add upto n accounts as per your data

From

Grouped

Group By

CustomerId

how to suppress zeroes after decimal point at the end in a value

hi,
i have a sql table field price and datatype is
decimal 13(20,6).
when i insert values to this field, values are being
inserted correctly. i.e. 13.45 inserted as 13.45 and
145.653 inserted as 145.653 only.
But while fetching only the values are coming as 13.450000,
145.653000, because the datatype is decimal 13(20,6) with
6 decimals. but i want 13.45, 145.653 as in the table.
How suppress the unwanted zeroes at the end of those
numbers.
any help.
thanks,
hari.
see following example:
drop table test
create table test(c1 decimal (15,5))
insert into test values (3.567000)
insert into test values (232233.567000)
insert into test values (3.567)
query:
select c1,reverse(substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) - 1)
)) 'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||hi thanks,
but it is not working for whole number like ex:11120
it is giving it as 11120. (with point at the end)
how to do that.
thanks,
hari.
>--Original Message--
>see following example:
>drop table test
>create table test(c1 decimal (15,5))
>insert into test values (3.567000)
>insert into test values (232233.567000)
>insert into test values (3.567)
>query:
>select c1,reverse(substring(reverse(cast(c1 as varchar
(25))) ,
>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as
>varchar(25)))) - 1)
>)) 'no_zeros'
>from test
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||try:
select c1, reverse(
case when substring(substring(reverse(cast(c1 as varchar(25)))
,
patindex('%[^0]%', reverse(cast(c1 as varchar(25))))
,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1)) ,1,1) = '.'
then
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) + 1 ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 2))
else
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1))
end)
'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"hari" <anonymous@.discussions.microsoft.com> wrote in message
news:207601c4a145$b5262d90$a601280a@.phx.gbl...[vbcol=seagreen]
> hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
> thanks,
> hari.
> (25))) ,
> reverse(cast(c1 as
|||See if this works:
select
replace(rtrim(replace(
replace(rtrim(replace(
c1,'0',' ')),' ','0')
,'.',' ')),' ','.')
from test
Steve Kass
Drew University
hari wrote:
[vbcol=seagreen]
>hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
>thanks,
>hari.
>
>(25))) ,
>
>reverse(cast(c1 as
>
>

How to suppress a field if it is null

I have a report where it displays demographic information. The Address2 field
is sometimes NULL and I haven't found a way to suppress this field. The only
thing I can do is make it invisible/visible with an IIF statement. I know in
Crystal Reports you could check a box that said "SUPPRESS IF BLANK" and this
would move all the other fields up on the report accordingly. With SQL
Reporting services it doesn't adjust the fields underneath it so I have a
blank/empty space. Does anyone know of any other things I can try.
ThanksHi GORAMS,
Are you using a table control? If so, take a look at the actual detail
line for Address2, and make sure that your IIF statement to control
visibility is set on the entire tablerow as opposed to just the
Address2 textbox. Just setting the visibility property on the Address2
textbox would cause your problem if the table has more than column.
Does this help?
Matt A
Rpeorting Services Newsletter at www.reportarchitex.com|||RS is shrinking fields if you specify that it is invisible (for example if
you put in Hidden property "=Fields!Something.Value is Nothing"). But, as in
CR, that depends of other fields in line, so if one is supperesed and other
one is not, it will not move fields under supperesed one up...
You can play arround little with columns maybe, or lists if you want to do
something like that..
Stjepan
"GORAMS" <GORAMS@.discussions.microsoft.com> wrote in message
news:A28602C1-D97C-4A19-9ABD-6A06D6A4A9BA@.microsoft.com...
>I have a report where it displays demographic information. The Address2
>field
> is sometimes NULL and I haven't found a way to suppress this field. The
> only
> thing I can do is make it invisible/visible with an IIF statement. I know
> in
> Crystal Reports you could check a box that said "SUPPRESS IF BLANK" and
> this
> would move all the other fields up on the report accordingly. With SQL
> Reporting services it doesn't adjust the fields underneath it so I have a
> blank/empty space. Does anyone know of any other things I can try.
> Thanks

How to summarize a text filed ?

Greetz!

I need to perform a SUM on a text field in CR 8.0. I tried creating a formula filed which contained something like this: SUM {textfield.text} but the report said: Unknown filed name. Now, the text field ( crystal reports text object actually ) is not a database field, but a plain text box which contains certain data. Can this be achieved at all ? Summarizing or any other function over a text box ?

Thnx.Hi

It is not possible to use SUM () for a non database field.|||Ok. Well, is there an alternative ? This text box mentioned earlier contains a vital data which I collected from a foreign source (used a text driver in VB to read it from a text delimited file ), and I can display it's value in a details section, but as I said earlier, I cannot SUM it. So .. if there's an alternative approach using crystal, I'd more then appritiate it.

How to Sum last three groups

I have a report that has a Payment field on the details line. It is then grouped by Month. The group footer contains a sum of the payments for that month.

How do I create a formula that displays the sum for the last three months?

Mon Sum 3monthSum
Jan 20.00 20.00 //(Jan)
Feb 10.00 30.00 //(Jan - Feb)
Mar 40.00 70.00 //(Jan - Mar)
Apr 30.00 80.00 //(Feb - Apr)
May 15.00 85.00 //(Mar - May)

Thanks,Do you want to find summation of sum of each month?
Make use of Running total feature

How to sub total a calculated column?

In my report body, there is table, which contains a column displaying calculated data based on a field from a dataset. The expression for the cell called "calculatedCost" below this column is :

"=Iif(Fields!Facotr.Value=0, Fields!cost.Value * Fields!Quantity.Value *1,Fields!cost.Value * Fields!Quantity.Value *Fields!Factor.Value)

The data row in the table will be grouped by fields called Code like "A", "B" and etc. Now I need to sub total the above calculated column under each group, i.e. Subtotals for Code A and Code B. How can I do this? I tried the following expression:

=Sum(ReportItems("calculatedCost").Value)

But I got an error saying that Aggregate functions couldn't be used in report body. Then how can I refer the value of cell "calculatedCost"? Any suggestion will be highly appreciated..

You could perform the calculation in your dataset and not display that field.

SELECT *, (<Expression HERE>) As CalcField FROM Query

Then use that field in the summary field.

=SUM(Fields!CalcField.Value)

|||Thanks a lot. I got it work!

How to store very large document data?

Dear all,

I am facing a problem while storing Very large document data to a single image field in SQL Server 2000. It takes very much time for documents in size upto 10 MB. And its not possible to store data more than 10 MB. If I try it gives "Connection Write (Wrapper Write())" message with state 01000 and native 4.

I have used SQLPutData and SQLParamData for this purpose.

Is there any efficient way to do this? My concern is for less time.

Thanks in advance,
AvneeshI need urgent help regarding above problem. Would somebody please send some suggestions ASAP.

Thanks in advnace,
Avneesh

How to store large text-strings in a sql-field?

I will store text with more than 8.000 characters in a sql-database. I defined my field as data-type=text. But when I paste a large text string into the database, only some of the text is being saved. The text also changes to "<long text> so I can't change it.

I tried to make some asp-code to handle the problem, but I can't find the right constants to use according to the data-type=text. In my example I use the constant "adBinary".

strInfo = Request.Form("txtInfo")
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection = MyConn
.CommandText = "add_Info"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter ("@.Info", adBinary, adParamInput, , strInfo)
.Execute lngRecs, , adExecuteNoRecords
Set cmd = Nothing
End With

Is there anybode that can help me?I think you are using SQL-SERVER,
First check the maximum size allowed in TEXT type of Field.

I doubt if it can allow so many characters.|||I found the solution. In the asp-code, I use datatype "varchar" and number of characters like 2147483647.

how to store image in image field in sql server 2000

hi all,

i have created a table with image field in it. Now i just want to store a jpeg file in it but not finding any way to do so.

how can i store any image ? what are the steps?

thanx in advance

Actually, the following statement will only work in sql2005. Sorry.

Try something like this:

Code Snippet

create table #bob

(id int, jpeg image)

insert into #bob

select 1, bulkcolumn

from openrowset(BULK 'c:\Winter.jpg', SINGLE_BLOB) AS table

HTH

|||

how to add using enterprise manager. I don't want to create a stored procedure for that or any sql statement.

Can i directly insert image as i can enter text into any text field?

|||

I don't think you're able to add them in via EM.

I think you can use textcopy (see Books Online) and there shouild an example of how this works in the pubimage.bat file in your install directory c:\program files\microsoft sql server\mssql\install\

HTH.

|||No way to do this in SSSM or EM.

If you favour doing this in SSMS in the next version of SQL Server, feel free to post a connect bug / feature request in the connect forums at www.connect.microsoft.com.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

how to store and retrive .DOC file in sql

Hi frds,

My question is how should i store a .DOC file in sql 2005 as image field and how to retrive the Doc stored in Image format

Thanks & regards

Sithender.s

Have a look at the technique for storing images in http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp (Storing and retrieving images from SQL Server using Microsoft .NET.)
http://www.codeproject.com/aspnet/PicManager.asp (How to upload files to Web pages in ASP.NET? How to read an image from a database using ADO.NET and display it in a Web page?)
http://www.codeproject.com/aspnet/NetPix.asp (This article explains an ASP.NET application to view and share photos online.)

This should give the basis for your intended document storage and retrieval.

|||

My question to you is why you want to store the .doc in Image data type. This data type is basically for backward compatibility with SQL Server 2000 and will not be available in next version of SQL Server. Use Varbinary data type instead of Image. However by using the following methods you can upload it to image data type as well. I have used file upload control of ASP.Net 2.0 to upload files in following example.

protectedvoid btnFileUpload_Click(object sender,EventArgs e) { if (FileUpload1.FileName.Length > 0) { int len = FileUpload1.PostedFile.ContentLength; String strDes = txtDescription.Text; byte[] file =newbyte[len]; FileUpload1.PostedFile.InputStream.Read(file, 0, len); clsDAL objDal =newclsDAL(); objDal.UploadFile(file,strDes); } } publicvoid UploadFile(Byte[] pFile,String pDesc) { SqlConnection cnnConn; SqlCommand cmmCommand; cnnConn =newSqlConnection(ConfigurationManager.ConnectionStrings["Connection String"].ConnectionString); cnnConn.Open(); cmmCommand =newSqlCommand(); cmmCommand.Connection = cnnConn; cmmCommand.CommandType =CommandType.StoredProcedure; cmmCommand.CommandText ="sp_UploadFile"; cmmCommand.Parameters.AddWithValue("@.pFile", pFile); cmmCommand.Parameters.AddWithValue("@.pDesc", pDesc); cmmCommand.ExecuteNonQuery(); }|||

Sorry, i am newbie in upload file.

May i know that where do i replace my sql statement and where do i should fill in the user is, password and server path like 192.xx.xxxx.xxx??

thanks.........

How to store an MS Word doc in a sql 2005 table

Hello,

I want to store the contents of an MS Word document (or a brief Word extract) in a database field. This document could contain carriage returns, boldface, etc. I have tried storing the Word doc in fields of type nchar, text, and sql_variant but they do not keep the Word formatting. I found that the "Extended Properties" attribute of a field seems to retain these properties. (Though if I recall right, Extended Properties has a length limitation and is a pain in the #*&_*^! to access)

What is the "right" way to do what I'm trying? Keep in mind that we may want to view this document under either "Query Analyzer" or print a hardcopy version. And we'd like both to be intelligible!

TIA,

Barkingdog

hello,

looks like an EDMS to me

1. you have to OCR(optical character recognition) the document to obtain the text content

that you want to view in sql query analyzer. OCR is a free feature of msoffice 2003.

2. Or. run an application that do office automation and read the text line by line.Save it as a string

then save to sql server. that is if you dont trust the OCR

3. to keep the document intact as it is. Save the document as a doc file then stream it in to a

sql server blob data type

4. stream out the doc and open it with msoffice when you want to view it.

5. you may want to apply fulltext feature on the result of no 1 and 2

regards,

|||In comment to joey, I would recommend the 3 +4 as you can store the data as a Blob and enable SQL Server to use fulltext search on that blob with the appropiate settings. You wont′need to store the data in "cleartext", binary will be enough to enable full-text capability.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

i see.

they need to read the docs through QA so i recommended an OCR

to store clear text.

thanks for the comment anyway

|||You are right, seems that I didnt′read that part in detail:-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de