Large Text - What type of field

[Kinl]

I'm trying to store a large amount of text in a server here, and its about well over 2000+ characters, at any given time. What type of field can house this?

Thanx,

shorty

 

[Dilyias]

varchar should handle char's up to 8000
text should handle chars up to 2 billion.

Look at data types in BOL (Books Online) for more info regarding these and other types.

 

[Kinl]

I moved it up to a varchar(8000), but I'm trying to insert this data via ADO through a ASP Page, using a SPROC i created on the server.
'
I Get an errror returned: Here it is:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with 'NOVEMBER 30, 2001 Welcome to the latest edition of the FUNN 132456789 Update. If you've never received an update before, ple' is too long. Maximum length is 128.
/dev/addrelease.asp, line 6

My fields in the SPROC are varchar(8000), as well as the field in the table.

I'm still getting that error.
Any ideas?

thanx,

shorty

 

[RickCole]

Your Sproc isn't trying to create an object with name of

'NOVEMBER 30, 2001 Welcome to the latest edition of the FUNN 132456789 Update. If you've never received an update before, ple'

rather then inserting as a data row??

Object names can only be 128 characters long.

Rick.

 

[pmanivas]

you have to set the textsize property to say 5000
let me know if it helps

 

[Kinl]

I'm passing in the values as a variables in the sproc.

Heres how I construct my SQL:


SQL = "EXEC spPR_ReleaseInsert [" & TRIM(Request.Form("info&quot) & "], [" & TRIM(Request.Form("headline&quot) & "], 1"


Here is my sproc:

CREATE proc spPR_ReleaseInsert
@UPDATE varchar(8000),
@HEADLINE text,
@PRIORITY int
AS

INSERT INTO PR_Releases
(tstamp, headline, release, priority)
VALUES (GETDATE(), @HEADLINE, @UPDATE, @PRIORITY)


And I still get that error message.

Does anyone see anything wrong?

Thanx,

shorty

 

[pmanivas]

ur sql stmt

"EXEC spPR_ReleaseInsert [" & TRIM(Request.Form("info&quot) & "], [" & TRIM(Request.Form("headline&quot) & "], 1"

is wrong

how come u have info & headline within quotes like that?

 

[Kinl]

its ASP, its requesting the data from the preivous html page on a form.

shorty

 

[pmanivas]

but u cant put a double quote in there
Either u assign that value to some variable and then put the variable in the sql stmt or replace it with single quote

 

[Kinl]

Here is the code now:

Dim Info, Headline
Info = TRIM(Request.Form("info&quot)
Headline = TRIM(Request.Form("headline&quot)
SQL = "EXEC spPR_ReleaseInsert [" & Info & "], [" & Headline & "], 1"



Still doesnt work.

shorty

 

[Kinl]

Here is the code in the ASP page:

<% Call Connection()
Dim Info, Headline
Info = TRIM(Request.Form(&quot;info&quot)
Headline = TRIM(Request.Form(&quot;headline&quot)
SQL = &quot;EXEC spPR_ReleaseInsert [&quot; & Info & &quot;], [&quot; & Headline & &quot;], 1&quot;
objConn.Execute SQL
Call CloseConnections()
%>

 

[pmanivas]

still getting the same error?
If so before all these stmt add the following stmt

objConn.Execute &quot;SET TEXTSIZE 200000&quot;

then try
Let me know if it helps

 

[Kinl]

I'm not working with a Text Field. i'm working with a varchar, and if i switch it to text, it still gives me the same error.

Thanx

shorty

 

[moonshadow]

I can't believe no-one's spotted the answer to this one. You've probably got some sort of delimeter in the parameter string. Try coding your db access correctly like this:

set objCmd=server.CreateObject(&quot;ADODB.command&quot
objCmd.ActiveConnection=objConn
objCmd.CommandText= &quot;spPR_ReleaseInsert&quot;
objCmd.CommandType=adCmdStoredProc
objCmd.Parameters(&quot;@NameOfParameter1&quot=Info
objCmd.Parameters(&quot;@NameOfParameter2&quot=Headline
objCmd.Parameters(&quot;@NameOfParameter3&quot=1
objCmd.execute

 

[Kinl]

Would those be the name of the parameters in the SPROC? or the names of the fields in the table? Do they have to have the '@' in front of them?

Thanx!!

 

[moonshadow]

Sorry, my post was unclear. They're the name of the parameters in your stored procedure - and they need the @. Can anyone recommend a good book on data access for ASP code ? It seems there's a lot of misinformation and bad habits going around.

 

[Ibtisam]

Hi Kinl,

In ASP you can not have double quotation inside double quotation. So, what you will get if you try this code?

the Code:

&quot;EXEC spPR_ReleaseInsert [' & TRIM(Request.Form(&quot;info&quot) & '], [' & TRIM(Request.Form(&quot;headline&quot) & '], 1&quot;

If it is still not working make the single quote as double guote and make the double qoute which is for Request.Form as Single quote, like this Request.Form('info') and Request.Form('headline').

I hope it will help.

 

[moonshadow]

Coding this way is asking for trouble. What if you have people name's like O'Brien, or some other text with single or double quotes ? Your ASP page will fall over in a heap. I think my suggestion is the best way of avoiding this. Additionally - when your code reaches objCmd.CommandType=adCmdStoredProc, ADO actually makes a call to the server to ask about the parameters including datatypes, which helps if you try and do something silly like passing text to a numeric parameter.
I forgot to mention that the ASP page will need to INCLUDE the adovbs.inc file, which I believe will be to hand on your IIS box (unless it comes with Visual Interdev). It's just a defination file of the ADO constants, like adCmdStoredProc. Kinl - please let us know if you got it working.