article

Building an Input Form Dynamically From a Database.

Email
Submitted on: 1/5/2015 1:04:00 PM
By: Joshua i. Lopez (from psc cd)  
Level: Beginner
User Rating: By 35 Users
Compatibility: ASP (Active Server Pages)
Views: 4173
 
     Updated 3/23/2001 11:17 PM !!!!!!!!!!!!!!!!!!!!! This code will allow you to build an input form "on the fly" from a database. Instead of creating and hard coding the same old input form you can automatically build your form based on column names or values in your database! Just added is another page that will submit your data to your database "on the fly", truely making the process of creating web based entry forms DYNAMIC!!!!!!!! Now whenever you have to alter your input forms you just have to change your SELECT statement, no more HTML!! Please vote for this code if you find it useful, and feel free to send me any questions you have regarding the code. Thank you.

This article has accompanying files
 
				

This is the first of 2 Pages you will need...

<html>
<body>
<Form Name="Form" Type="Submit" Method="Post" Action="dynamic2.asp">


<%
<!--~----- Dim Your Variables -----~-->
Dim MyConn, SQL, RS, i

<!--~----- Create the Connection and Open the Connection to the Database -----~-->
<!--~----- This Will Connect to a MS-SQL Database -----~-->
<!--~----- To Connect to an Access Database Replace the Bottom 3 Lines of Code with either... -----~-->
<!--~----- MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; -----~--> 
<!--~----- DBQ=C:\Databases\demo.mdb" -----~-->
<!--~----- or -----~-->
<!--~----- MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data -----~--> 
<!--~----- Source=C:\Databases\demo.mdb" -----~-->
<!--~----- or -----~-->
<!--~----- MyConn.Open DSN_Name -----~-->
Set MyConn=Server.CreateObject("ADODB.Connection")
Myconn.ConnectionString = "Provider=SQLOLEDB; Data Source=
Your SQL Server; Initial Catalog=Your Database; User ID=Your Login; Password=Your Password"
Myconn.open

<!--~----- Wriye your SQL statement -----~-->
<!--~----- In this case we have 2 SQL statements because I have a different value for my form fields other than its name -----~-->
<!--~----- If your checkbox/text field will have the same name and value you can safely remove from "SQL1 = "Select" down -----~-->
SQL = "Select
Column Name(s) in your Database that holds values that will become the checkbox/textfield name From Your Table Name"
Set RS = MyConn.Execute(SQL)
SQL1 = "Select
Column Name in your Database that holds values that will become the checkbox/textfield value From Your Table Name"
Set RS1 = MyConn.Execute(SQL1)

<!--~----- Read All of the Records with EOF -----~-->
While not RS.EOF

<!--~----- Build Your Checkboxs -----~-->
<!--~----- You Can Also Build Text Fields by Modifying the Second Line to Read -----~-->
<!--~----- Response.Write rs.fields(intCol).Value & ": <input type=Text size=15 name=" & rs.fields(intCol).Value & "><br>" & vbcrlf -----~-->
<!--~----- If you want the checkboxs or text fields to display column names instead of data values replace any .Value with .Name -----~--> 
<!--~----- Example -----~-->
<!--~----- Response.Write rs.fields(intCol).Name & ": <input type=checkbox name=" & rs.fields(intCol).Name & " value=" & rs.fields(intCol).Name & "><br>" & vbcrlf -----~-->
<!--~----- Also, if your SQL select statement only had one line (same name/value for checkbox/textfields) the following lines of code would read... -----~--> 
<!--~----- For i = 0 to RS.fields.count - 1 -----~-->
<!--~----- Response.Write RS.fields(i).Value & ":<input type=checkbox name='" & RS.fields(i).Value & "' value=" & RS.fields(i).Value & "><br>" & vbcrlf -----~-->
<!--~----- RS.MoveNext -----~-->
For i = 0 to RS.fields.count and RS1.fields.count - 1
Response.Write RS.fields(i).Value & ":<input type=checkbox name='" & RS.fields(i).Value & "' value=" & RS1.fields(i).Value & "><br>" & vbcrlf
RS.MoveNext
RS1.MoveNext
Next
Wend


RS.Close

RS1.Close
MyConn.Close
Set RS = Nothing

Set RS1 = Nothing
Set MyConn = Nothing

%>

<br>
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>

 

Name this page dynamic1.asp

 

 

Now we create the page that will insert our data into your database

 

<html>
<body>


<%

Dim MyConn, MySQL, rs, i

Set MyConn=Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
MyConn = "Provider=SQLOLEDB; Data Source=
Your SQL Server; Initial Catalog=Your Database; User ID=Your SQL Login; Password=Your Password"
MYSQL = "SELECT
Column Name you want data entered into FROM Your Table Name"
rs.Open MySQL, MyConn, 1, 3

%>

<%

for i = 1 to Request.form.count -1
rs.addnew
rs.Fields("
Column Name you want data entered into") = Request.form(i)
Next
rs.update
Response.Write "Thank you"

%>

</body>
</html>

 

Name this page dynamic2.asp

 

Vote for me if you find this useful, let me know if you need any help with the code!!!

winzip iconDownload article

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.