0

I found this SQL statement elsewhere on StackOverFlow which I mondified for my use.

SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'tblContent' AND COLUMN_NAME = 'ContentTitle' OR COLUMN_NAME = 'ContentImageCaption'

This works and shows the maximum character length of both fields.

I want to put the length of the column names on to a web form. For example:

<input name="ContentTitle" type="text" value="<%=(rsContent.Fields.Item("ContentTitle").Value)%>" maxlength="40">
<p>Maximum Characters Allowed: 40</p>

using the length from CHARACTER_MAXIMUM_LENGTHto fill in the maxlength tag and the total characters allowed in the statement below.

I hope that makes sense.

Community
  • 1
  • 1

1 Answers1

0

I suppose you could read the column names and field lengths into an array and then generate the form fields based on that array...

dim SQL, rs
dim Flds, f
SQL = "SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH " _
    & "FROM information_schema.columns " _
    & "WHERE table_schema = DATABASE() AND table_name = 'tblContent' " _
    & "AND COLUMN_NAME = 'ContentTitle' OR COLUMN_NAME = 'ContentImageCaption'"
Set rs = Server.Createobject("ADODB.Recordset")
rs.Open SQL, "my_connection_string",1,2
If Not rs.EOF Then Flds = rs.GetRows
rs.Close
Set rs = Nothing
'... open rsContent, write out beginning of form, etc. ...
For f = 0 to UBound(Flds,2)
   Response.Write "<p>" & Flds(0,f) & ": <input type='text' name='" & Flds(0,f) & "'"
   Response.Write " maxlength='" & Flds(1,f) & "'"
   Response.Write " value='" & rsContent("ContentTitle") & "'>"
   Response.Write "<span class='instructions'>Maximum Characters Allowed: "
   Response.Write Flds(1,f) & "</span></p>"
Next
'... close rsContent, write out rest of form (e.g. a submit button), etc. ...

However, in reality the table schema is unlikely to contain all of the information you need for your form. (For example, human-readable field labels would be good.) So, ask yourself: how often is the table schema really gonna change? Wouldn't it be better to just write the information into the form manually?

If you want to use the same form for multiple tables, then some sort of auto-generation method might make sense. However, I'd still set up the "form definition" array manually.

dim F(2,3) ' (i,0) = field name, (i,1) = max length, (i,2) = size, (i,3) = label
F(0,0) = 2 ' = number of fields
F(1,0) = "ContentTitle" : F(1,1) = 40 : F(1,2) = 25 : F(1,3) = "Title" 
F(2,0) = "ImageCaption" : F(2,1) = 30 : F(2,2) = 20 : F(2,3) = "Caption"
Martha
  • 3,932
  • 3
  • 33
  • 42
  • Hi Martha. Thanks for the reply. I do have write in the maximum characters allowed at the moment but it was just something that came into my head, so, with the help of Google (other search engines are available) I found the code. Then I played with it a little to try and get the information in to usable content but I don't know enough about SQL, so I thought I'd ask the experts. It's not imperative, just a whim to see if it can be done. –  Mar 18 '14 at 10:00