| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2005-04-13 : 11:48:24
|
Could some of you help me point out what is my problem?  Users typed a long text and the data did not get saved into the table.The bug sounds simple, but when I tried to reproduce it in the develop environment, it disappeared! The data is saved into dev db.Here are some details:UI: Access textbox, text length 450 characters without space/line break.Insert StoredProc: input param varchar(200)table field: data type text length 16 ([Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL)Strange things are 1) I have the exact same setting for dev, but it did not have the problem; 2) an Update SP later could add more into the table, and the field will take them; 3) and when I increase insert SP's to varchar(400), but did not seem work either.Thanks! |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-04-13 : 12:19:27
|
| Now by trial and error, I increased the input sp param varchar to 800, it could save more data now.But I still don't understand why varchar(200) in one box = varchar(800) in other box. The only different of the sp is that the dev one has SET NOCOUNT ON.I am going to swap them during off-peak hours and see what will happen. |
 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-13 : 13:10:23
|
| Think a second "Access textbox, text length 450""input param varchar(200)"JimUsers <> Logic |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-04-13 : 15:41:06
|
| Just to clarify. Part of the characters did get saved into the table, but the tail end of it got "truncated".I have not problem with users !=logic.I have hard time to explain to my boss why logic ABC <> logic ABC! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-13 : 15:45:52
|
| Why would you textbox allow 450, your input parameter allow 200, and your column allow 4GB (text data type)? These should all be the same. You are only getting partial data in the table because you are allowing 450 in the textbox and SQL Server is truncating to 200 due to the size of the input parameter. Check out this example in Query Analyzer to see why this is a problem:DECLARE @s varchar(10) -- can only put max 10 characters in variable SET @s = '12345678901234567890' --let's put in more than 10PRINT @s -- truncationWhy use text data type when you don't need it!!!?Tara |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-04-13 : 16:20:28
|
| I am sorry I did not say it very clearly.The UI is a Access text box, the default size is its limit, and I have no idea how big it is.The 450 is the approximate number of characters I typed in there to test.And why it was designed the way it is, because no body knows better, including whoever created the original app and me, who moved its backend from Sybase to SQL2k.Let's say to store above content, what should be the right size in UI(Access/VB/Dot net), in Stored Procedure (type and length) and in table? Does space count? What about carriage return? I assume a CS degree will teach you all that, but too bad I did not get any formal training on things like this.Don’t get my wrong, I agree with your idea of they should be same, but just don’t know how, and I certainly appreciate the stuff I learnt here. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-13 : 16:27:32
|
| Well whatever your application allows to be entered is what the input parameter should be as well as the size of the column. Text is a waste if you aren't over varchar(8000) or varchar(4000) for unicode data.I don't have any idea if a CS degree would help out. I've got a math degree. These types of things I've learned on the job.Tara |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-13 : 16:35:47
|
| Part of his problem is he is using linked tables in access Tara and that opens the door for all kinds of problems.Hommer my see my response your other post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47567And mine is in electronic engineeringJimUsers <> Logic |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-04-14 : 11:19:45
|
Jim,I think ADP requires Windows OS to be at least Win 2000 SP3, and that is some thing we don't have yet.And the interesting part of the whole thing could be described in the following analogy.All these linked tables are in a big pipe, while this var(varchar(200)) is a small pipe within the big one. When the big pipe is more crowed (in production scenario), the small pipe got squeezed down to only allow passing of certain amount (3 lines?) of text. When the big pipe is wide open (in dev), the same small pipe will let more %^&* flow through. Amazing! I like this elastic piping business.Well, by using analogy, you folk should have a pretty good idea what my majors are.  -Hommer |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-14 : 13:26:59
|
| Hommer I run 2003 .adp applications on win98, NT4.0, win2000 and XP in our plant with no problems. The key to problem free aplications on these is to use the the devolopers Access runtime (which is free)on the PCs.JimUsers <> Logic |
|
|
|
|
text did not get save into table