Common Problem on Data Extraction from Database

[whloo]

Table 1
Name Role
---- ----
John Administrator
Owen User
Baros User

Lets say we try to check if the John is an Administrator, we will extract data Role field from the table.
As expected, we would use

if rs.Fields("Role&quot.Value = "Administrator" then
Perform our action at here....

Then suddenly we will find that how come John is not fall in Administrator role although you can see John is clearly an Administrator from database.

There are 2 ways that i always use to check and solve this.

1st Way
-------
i will print out the field first. Example:

Response.Write("Name: --"& rs.Fields("Role&quot.Value &"--&quot

This is the fastest way to debug i guess as it will display the value.
The reason why i put additional "--" is because i would like to check if there are any leading space to it.

Example output will be:

Example 1: (Which we always expected, but it doesn't always return us this exact value)
Name: --Administrator--

Example 2: (This is what many of us never expect it.)
Name: --Administrator --

Why is this so in Example 2, althought we enter "Administrator" correctly into the Role field?
Frankly speaking, i am not very sure also.
But i am pretty sure that i always encounter this problem.
All i can say is, whenever we retrieve a String from database, it will return you with its null character at the end of it.
Hence, what is the solution?
The solution will be in 2nd way that i am going to show next.

2nd Way
-------

Apply trim(Str) function on every result that we get from database.
With this way, we will be able to solve the problem.

Example : trim(rs.Fields("Role&quot.Value)

Hope this will help any of you.
Happy learning from each other.

Failure is the mother of every Success

 

[adroit]

What about using RTRIM(LTRIM(Role)) in your SELECT Statement ?
I think it is a better technique for extracting such type of fields from a table.