missing character

[fennychieck]

dear all,

i've a problem here. everytime a user click on "approve" button,
they'll always get database error. and when i check the error log,
there are several fields that have missing characters, which results
in database error.
for example: the fields for
1. "refID" (supposely: 1952/102007 becomes 952/10 007 or becomes 9
2/102007)
2 "staffID" (3456 becomes 456)
3. "PONo" (IVT123 becomes VT123)
4. "date" (2007-10-13 becomes 2007-10-3)
and etc

the facts are:
1. not all user face this error (user frm HK, China and Mly
(sumtimes), maybe bcoz they use it often?)
2. when i test on my end (Sg), it doesnt give such error
3. it seems like sumtimes the character that gone missed is always
number "3" or "2"
4. and also recently, there's case that the first char is also
missing.
and etc.

Possibilities:
1. The user used the application often?
2. There's got anythng to do with the long lengthy code? recently, we
added a function to restore the sql statement of particular action
into a txt file, for example when user approve, all the data inside
that form will be stored in the txt file in one folder.

Help please. Thanks in advance!

 

[fennychieck]

Consider logging every SQL statement then determining which
ones fail -- then run those SQL statements to see what happens.- Hide quoted text -

- Show quoted text -

Hi All,
I have logged the SQL before it is executed. The values are posted
form the user's form and some (like the date), are actually selected
from a datepicker. When I logged the SQL before passing execution, it
is correct i.e. no characters are missing. However, when executed,
there is a database error, and when I get the offending SQL, it has
some characters missing. So before executing it's like, "Insert into
tableA (columnA,columnB) Values ('01/01/2007','Hello Everyone')" and
after execution, there is an exception, which logs the error into a
database table. The offending SQL is then saved as "Insert into tableA
(columnA,columnB) Values ('0 / /20 7','Hello Eve ne')

Could there be an error in the transmission of data from webserver to
SQL?

tq

 

[Omar]

[snip]

I have logged the SQL before it is executed. The values are posted
form the user's form and some (like the date), are actually selected
from a datepicker. When I logged the SQL before passing execution, it
is correct i.e. no characters are missing. However, when executed,
there is a database error, and when I get the offending SQL, it has
some characters missing. So before executing it's like, "Insert into
tableA (columnA,columnB) Values ('01/01/2007','Hello Everyone')" and
after execution, there is an exception, which logs the error into a
database table. The offending SQL is then saved as "Insert into tableA
(columnA,columnB) Values ('0 / /20 7','Hello Eve ne')

Could there be an error in the transmission of data from webserver to
SQL?

I'm confused by what you said.

You log the SQL statement before it's executed and it's correct -- okay.
Then you execute it and log it again -- how do you log it again?
At what point does the SQL statement change?

What she means is something like this.

'Log SQL starement into error log.txt

ExecuteQuery(strSql)
'catch the error, when error is caught, save the offending strSQL into
a database table. When looking at the sql which was saved in the
database table, it has some 'characters missing, whereas that 'which
was logged in the error log.txt is correct and can be executed through
SQL Query Analyzer.

Hope that clears it.

 

[Bob Barrows [MVP]]

Hi All,
I have logged the SQL before it is executed. The values are posted
form the user's form and some (like the date), are actually selected
from a datepicker. When I logged the SQL before passing execution, it
is correct i.e. no characters are missing. However, when executed,
there is a database error, and when I get the offending SQL, it has
some characters missing. So before executing it's like, "Insert into
tableA (columnA,columnB) Values ('01/01/2007','Hello Everyone')" and
after execution, there is an exception, which logs the error into a
database table. The offending SQL is then saved as "Insert into tableA
(columnA,columnB) Values ('0 / /20 7','Hello Eve ne')

Could there be an error in the transmission of data from webserver to
SQL?

The best way to confirm what is actually being executed on the server
(assuming you are using MS SQL Server) is to use SQL Profiler to run a
trace while attempting to run your page.

I suspect the root cause of your problem is the use of dynamic sql
rather than parameters. See here for an example of using a Command
object to execute a parameterized sql string:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

My preference is to use stored procedures. See here for my preferred
method to execute stored procedures:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en