Counting how many characters per columns (database field)

use Len function

mLength = Len(SR("myMemoField"))

from sql try to get the max length

SELECT MAX(Len(myMemoField)) FROM myTable

sorry i am new to this
i need an automated way that writes on the screen the number of characters used per field / table

Hi

I think you are after something like this

<%


Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\inetpub\wwwroot\asp\db\employees.mdb;Uid=Admin;Pwd=;"


set RS = server.createobject("ADODB.recordset")


RS.Open "SELECT * FROM tblemployees", DataConn



FOR i=0 to RS.Fields.Count-1
   response.write "NAME=" & RS.Fields(i).Name
   response.write "<BR>SIZE=" & RS.Fields(RS.Fields(i).Name).definedsize
   response.write "<BR><BR>"
NEXT

RS.Close
DataConn.Close
set RS = Nothing
set DataConn = Nothing


 %>

cheers

Len

also

FOR i=0 to RS.Fields.Count-1
   response.write "NAME=" & RS.Fields(i).Name
   response.write "<BR>SIZE=" & RS.Fields(RS.Fields(i).Name).definedsize
   response.write "<BR>Type=" & RS.Fields(RS.Fields(i).Name).type
   response.write "<BR><BR>"
NEXT

you should then be able to work out the Name, datatype & size for your new SQL Server Field

cheers

Len

it does not give me the result i need

it gives the following :

NAME=cname
SIZE=255
Type=202

the cname field is a text field (type 202) and it supports 255 characters
but it does not tell me how many characters ar occupied or left ??
this is what i need for my SQL migration

DTS-Export data(even without data).

Source as u r mS-Access
Destn as S-SQL server

And finish migration.
Why to worry lenght of field before hand.

DTS is there to take care.
After generate MS-SQL script and trim it.

because i have lot's of fields and whilevarchars in SQL cannot exceed 8064 charatcers and while i cannot use the text or ntext type
i need to set my varchars correctly

ie, if all my cname requires maximum 24 charcters ten i set my varchar to 25 rather then fixing it blindly to 50

DTS is not ok for me, i have already tried it
 

If you use a combination of mine & nubeks solution you will e able to get what you need, I don't have time to fully code this at the moment but  

do something like



  FOR i=0 to RS.Fields.Count-1
     response.write "NAME=" & RS.Fields(i).Name
     response.write "<BR>SIZE=" & RS.Fields(RS.Fields(i).Name).definedsize
     response.write "<BR>Type=" & RS.Fields(RS.Fields(i).Name).type
     

     For each row in RS ' set up a counter
       maxlength = 0
       length = row(RS.Fields(i).Name)
        if length > maxlength then
           maxlength =length
        end if
     Loop
     response.write "<BR>maxlength of field=" & maxlength
     response.write "<BR><BR>"
  NEXT

(not the most effiicient, but as its a one off)

you will now have your field length, the maximum length of any field in that row, and you can calculate any other value you want

cheers

Len

not working
i am getting this error

Microsoft VBScript compilation error '800a040e'

'loop' without 'do'

/csasp_db_countchar.asp, line 22

Loop
^

That was just to give you the idea

For irow = 0 to  RS.rowcount -1
       maxlength = 0
       length = len(row(RS.Fields(irow).Name))
        if length > maxlength then
           maxlength =length
        end if
next

i am new to this and with your code errors, i guess i am too far from solution or even having an idea
you new code generates the following error

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'rowcount'

/csasp_db_countchar.asp, line 16

I hurried when i posted your answer as ACCEPTED
but in fact it's not resolved

i am not getting any error but the results obtained are not accurate
ie , it says for example
cdescription
SIZE= 536870910
Type= 203
longest entry in this field is 128 chars long  

when i check my table
i have records with 2966 characters

and also when it says

specialoffer
SIZE= 100
Type= 202
longest entry in this field is 0 chars long  

After checking my table this fields contains a message of 86 characters

what's the problem

oopps  sorry, should be

 RS.movefirst
 maxlength = 0
  do While not RS.EOF
     
       

great,
it works great
thank you