April 28, 2003 at 10:18 am
I have numeric field that is set to nvarchar which is used as an id number field. It works fine directly in SQL but when trying to do a select from vbscript over a web page it fails if the number of characters exceed 6. I am running Win 2000 Small Buisness Server SQL and IIS are the standard editions that come with SBS.
Is any one aware of of limit or memory issue in IIS 5 that would cause such a problem?
Thanks for any help.
April 28, 2003 at 11:23 am
Could you post some sample code that is causing the errors, as well as the DDL of the table in question? It will help to determine the problem.
April 28, 2003 at 11:27 am
If Your Field is Numeric only and not over 50 characters long I suguest you use char(N) where N is the length of your ID number. It works great for me.
April 28, 2003 at 12:01 pm
jpipes,
Here is the code:
lexicorSQL = "Select * from patient where patientid = '" & request.querystring("pID") & "'"
doQuery(lexicorSQL)
How do I get you a DDL? I was sort of thrown into dealing with this problem and am not a DBA. As long as the patientid is 6 or less characters everything works great. And if it is over 6 characters the querry works great in SQL but when trying to run the above querry from a web page I get a runtime error at the line number that corresponds with the querry above.
FJMelendez,
When using char(n) leading zeros are stripped off, but I need thoose for order purposes.
April 28, 2003 at 12:09 pm
The stripping of the leading zeros actually has nothing to do with whether the field is char or nvarchar. It must have something to do with some implicit or explicit conversion going on in the page or in the db.
To get the DDL of the patient table, copy and paste the output of the following into this forum:
sp_help patient
Thanks,
Jay
April 28, 2003 at 12:17 pm
Sorry to be such a problem here. I opened the table in SQL Enterprise Manager, clicked the SQL button at the top, typed in the code you gave me, and the results where the table name, the DBO, Type, and Create Date, some how I do not think that is what you meant. What am I missing?
This is the exact error that is given on the web page when running the querry on an entery with more then 6 digits:
error '80020009'
Exception occurred.
jwable
April 28, 2003 at 12:36 pm
Go into Query Analyzer. From Enterprise Manager, click on Tools --> SQL Server Query Analyzer. Then type in:
sp_help patient
Post the results...
April 28, 2003 at 12:38 pm
I agree the stripping of zeros has to occur in the ASP code. It sounds to me that the problem may be in the underline code used in the page. Also something I verify with all my developers is the version of the OBDC that you are using for development and the ADO include file used. They must be the same version in development and in production. So if you are using an include from MDAC 2.6 SP1, your server must have version 2.6 SP1 Installed.
April 28, 2003 at 1:17 pm
Here's the DDL:
patidpri,int,no,4,10 ,0 ,no,(n/a),(n/a),NULL
ClientID,int,no,4,10 ,0 ,yes,(n/a),(n/a),NULL
CProviderID,int,no,4,10 ,0 ,yes,(n/a),(n/a),NULL
PhysicianID,int,no,4,10 ,0 ,yes,(n/a),(n/a),NULL
PatientID,nvarchar,no,100, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
Birthdate,datetime,no,8, , ,yes,(n/a),(n/a),NULL
PriDiag,ntext,no,16, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
SecDiag1,nvarchar,no,100, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
SecDiag2,nvarchar,no,100, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
Medications,ntext,no,16, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
Allergies,ntext,no,16, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
NANDA,ntext,no,16, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
Notes,ntext,no,16, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
Dev_100_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_200_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_300_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_400_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_500_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_600_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_700_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_700_Target,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_700_Concern,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_800_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Dev_900_Start,datetime,no,8, , ,yes,(n/a),(n/a),NULL
Cp_team,nvarchar,no,100, , ,yes,(n/a),(n/a),SQL_Latin1_General_CP1_CI_AS
upsize_ts,timestamp,no,8, , ,yes,(n/a),(n/a),NULL
April 28, 2003 at 2:12 pm
Could you provide a sample patient id that does work, a sample one that doesn't and the exact code that executes doQuery() prcoedure. There has to be something in the ASP code that is stripping/converting the value passed in the QueryString.
April 28, 2003 at 2:30 pm
Any digit that contains 6 or less numbers works, examples:
000000
012345
999999
When adding 7 or more digits irregardless of the number it generates the exception error. But only when running the querry in ASP, the query executes properly directly inside of SQL.
Here is the code of doquery procedure:
sub doQuery(lexicorSQL)
set rstemp=conntemp.execute(lexicorSQL)
end sub
As for the leading zero issue, I opened a backup copy of the database went into the table designer change the data type for patientid to char(n) and SQL stripped out all the leading zeros for all data stored for patientid so 001155 became 1155, this was directly in SQL not ASP.
April 28, 2003 at 2:48 pm
quote:
...all the leading zeros for all data stored for patientid so 001155 became 1155, this was directly in SQL not ASP....
Unfortunately, I have never seen this happen, and am having trouble understanding how this happens, unless the "0" is not really a zero, but some Unicode character outside the ASCII character set???
To analyze further, have you double checked that the values coming in on the querystring are indeed what you expect them to be. Just do a simple
Response.Write Request.QueryString("pid")
Response.End
right before the call to doQuery()
Also, just for posterity's sake, post the results of the following code as well (run it for both good and bad values):
lexicorSQL = "Select * from patient where patientid = '" & request.querystring("pID") & "'"
Response.Write lexicorSQL
Response.End
Thanks,
Jay
April 28, 2003 at 3:00 pm
Ahhh now we are getting somewhere. The querystring is dropping all numbers after the 6th digit, therefore I am getting the error becuase there is no data that matches. So now I just need to figure out the query string. Thank you very much for your input.
April 28, 2003 at 3:04 pm
No problem, let us know if we can help any further...
Jay
April 28, 2003 at 3:14 pm
I agree, unless you are not using ASCII characters there should not be any reason for the characters to be dropped.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply