Printable View
Hi,
In my one table, I have a varchar field. But when I try to insert value (having char >256) in that field, it is updating the field till 256 char only, not storing all chars. I have set max limit to that field that is 8000 still it is storing only 256 chars at max, please solve my problem.
I am using SQL 2000 server.
Thanks
Sanjivani
How do you update the field? Using VB, Access, .Net? Do you use bound controls or not?
I have tried it in many way, using Query Analyser, using VB, manually copying paste, but it is only copying paste 256 char.
In Vb, I have tried using Execute method of ADODB.Connection object.
Thanks
Sanjivani
Are you saying that when you execute an UPDATE statement in QA and the value is longer than 256 characters it truncates the string? Keep in mind that QA only displays the first 256 characters of a column.
Can you post the result of
Code:sp_help 'name of the table'
Yes in QA also, and in Vb also.
Please see the attached image.
Thanks
Sanjivani
QA cannot display more than 256 characters. Execute the following to get the length of data in the column (starting with the highest number)Quote:
Originally Posted by sanjivani
Do you get any results where the length is greater than 256 characters?Code:select len(ReportFields) from your_table order by len(ReportFields) desc
Also, post the VB code that does the update.
Actaully I am using this table to store some settings, I haven't made any iterface for user, it is for developer use.
I had tried in VB to just experiment. That stamement was like below.
dbconn.Execute "UPDATE MAILSETTINGS
SET ReportFields = 'Prefix;First Name;Middle Name;Last Name;Admission No.;Roll No.;Class & Sec;Father Name;EMail;Address;City;Pin;Phone;Fax;Emergency No.;Date of Birth;Nationality;Gender;Transport Avaialble;Bus Stop;Admission Class;Admission No.;Admission Yr;Pre Student ID; Children Son;Children Daughter;Class; Effect Date From;Photograph;Birth Certificate;Previous Certificate;Transfer Certificate;Indemnity Bond;Medical Certificate;Blood Group;Is Active;Other Certificate;Photo;House; Religion;Main Caste; Caste;Route;Teacher Ward;Migration Certificate;Student Type;Rights on Child;Passport No.;Passport Type;Passport CountryCode;Passport Given Name;Passport Place of Issue; Passport Issue Date;Passport Valid From; Passport VAlid To;Passport Expiry Date'
WHERE (MAILSETTINGS_ID = 9)"
After executing the query you suggested, yes it is displaying len >256 which is 736.
When I try to execute above update query it is updating the field till the red colored word only.
Ohh, I dont know how it is happening.
kaffenils, thanks for trying solution for me.
~Sanjivani
I mentioned it is updating till 256 char, sorry for that. Actually when I was trying for this and checked len of the field yesterday, it showed me 256 that time, so I just assumed it is not taking more than 256 char.
Thanks
Sanjivani
You say that when you execute my SELECT LEN statement it returns 736. That means that the the field has 736 characters. Try to open a recordset with this table in VB and debug.print the value to the Immediate Window. You should now see the complete text.Quote:
Originally Posted by sanjivani
It is showing "Prefix;First Name;Middle Name;Last Name;Admission No.;Roll No.;Class & Sec;Father Name;EMail;Address;City;Pin;Phone;Fax;Emergency No.;Date of Birth;Nationality;Gender;Transport Avaialble;Bus Stop;Admission Class;Admission No.;Admission Yr;Pre Student ID; Children Son;Children Daughter;Class; Effect Date From;Photograph;Birth Certificate;Previous Certificate;Transfer Certificate;Indemnity Bond;Medical Certificate;Blood"
But it should show
"Prefix;First Name;Middle Name;Last Name;Admission No.;Roll No.;Class & Sec;Father Name;EMail;Address;City;Pin;Phone;Fax;Emergency No.;Date of Birth;Nationality;Gender;Transport Avaialble;Bus Stop;Admission Class;Admission No.;Admission Yr;Pre Student ID; Children Son;Children Daughter;Class; Effect Date From;Photograph;Birth Certificate;Previous Certificate;Transfer Certificate;Indemnity Bond;Medical Certificate;Blood Group;Is Active;Other Certificate;Photo;House; Religion;Main Caste; Caste;Route;Teacher Ward;Migration Certificate;Student Type;Rights on Child;Passport No.;Passport Type;Passport CountryCode;Passport Given Name;Passport Place of Issue; Passport Issue Date;Passport Valid From; Passport VAlid To;Passport Expiry Date"
~Sanjivani
This is strange.
Are you using ADO?
OT, but isn't the caste system an oboslete, medieval and discriminating system? I was kind of hoping that the world had moved forward :confused:Quote:
Originally Posted by sanjivani
Yes world has moved forward and we dont need to think MAIN CASTE ect, but see today also we have to keep record of this for some use like while taking admision ect. So If any system is demanding for such detail that doesn't mean world has not moved forward ect....
~Sanjivani
Yes, I am using ADO....
~Sanjivani
**** DELETED **** Didn't see your last post.Quote:
Originally Posted by sanjivani
Why, didnt like my answer? :-)
~Sanjivani
The strange thing is thatQuote:
Originally Posted by sanjivani
"Prefix;First Name;Middle Name;Last Name;Admission No.;Roll No.;Class & Sec;Father Name;EMail;Address;City;Pin;Phone;Fax;Emergency No.;Date of Birth;Nationality;Gender;Transport Avaialble;Bus Stop;Admission Class;Admission No.;Admission Yr;Pre Student ID; Children Son;Children Daughter;Class; Effect Date From;Photograph;Birth Certificate;Previous Certificate;Transfer Certificate;Indemnity Bond;Medical Certificate;Blood"
is 421 characters, not 256. Hmmmm :confused:
Try this in QA
Does it print the correct value now?Code:set nocount on
declare @text varchar(8000), @Id int
set @Id=9
update MAILSETTINGS set ReportFields= 'Prefix;First Name;Middle Name;Last Name;Admission No.;Roll No.;Class & Sec;Father Name;EMail;Address;City;Pin;Phone;Fax;Emergency No.;Date of Birth;Nationality;Gender;Transport Avaialble;Bus Stop;Admission Class;Admission No.;Admission Yr;Pre Student ID; Children Son;Children Daughter;Class; Effect Date From;Photograph;Birth Certificate;Previous Certificate;Transfer Certificate;Indemnity Bond;Medical Certificate;Blood Group;Is Active;Other Certificate;Photo;House; Religion;Main Caste; Caste;Route;Teacher Ward;Migration Certificate;Student Type;Rights on Child;Passport No.;Passport Type;Passport CountryCode;Passport Given Name;Passport Place of Issue; Passport Issue Date;Passport Valid From; Passport VAlid To;Passport Expiry Date'
WHERE MAILSETTINGS_ID = 9
select @text=ReportFields from MAILSETTINGS where MAILSETTINGS_ID=@Id
print @text
I was repeating the ADO question, but then I saw your answer... to late :)Quote:
Originally Posted by sanjivani
About the caste question; I was just wondering why you would register this piece of information when the caste system is void (unless when used for positive "discimination").
Very strange, it is priting whole text that is
"Prefix;First Name;Middle Name;Last Name;Admission No.;Roll No.;Class & Sec;Father Name;EMail;Address;City;Pin;Phone;Fax;Emergency No.;Date of Birth;Nationality;Gender;Transport Avaialble;Bus Stop;Admission Class;Admission No.;Admission Yr;Pre Student ID; Children Son;Children Daughter;Class; Effect Date From;Photograph;Birth Certificate;Previous Certificate;Transfer Certificate;Indemnity Bond;Medical Certificate;Blood Group;Is Active;Other Certificate;Photo;House; Religion;Main Caste; Caste;Route;Teacher Ward;Migration Certificate;Student Type;Rights on Child;Passport No.;Passport Type;Passport CountryCode;Passport Given Name;Passport Place of Issue; Passport Issue Date;Passport Valid From; Passport VAlid To;Passport Expiry Date"
But why I am not getting whole text from VB. Using select query in VB, I am getting only partial text.
What could be the reason.
~Sanjivani
Run the UPDATE statement from VB (with MAILSETTINGS_ID=9). Then run the TSQL code (without the UPDATE statement) in QA and see if it still prints the whole text.
Do you have the latest version of MDAC?
One more thing (a little bit out of scope), you don't need to create an additional index on MAILSETTINGS_ID (index IX_MailSettings) when the column is the primary key. The primary key constraint is already an index.
Yes, it is priting whole text. I have executed VB code to update the field, then printed that feild value in the same way which you have told me in previous post by commenteing that update statement there. It is printing whole text.
I think it is 6.0.88.62, I am telling you the version of MSRDO20.DLL
file, one of my friend told that this file gives version of MDAC, so I am telling you.
BTW, I have not installed any service pack ect, it is all default drivers are installed on my machine, as far as I know.
~Sanjivani
That is the driver for RDO. RDO is dinosaur technology and I really hope that you don't use it.
Open References in VB and tell me what version of Microsoft ActiveX Data Objects that is referenced (2.x something, latest is 2.8).
No I dont use RDO.
Its 2.1.
I guess, I should install VB's SP 5, it will install the latest drivers for ADO. Should I install it. What do you say that might be the problem?
~Sanjivani
Yes, it is solved.
I installed VB SP5. I could not see the latest ActiveX database object but when I fired select statment from VB to get field value, it returned me whole string.
So, I can say that VB SP5 solved my problem at last.
Thanks Kaffenils for your help, you seem master in databse :-)
~Sanjivani
There is a VS SP-6 available. Its usually best to run the latest SP available. Even more so when its been out for a while and deemed safe. :)
Good to hear that your problem is fixed.Quote:
Originally Posted by sanjivani
You should also download and install MDAC 2.8 which contains the latest database drivers. Click the link below to download it
http://www.microsoft.com/downloads/d...DisplayLang=en
Thanks kaffenils, Yes I will download it.
~Sanjivani
And also, RobDog888, thanks for advice. Actually I dont have SP6 with me. Once I will get it , I will surely start using that.
Thanks
Sanjivani