Handling "box" characters in a string 1

[tmar]

I'm trying to put together a program that allows a user to copy data from Excel and then paste it into a datagrid in VB.net. The problem I'm running into is that there is a field where users have typed something in which causes a box in the text field (I'm assuming it's a 'return'). This field is giving me an error on converting a dbnull to a string.

I assumed the characted was either a vbcrlf or vbcr and did a replace function for that (ie replace(notes,vbcr,"") ) but that didn't help.

When I paste the values from Excel into Notepad it looks like the text below:

PI5005070 4351 INS 3 "projector install[]cable pull[]terminations" 4/14/2014

Except that the "[]" are actual boxes.

 

[SkipVought]

Try replacing vbLf

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[tmar]

Just tried it and get the same error.

Conversions from type 'DBNull' to type 'String' is not valid.

 

[jges]

Create a small project that takes one of these strings as input and loop through the string character by character to see what the problem is. Use the Asc and/or the AscW functions to identify the characters.

http://msdn.microsoft.com/en-US/library/zew1e4wc(v=vs.80).aspx

Once you know what the problem character is, you can add checks in your code to find it and strip it out.

 

[softhemc]

In situations like that I simply open the Notepad file in an Hex Editor (if you don't already have one there are lots of free ones available.)

Once you've identified the offending character you can use search and replace (either within VB or the Hex editor) to replace it with something more manageable.

This solution has never failed for me whenever I've encountered a situation similar to yours - it also has the benefit of removing all guesswork, the offending character(s) is/are clearly identified.