SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 how many charaters can save in text datatype?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  03:14:16  Show Profile  Reply with Quote
i have datacolumn(datatype is text 16),it can't store whole long sentense .
for example :i want to save "Have a nice day!",it can save and show "Have a nice"
what's wrong?

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  03:50:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Books Online says 2147483647 characters. More than 2 billion that is.
Text are stored on a different place in the server and the 16 bytes you see is just a pointer to the place where the data is stored.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  03:56:52  Show Profile  Reply with Quote
so how can i retrieve all text from database ?
it also can't save all text of user input.
any suggession?

quote:
Originally posted by Peso

Books Online says 2147483647 characters. More than 2 billion that is.
Text are stored on a different place in the server and the 16 bytes you see is just a pointer to the place where the data is stored.



E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  03:59:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think your stored procedure has another dataype and that parameter length is set to 11 characters.
Or this "bug" is located in the front-end application.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  04:00:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or you are using a user-defined data type? Which in turn is a varchar/char datatype?



E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 04/14/2008 04:01:02
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  04:07:00  Show Profile  Reply with Quote
i can't set lenth for text in storedprocedure.
i've shown just sample sentense.
Actually datatype is text(16) .
when i try to save long sentense more then 16chars ,it can only save 16chars or lessthan 16.

Edited by - kwikwisi on 04/14/2008 04:07:33
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  04:08:45  Show Profile  Reply with Quote
no i use built-in datatype text.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  04:11:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Show us the stored procedure code.
Show us the database table definition.
Show us the front-end application code.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  04:18:14  Show Profile  Reply with Quote
This is stored procedure
CREATE PROCEDURE [dbo].[sp_AddAnsTemp]
@id int, @proid int, @prodesc text, @proansw text,@proreso text,@chk int
AS
insert into TempAns(UserID,ProblemID,ProblemDescription,ProblemAnswer,ProblemResolution,Chk)
values (@id, @proid, @prodesc, @proansw,@proreso,@chk)
GO

this is coding
Dim paramans As New SqlParameter("@proansw", SqlDbType.Text, 16)
Dim txtbox As New TextBox
txtbox = CType(item.FindControl("txtAns"), TextBox)
paramans.Value = txtbox.Text.Trim
cmd.Parameters.Add(paramans)


This is table

TempAns
(---,ProblemAnswer(text 16),--)

can u help me!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  04:20:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is the offending line!
Dim paramans As New SqlParameter("@proansw", SqlDbType.Text, 16)
You have told ADO to use a maximum of 16 characters.

Use
Dim paramans As New SqlParameter("@proansw", SqlDbType.Text ) instead


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  04:28:17  Show Profile  Reply with Quote
thanks alot

now works well for short sentense like:
"Blinking ADSL LED on router"

But still in problem for such long sentense:

"Problem was found when po/smtp.ti-sin.com was pinged and it was found that it was pointing to QALA IP. After a procedure from TL Sham, their mail client was configured to use the Proteus IP address for both incoming and outgoing server names. User now able to receive external emails on Outlook Express and Webmail."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  04:36:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Dim paramans As New SqlParameter("@proansw", SqlDbType.Text, 2147483647 )



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  04:51:40  Show Profile  Reply with Quote
some long sentense are saved but some aren't saved even shorter than long one which is saved.
what's wrong ?

Edited by - kwikwisi on 04/14/2008 04:54:59
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  05:06:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You tell me.

Run this code and post back the result
SELECT	COLUMN_NAME,
	ORDINAL_POSITION,
	DATA_TYPE,
	CHARACTER_MAXIMUM_LENGTH
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_NAME = 'TempAns'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  05:17:41  Show Profile  Reply with Quote
in other blank page?
just run as u write?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  05:23:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Replace 'TempAns' with the table you really are using.
I put 'TempAns' as search table because that is the name you posted earlier.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  05:30:28  Show Profile  Reply with Quote
in my table defnition ,datatype is text(16)
error is because of such length?
but i can't change
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  05:36:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Post the result, not your interpretation !



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  05:39:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Also, which long sentences are stored, and which are not stored?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

280 Posts

Posted - 04/14/2008 :  05:39:59  Show Profile  Reply with Quote
what's ordinal_position and information_sechema.columns
i don't understan well what u mean?

quote:
Originally posted by Peso

You tell me.

Run this code and post back the result
SELECT	COLUMN_NAME,
	ORDINAL_POSITION,
	DATA_TYPE,
	CHARACTER_MAXIMUM_LENGTH
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_NAME = 'TempAns'



E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/14/2008 :  05:42:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Run the query on your server.
Then post here the result of the query.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000