Limit on number of characters sent from VB to SQL??

[Arg81]

Hi Guys,

My app has now gone live, and I've bumped into my first headache

One of my fields is used to write a lot of information in, and even though my SQL table is set to hold 2000 varchar for that field, when the row is submitted in my app, only the first 994 characters appear in my SQL table. However I can go into SQL and add the remaining characters, and then on my search form in the app, load the row and all characters appear OK in the field.

The problem is only when Inserting a new row.

Is this an issue with character limitations or am I missing something plainly obvious???

I could do with some advice rather quickly

Kind Regards,
Luke

 

[kulrom]

Hi,
Is it SQL Server or something else (you stated it only as SQL that is a bit confusing as there is a big difference between say mySQL's varchar and MSSQL's varchar ...well mySQL 5 remove some limitaions of varchar but again it is not the same as varchar of MSSQL).

If it's MSSQL 2000 or 2005:
Actually, all this above doesn't make much sense as you can obviously add 2000 varchar in that field. I know about some limitations of MSSQL but all that i know is not valid in your case (i.e. notice that the row of certain table has limit of about 8000 byte as well so, This means if you have a few large varchar or nvarchar fields it's actually quite easy to outrun the size of a row) as you are able to add the remaining charasters. But again i want to point out that you can force it to accept data larger than 8KB but as soon as you try to populate a row with more than 8K of data, you will run into problems.
Ok, if it's not the case with you then give it a try to change datatype of certain field to text instead varchar.

Regards

 

[Arg81]

Hi Kulrom,

Sorry, it is MS SQL Server 2000, and I am using VS2003.

If I set the field to text, it has an non-changable field length of 16, hence why I tried to use char / varchar.

The interesting thing is I have set the field length to 8000. Tried typing something in, and after a while it will stop me typing in.
I have copied and pasted this field into MS Word, and upon doing a word count it is 1024 (inc spaces).

..so the problem is with MS SQL, although I can't see why it's stopping me entering characters when it gets >1000 even though the field length is set to 8000. I assume 8000 IS the number of characters that can be entered?? Or is it something else, so 1024 characters = 8000 something else... :-D

Regards,
Luke

 

[TechGnome]

SQL Server has a limitation that a single row of data in a table CANNOT EXCEED 8k. So if you have a table that has, let's say and ID of int (4bytes).. a datetime (8bytes)... and a varchar(8000)... the most you will be able to enter into the varchar is 8000-3-8 or 7988characters.

Now, that said, what you haven't told us is HOW you are entering the data... specifically the code that is doing the insert, what does it look like? Are you using embeded SQL (SQL is build & executed from VB), a parameterized query or a stored procedure?

-tg

 

[Arg81]

Tech,

I thought that was the case. However I've just put that to the test and it's still well out...

My table has the following fields and field sizes;

MeID 4 (int)
CustomerID 4 (int)
CustomerContact 30 (varchar)
EndCustomer 30 (varchar)
DateRequested 4 (SmallDateTime)
DateRequired 4 (SmallDateTime)
MeContact 4 (int)
MeContact2 4 (int)
MeAccount 4 (int)
MeAccount2 4 (int)
Ojective 100 (varchar)
Information 8000 (varchar)
CreatedBy 4 (int)
CreatedDate 4 (int)
StatusID 4 (int)
DateCompleted 4 (smallDateTime)

so then 8000 - 4 - 4 -30 - 30 - 4 - 4 - 4 - 4 - 4 - 4 - 100 - 1000 - 4 - 4 - 4 - 4 = 6792.

But I can only enter around 1000 characters.

Again, am I missing something blatently obvious??

In terms of the Insert Command, the problem is at SQL table level if I use SQL Server Enterprise Manager, so no VB code is used at this point.
However, I use the UpdateDataSet() command generated from the DataFormWizard.

Also, the form loads the dataSet(s) and adapters, and uses BindingContext.AddNew() to add the new row.

Luke

 

[Arg81]

This is still bugging me, any ideas?

Regards,
Luke

 

[Arg81]

take it no-one else has had this problem then?? Anyone know of SQL forums, I do believe this is an SQL problem...

Luke

 

[Arg81]

solved...after a lot of searching on the net.

Apparently there is an issue with Enterprise Administrator on the amount on data it can enter into a field, even if the size of the field is set higher.

I found a lot of people were getting the same - data truncated down to 1023 characters when copying and pasting from i.e. MS Word into the field in Enterprise Admin of SQL Server 2000.

The way to do it is to use SQL Query Analyser and Update the row from there.
I've tested this and it's true - In Enterprise Manager, open database, table and then "Return All Rows" and trying to update the field in question, if it is larger than 1023 characters, you cannot delete or add to the field, only view.

I also noticed my original insert failed as the field size was only 1000, I have now increased this and works perfectly now

...all in all, yes it was an SQL problem!!!

Luke