string truncation
-
jschell wrote:
a much better idea is to validate the data first
In theory sure. However it seems to imply the GUI knows all the database field widths, or alternatively the DB knows how to identify the failing field and pass that on to the GUI. Hence my third question. :doh:
Luc Pattyn [My Articles] Nil Volentibus Arduum
Luc Pattyn wrote:
the GUI knows all the database field widths
Yes, well you can tell it, e.g. TextBox.MaxLength, but I've never bothered to do that. To do it properly you'd also need to query the database to get the length at run time somehow.
-
Go to Query Analyser or SQL Server management studio... do your inserts in T-SQL and then query results. It should be easy to see which field is being truncated... also, SQL server may throw an error that tells you which field is being truncated. In general, when designing the table, you should have an idea about the field size. For example if the field is FirstName, I would give it VARCHAR(25).
Maybe I didn't explain well enough, but it isn't a one-off situation that I am trying to resolve. My app holds a number of tables and dialogs; each dialog holds TextBoxes (and other Controls), and the strings entered in the TextBoxes is going to be inserted or updated in the tables. Think of people's first and last name, addresses, phone numbers, a comment line, some preferences, and many more. Right now there is no protection against overflow, as TextBoxes allow an unlimited amount of text to be entered (and the GUI is unaware of the table field widths anyway). So it is at run-time that the users might enter too much text in any of a large number of fields; the fields are quite sufficient for the intended use, but nothing is preventing abuse. So I want: 1. to make sure my app behaves well no matter what the input is; 2. provide detailed feedback where necessary (such as "Phone number cannot exceed 50 characters"). One way to remedy the situation would be to populate, at start-up, some data structures describing all relevant field widths, and pass this to the GUI stuff, so it can check all the lengths and warn the user, before attempting an insert/update. However that seems more effort than elegance, hence me asking about some best practices and/or easy solutions. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Luc Pattyn wrote:
the GUI knows all the database field widths
Yes, well you can tell it, e.g. TextBox.MaxLength, but I've never bothered to do that. To do it properly you'd also need to query the database to get the length at run time somehow.
Then what do you do? Do you just have a try-catch and tell the user "Something went wrong, maybe one of your strings is too long"? That is way to vague to my liking. OTOH passing (part of) the TableSchema information to the GUI seems, well, quite cumbersome. :doh:
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Maybe I didn't explain well enough, but it isn't a one-off situation that I am trying to resolve. My app holds a number of tables and dialogs; each dialog holds TextBoxes (and other Controls), and the strings entered in the TextBoxes is going to be inserted or updated in the tables. Think of people's first and last name, addresses, phone numbers, a comment line, some preferences, and many more. Right now there is no protection against overflow, as TextBoxes allow an unlimited amount of text to be entered (and the GUI is unaware of the table field widths anyway). So it is at run-time that the users might enter too much text in any of a large number of fields; the fields are quite sufficient for the intended use, but nothing is preventing abuse. So I want: 1. to make sure my app behaves well no matter what the input is; 2. provide detailed feedback where necessary (such as "Phone number cannot exceed 50 characters"). One way to remedy the situation would be to populate, at start-up, some data structures describing all relevant field widths, and pass this to the GUI stuff, so it can check all the lengths and warn the user, before attempting an insert/update. However that seems more effort than elegance, hence me asking about some best practices and/or easy solutions. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
If you are in control of both the front end and the database, I don't see the issue with allowing the front end (be it through the actual front, or a "business layer" or such) to control the validation of your data.
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
String or binary data would be truncated is the nice SqlException one gets from SQL Server when attempting to insert/update a record with a string value that is longer than the field is wide. That raises 3 questions: 1. what would be the recommended approach in general? 2. what would be the recommended approach in a medium-size application (lots of insert/update statements, no SP), when the user doesn't care much and would be happy when the strings where truncated without notification? 3. is there a simple way to identify which field is overflowing? TIA
Luc Pattyn [My Articles] Nil Volentibus Arduum
3. Not that I know of. 2. Size doesn't matter. :-D 1. Estimate and double. :laugh: If you have a number of large text fields, you could combine them all into one table with an ID (of some type) and a value of NVARCHAR(MAX), then only store the ID in the table that uses the value.
-
If you are in control of both the front end and the database, I don't see the issue with allowing the front end (be it through the actual front, or a "business layer" or such) to control the validation of your data.
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
_Damian S_ wrote:
If you are in control of both the front end and the database
I am.
_Damian S_ wrote:
I don't see the issue
I see it doable, in more than one way: 1. I could pass the DB field characteristics to the GUI and have the input checked against them by the GUI. 2. I could pass the Control to the back-end and let that decide whether the current content could be stored, and if not, signal that somehow to the user (e.g. by changing the BackColor). Both seem pretty far from the ideas behind MVVM (which I do not master), and rather cumbersome. So I'd like to get some real advice, if possible an example. Note: a number of the text fields have sizes that haven't been fixed yet. Just setting a TextBox.MaxLength to a constant won't cut it. That would be good enough for phone numbers and ZIP codes and the like, but not for unstructured info, such as comment fields (which may change size upon the client's request). :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Then what do you do? Do you just have a try-catch and tell the user "Something went wrong, maybe one of your strings is too long"? That is way to vague to my liking. OTOH passing (part of) the TableSchema information to the GUI seems, well, quite cumbersome. :doh:
Luc Pattyn [My Articles] Nil Volentibus Arduum
Luc Pattyn wrote:
try-catch and tell the user "Something went wrong
You must have seen some of my apps. :) That's what I've done so far, but I'm not writing apps to sell to the great unwashed public (not meaning you). You could probably query the database at compile time rather than run time.
-
_Damian S_ wrote:
If you are in control of both the front end and the database
I am.
_Damian S_ wrote:
I don't see the issue
I see it doable, in more than one way: 1. I could pass the DB field characteristics to the GUI and have the input checked against them by the GUI. 2. I could pass the Control to the back-end and let that decide whether the current content could be stored, and if not, signal that somehow to the user (e.g. by changing the BackColor). Both seem pretty far from the ideas behind MVVM (which I do not master), and rather cumbersome. So I'd like to get some real advice, if possible an example. Note: a number of the text fields have sizes that haven't been fixed yet. Just setting a TextBox.MaxLength to a constant won't cut it. That would be good enough for phone numbers and ZIP codes and the like, but not for unstructured info, such as comment fields (which may change size upon the client's request). :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Client side validation (in a web based system) saves you a postback for each field you are validating. Personally I validate data in the gui through various means (whether that's through field validation as part of the control or functions or whatever), because that way you have granular control over how your program acts. Of course, you need to update and put out a new version if your data structures change, but you can't have everything!!
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
Luc Pattyn wrote:
try-catch and tell the user "Something went wrong
You must have seen some of my apps. :) That's what I've done so far, but I'm not writing apps to sell to the great unwashed public (not meaning you). You could probably query the database at compile time rather than run time.
So far this is the best approach I have come up with (untested!): 1. I'll have my app interrogate the DB structures at startup to basically set up a
Dictionary<string tableName, Dictionary<string fieldName, int fieldLength>> stringMaxLenghts;
That isn't too much trouble, I already have a number of run-time checks for the presence of some tables and some fields (the database is going to exist in many generations). 2. For each relevant TextBox I'll have each of my dialog Load handlers call a static method:
StaticClass.SetTextBoxMaxLength(TextBox textbox, string tableName, string fieldName) {...}
which sets the TextBox.MaxLength property in accordance to the stringMaxLenghts information. That will eventually cause some beeping at the very moment the user is typing too much text. Not that I like beeping a lot. 3.In the data layer, I'll have my DatabaseCommand.AddStringParameter(...) methods check the length of the string against stringMaxLenghts once more (and truncate if necessary); this to be safe in case some data slips through, e.g. something that doesn't come straight from a TextBox. That seems a reasonable cost (only one Dict-of-Dicts being passed, plus a number of method calls in Load handlers) to accomplish most of what I want. Comments are welcome. Better ideas too. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
So far this is the best approach I have come up with (untested!): 1. I'll have my app interrogate the DB structures at startup to basically set up a
Dictionary<string tableName, Dictionary<string fieldName, int fieldLength>> stringMaxLenghts;
That isn't too much trouble, I already have a number of run-time checks for the presence of some tables and some fields (the database is going to exist in many generations). 2. For each relevant TextBox I'll have each of my dialog Load handlers call a static method:
StaticClass.SetTextBoxMaxLength(TextBox textbox, string tableName, string fieldName) {...}
which sets the TextBox.MaxLength property in accordance to the stringMaxLenghts information. That will eventually cause some beeping at the very moment the user is typing too much text. Not that I like beeping a lot. 3.In the data layer, I'll have my DatabaseCommand.AddStringParameter(...) methods check the length of the string against stringMaxLenghts once more (and truncate if necessary); this to be safe in case some data slips through, e.g. something that doesn't come straight from a TextBox. That seems a reasonable cost (only one Dict-of-Dicts being passed, plus a number of method calls in Load handlers) to accomplish most of what I want. Comments are welcome. Better ideas too. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Luc Pattyn wrote:
cause some beeping
Ew. Setting the MaxLength should be enough. And you could have a label or something that counts down the number of characters to go before the field is full.
-
Client side validation (in a web based system) saves you a postback for each field you are validating. Personally I validate data in the gui through various means (whether that's through field validation as part of the control or functions or whatever), because that way you have granular control over how your program acts. Of course, you need to update and put out a new version if your data structures change, but you can't have everything!!
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
It is a WinForms app, but I tend to prefer "GUI side" validation too. Here[^] is the best I could come up with so far. Please feel free to comment. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Luc Pattyn wrote:
cause some beeping
Ew. Setting the MaxLength should be enough. And you could have a label or something that counts down the number of characters to go before the field is full.
it is WinForms that does the beeping, I don't add to it. I know I could add a "chars remaining" indicator (and I do appreciate those on the web sites that have them, e.g. the CP forum's signature editor), but then that would really alter and enlarge all my dialogs, I don't want to do that. However I could use a TextBox derivative in a few occasions, that would fit nicely with the current scheme. So phone numbers and ZIP codes would beep on abuse only, whereas big TextBoxes (e.g. a comment field) might be more civilized. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
So far this is the best approach I have come up with (untested!): 1. I'll have my app interrogate the DB structures at startup to basically set up a
Dictionary<string tableName, Dictionary<string fieldName, int fieldLength>> stringMaxLenghts;
That isn't too much trouble, I already have a number of run-time checks for the presence of some tables and some fields (the database is going to exist in many generations). 2. For each relevant TextBox I'll have each of my dialog Load handlers call a static method:
StaticClass.SetTextBoxMaxLength(TextBox textbox, string tableName, string fieldName) {...}
which sets the TextBox.MaxLength property in accordance to the stringMaxLenghts information. That will eventually cause some beeping at the very moment the user is typing too much text. Not that I like beeping a lot. 3.In the data layer, I'll have my DatabaseCommand.AddStringParameter(...) methods check the length of the string against stringMaxLenghts once more (and truncate if necessary); this to be safe in case some data slips through, e.g. something that doesn't come straight from a TextBox. That seems a reasonable cost (only one Dict-of-Dicts being passed, plus a number of method calls in Load handlers) to accomplish most of what I want. Comments are welcome. Better ideas too. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Good strategy. Item 3 is probably not required, but a good catch-all nonetheless...
Luc Pattyn wrote:
stringMaxLenghts
Has a typo in it. stringMaxLengths
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
it is WinForms that does the beeping, I don't add to it. I know I could add a "chars remaining" indicator (and I do appreciate those on the web sites that have them, e.g. the CP forum's signature editor), but then that would really alter and enlarge all my dialogs, I don't want to do that. However I could use a TextBox derivative in a few occasions, that would fit nicely with the current scheme. So phone numbers and ZIP codes would beep on abuse only, whereas big TextBoxes (e.g. a comment field) might be more civilized. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Luc Pattyn wrote:
might be more civilized.
Ah, yes, maybe on KeyDown or something.
-
Good strategy. Item 3 is probably not required, but a good catch-all nonetheless...
Luc Pattyn wrote:
stringMaxLenghts
Has a typo in it. stringMaxLengths
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
Thanks for the heads up. I'll give it a whirl tomorrow. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
String or binary data would be truncated is the nice SqlException one gets from SQL Server when attempting to insert/update a record with a string value that is longer than the field is wide. That raises 3 questions: 1. what would be the recommended approach in general? 2. what would be the recommended approach in a medium-size application (lots of insert/update statements, no SP), when the user doesn't care much and would be happy when the strings where truncated without notification? 3. is there a simple way to identify which field is overflowing? TIA
Luc Pattyn [My Articles] Nil Volentibus Arduum
Wouldn't it be a better idea to have a model layer that sits in between the data access layer and the GUI. The properties in the model layer would know the limits of their respective database fields, and pass this on via an interface such as IDataErrorInfo or something similar. This way you can have GUI validation.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
-
Wouldn't it be a better idea to have a model layer that sits in between the data access layer and the GUI. The properties in the model layer would know the limits of their respective database fields, and pass this on via an interface such as IDataErrorInfo or something similar. This way you can have GUI validation.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
Thanks Wayne. Yep, that is the way it seems to be evolving. I already discovered I could make most of my business objects derive from a base class (say DatabaseRecord), which could help taking care of general field information handling. I'd still appreciate an example though, so if you're aware of some article I should read, please let me know. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Thanks Wayne. Yep, that is the way it seems to be evolving. I already discovered I could make most of my business objects derive from a base class (say DatabaseRecord), which could help taking care of general field information handling. I'd still appreciate an example though, so if you're aware of some article I should read, please let me know. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
I have a basic article on the IDataErrorInfo that follows the MVVM pattern Validating User Input - WPF MVVM[^] that should get you started. Good Luck!
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
-
I have a basic article on the IDataErrorInfo that follows the MVVM pattern Validating User Input - WPF MVVM[^] that should get you started. Good Luck!
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
Ah. That looks interesting. Thanks very much, I'll have to read that in the morning, and apply it right away. :-D
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
jschell wrote:
a much better idea is to validate the data first
In theory sure. However it seems to imply the GUI knows all the database field widths, or alternatively the DB knows how to identify the failing field and pass that on to the GUI. Hence my third question. :doh:
Luc Pattyn [My Articles] Nil Volentibus Arduum