String truncate error

Yesterday my program was inserting and loading data in the database just fine, now when i try to do a myriad of things(make a paramatizsed search ad a favorites option) when ever i add data to the database i get a string trucate exception, basically its telling me the data im uploading is too large, problem is the feilds in the databse can take it.Its telling me the column im updating to has a string limit of 255 , but in reality the column its updating to has 2000 chracters available. Could some one please tell me how to fix this, it is quite urgent.

Answer this question

String truncate error

TrisTyPL
I fixed it , i went into the designer.vb file for the form in question and went to the defination of the function, where I was advised to change the line where the string size was defined to this
param.DbType = DbType.String
Hur?it
The pramater is created via sql statements in the query analyser in the dataset designer to be exact. Here is the query:

INSERT INTO [Directions] ([Directions]) VALUES (@textbox3)

the paramter is linked to the column directions that has 2000 chracters available, the paramater also has 2000 chracters available(checked it in the properties). I dont know to explicitly tell it to have 2000 chracters apart from linking to it in the statement.
JoeDeVirs
What database Which client do you use Who throws the error Some code, please
Nicolas S.
How are you creating the parameter - can you post the code

What happens if you explicitly specify that the parameter may be up to 2000 chars long

Thanks,

Zlatko
Sean Hunt
Sorry i dont know how to go about doing that for a mobile database, or for any other query for that matter.
Gerd Sauermann
Have you created an index on that includes that string column SQL 2005 has limit of 900 bytes for the keys. As far as I remember SQL 2000’s limit was 255. I cannot find SQL CE’s limit but it might be no more than 255.

Zlatko
Spook77

Hi Horator, I think I've discovered what the problem is.

As I'm sure you know, any change to the designer-generated code such as the one you've made here will be immediately overwritten the next time you modify and save the .XSD, even if only to click and slightly drag one of the tables in the UI view.

Was your DbType set to StringFixedLength before you changed it to String

It turns out that the code generator adds an entry param.DbType=DbType.StringFixedLength to the <TableName>TableAdapter.InitAdapter() method for the database column in question, but only if you've given that column a data type of nchar.

All this is OK, I suppose, until you try to store a string that's shorter than the length of the column. SqlCe doesn't like that. The generated code doesn't pad out the string to the proper length—that's up to us to do. If we want the data type to be nchar, that is.

When I discovered this after some testing I immediately changed all of my nchar data types to nvarchar, and everything worked fine.

HTH
Kelvin Penus
Sorry bout that, its SQLce 2.0 with a visual basic 2k5 front end, its basically a mobile app.Here is the code for the form that adds data to the database:

Public Class Addrecipe

Private Sub RecipeDataBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

Me.Validate()

Me.RecipeDataBindingSource.EndEdit()

Me.RecipeDataTableAdapter.Update(Me.Recipe_DataDataSet.RecipeData)

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'TODO: This line of code loads data into the 'Recipe_DataDataSet.Directions' table. You can move, or remove it, as needed.

Me.DirectionsTableAdapter.Fill(Me.Recipe_DataDataSet.Directions)

'TODO: This line of code loads data into the 'Recipe_DataDataSet.Ingredients' table. You can move, or remove it, as needed.

Me.IngredientsTableAdapter.Fill(Me.Recipe_DataDataSet.Ingredients)

'TODO: This line of code loads data into the 'Recipe_DataDataSet.RecipeData' table. You can move, or remove it, as needed.

Me.RecipeDataTableAdapter.Fill(Me.Recipe_DataDataSet.RecipeData)

Try

MsgBox("B4 try")

Me.RecipeDataTableAdapter.InsertQuery(TextBox1.Text, textbox5.text)

Me.IngredientsTableAdapter.InsertQuery(TextBox2.Text)

Me.DirectionsTableAdapter.InsertQuery(TextBox3.Text)

Me.RecipeDataBindingSource.EndEdit()

Me.RecipeDataTableAdapter.Update(Me.Recipe_DataDataSet.RecipeData)

Me.IngredientsTableAdapter.Update(Me.Recipe_DataDataSet.Ingredients)

Me.DirectionsTableAdapter.Update(Me.Recipe_DataDataSet.Directions)

MsgBox("Try completed")

Catch ex As Exception

MsgBox(ex.ToString, MsgBoxStyle.Critical)

End Try

End Sub

End Class

Here is the code for the insert quries(there are 3 of them, one for each table):

INSERT INTO RecipeData
(Name, Mealtype)
VALUES (@textbox1, @textbox5) 'recipedata table insert

INSERT INTO [Ingredients] ([Ingredient]) VALUES (@textbox2) 'ingredients table insert

INSERT INTO [Directions] ([Directions]) VALUES (@textbox3) 'directions table insert

The execption thats being thrown is a system.invalidoperationexception:@textbox3(the parameter for the directions data):String Truncation max=255,len=315 . It then goes on to list the data in question and the offending query which is the insert qury in the directions tableadapter. The sql feild in question can take 2000 characters and on a previous occasion has taken in data larger than this. I do not know why this has happened all of a sudden.This error occurs on both my desktop and pocket pc clint. When i enter the data in directly into the database via sql it works , but when use i use my frontend im geting this error.
rex letor
Would someone be able to tell me if this is a fault with Visual Studio(akin to the updating the database) I really need to know as I have not yet been able to solve this and have been conuslting with people and searching the net for over a week now with no success.
G. Cr.
Moving this thread to SQL Server Mobile...

Can someone please take a look at this issue and advise how to trace what’s going on

Thanks,

Zlatko
AlexanderGross
If by index you mean there is a primary key in the table and the column being updated has a type definition etc. then yes.Is there any way to get around or set this value Its not coming from the database as it has a capacity of 2000. The value seems to be set within ADO as i can put the actual data in quotes in a insert statement and it works , but when i put it in a paramater it says its too long.
Rdan
Please post a Profiler trace that shows the creation of the parameters in the context of Fill() and Update().
String truncate error

Answer this question