| Author |
Topic |
|
juicemousezero
Starting Member
10 Posts |
 Posted - 2005-08-02 : 09:20:20
|
I was wondering what the best kind of datatype would be to use if one wanted to store some large bodies of text in the database (HTML tags and all, mind) and then retrieve them selectively for display on a .aspx page.See, I do that same thing on my website where I have a MySQL database and I use PHP to talk to it, and I have that particular column set to the 'text' datatype and it works fine. For the sake of practice, I want to make a vb.net/sql server version of my site, but on my SQL Server version of that DB, the 'text' datatype doesn't work and I was just wondering if I should be using another datatype or if I’m doing anything wrong.  Thanks in advance.  |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 09:23:44
|
| Welcome to SQLTeam juicemousezero!On SQL Server varchar has a limit of 8000 characters (4000 if you use nvarchar in order to be able to store UNICODE characters), so that kinda suggests that you need TEXT datatype."on my SQL Server version of that DB, the 'text' datatype doesn't work"That aint right! What sort of errors / "funny happening" do you get?Kristen |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 09:28:32
|
| Brett, we do this - allows us to do easy searches of Content, and create templates that include other stuff, and so on.If JuiceMouse0 is like us then 99.999% of the content will be less than 8K (so splitting over multiple rows might be an answer).In our case the agility of having the code in the DB outweights the ease of having it in a file.Now ... if it was an image ... we'd be on the same hymn sheetKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 10:29:04
|
| SELECT DATA_LENGTH('RUN') ? Kristen |
|
|
|
juicemousezero
Starting Member
10 Posts |
Posted - 2005-08-03 : 10:31:05
|
| Not to worry, none of this stuff is over 8k and should even be less than 8000 characters. I tried varchar as well with the same problem, thus my confusion.In fact you can see the working example at my site in its PHP/MySQL glory... http://www.juicemousecypher.com . It's not a complex system, but it's freakin handy, you know?This is the error I'm getting: "The data you entered is not consistant with the data type or length of the column, or over grid buffer limit." So yeah I just don't know what else to try cause it should be so simple anyway. In the event that I copied something that I wrote in Microsoft Word, would that stuff I copied give me problems? I ask because I know Word does weird stuff to text, like commas and what not. If that can be a problem, that might be it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-03 : 12:07:23
|
| Single / Double Quotes is the most likely thing - Word turns those around to look "pretty"Is that the exact error message verbatim? (Including the spelling of "consistant"<sic>?)Kristen |
|
|
|
juicemousezero
Starting Member
10 Posts |
Posted - 2005-08-03 : 12:17:16
|
That's error message verbatim, albeit the "consistant" bit, which is just a manifestation of my laziness to use spell check.  "Consistent" is what I meant. Sorry about that. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-03 : 16:01:41
|
| Couldn't find it in Google, any ideas where its coming from?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-03 : 16:15:17
|
quote:
Originally posted by X002548
I guess we need to define what large bodies of text are.Are we talking war and peace, or France's terrorism deterance policy?
That was great!Tara |
|
|
|
juicemousezero
Starting Member
10 Posts |
Posted - 2005-08-03 : 17:10:13
|
Well I tried replacing the single and double quotes with plain text and it still didn't work.  This is really starting to get to me. I guess I'll have to ask someone at school next time I go in, but that may be in another week or so. Grr. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-04 : 00:45:11
|
| Post the faulty code here?Kristen |
|
|
|
juicemousezero
Starting Member
10 Posts |
Posted - 2005-08-04 : 10:18:04
|
| I think I've done enough troubleshooting to track the problem down, which now leaves me scared and confused. It appears that the amount of text I'm trying to insert is simply too much, which I find totally puzzling since I've got the MySQL DB doing it, and you'd think that high end software like SQL Server could certainly handle the job, and yet it doesn't seem to be able to... it just doesn't add up to me, you know?Well the code itself is just a few insert statements, such as...INSERT INTO AOW (album_name, content, band_name)VALUES ("Permission to Land", "I started the first BOW thing with the Darkness so I'll do that with this, too. This album is absolutely awesome. I listen to it a lot; always while driving and I'm always dancing around in my seat and singing along (poorly) with Justin Hawkins as he shreds and falsettos his way through one amazingly listenable song after another. I mean this puppy is awesome from beginning to end, people. With this release they've taken a lot of great elements from classic bands and rolled them into one nice package (the theatrics of Queen, for example). I really can't wait to see these dudes live. Till then however, I'll be continuing my jammage with this CD. I can only imagine what people think when they see me in my car jumping around and looking stupid. But it just doesn't matter because this CD just makes you feel good. Very much recommended. You had better give it a chance before you go around hating them because of the singer.", "The Darkness")That's the shortest of all the entries for the AOW table. If I can't do that one, I can't do the others. And in another table I'll be storing pretty big chunks of HTML (cause you see, I only want two actual pages on the site, one to redirect to the viewing page, and the viewing page to display the website's content which will be taken from the DB).But is that even a feasible project? |
|
|
|
juicemousezero
Starting Member
10 Posts |
Posted - 2005-08-04 : 10:21:38
|
| By the way, thanks for the help you guys. This is really a pleasant forum. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-04 : 10:32:18
|
| What you are doing is "normal" Pushing large text into SQL is not a problem, but:1) You should be sure that your Application is catching, and displaying to you, any errors that occur - otherwise you are running blind2) The ADO Drivers for SQL used to have, in old versions, a Real Problem with Big Text.3) You've got some embedded single quotes in your "content" above. Depending on how you are passing that data through ADO to SQL that may be mucking things up.Kristen |
|
|
|
juicemousezero
Starting Member
10 Posts |
Posted - 2005-08-04 : 10:46:46
|
| Well, I'm using an academic version of Visual Studio.NET 2002... old it is indeed but Microsoft likes their money, and I dont have enough for anything newer. Do you know if that's old enough to give me problems? I guess that'd make sense if so. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-04 : 10:49:05
|
| You can download the latest version of ODBC/ADO/etc. which might cure the problem - if that's what the problem is! Might be an idea to check what version you have, and then see what MS say has been fixed in the newer version's Feature ListsKristen |
|
|
|
|
A quick noob datatype question