how to build db for using in listboxes in ASP

gilgalbiblewhee · #1 (**permalink**) 05-10-06, 18:45

I want to have a database on a book where this is how it goes:

Code:

Preface
Introduction
-subtitle1
-subtitle2
Chapter 1
-subtitle 1
-subtitle 2...

I have MSAccess 2000. How do you recommend me to build it? I want to use it with a combination of listboxes in ASP.

csamuels · #2 (**permalink**) 05-18-06, 16:24

what do you want it to do?

wayneph · #3 (**permalink**) 05-23-06, 18:46

You could have a Chapters table that stores the top level information, and a SubTitles table that sores the second level. In the SubTitles table you'll need a column that points back to the main table.

Then depending on the data you'll have to decide if you want to load all of the data up front and change it using JavaScript, or if you want to post the Chapter selection back to the server, and change the data that way. You could also look at a AJax technique to take advantage of both methods.

myle · #4 (**permalink**) 05-26-06, 15:16

here some code that will build a dropbox on a webpage just by

call BuildDrop(FieldName, TableName,pick,Action)

I use it all the time

sorry about the code been misse

Code:

Sub BuildDrop(FieldName, TableName,pick,Action)
'**************************************
'This Will build a Dropbox in a Web site
'Base on a Database Feild
'**************************************
' ReWrite this Function To handle -1
' rule with this is Autonumber must have same name as table
' use Query to get it right.

	Dim IsItSelected
	Dim SQL, ID , IDF , Picked2 , Picked , rsBuildDrop , IID , IDD , SS
	Dim OutputLine
	ID = TableName & "ID"
	IDF = FieldName
	
	Picked = Picked2
	if pick="" then pick=" "
	Set rsBuildDrop = Server.CreateObject("ADODB.Recordset")
	sql = ""
	if VarType(pick) = 8 then '8 = string 
		sql = sql & "SELECT " & TableName & "." & FieldName
		sql = sql & " FROM " & TableName & " "
		SQL = SQL & " GROUP BY " & TableName & "." & FieldName
		SQL = SQL & " ORDER BY " & TableName & "." & FieldName &  ";"
	else
		sql = sql & "SELECT " & TableName & "." & TableName & "ID ," & TableName & "." & FieldName 
		sql = sql & " FROM " & TableName & " "
		SQL = SQL & " GROUP BY " & TableName & "." & FieldName & "," & TableName & "." & TableName & "ID"
		SQL = SQL & " ORDER BY " & TableName & "." & TableName & "ID;"
End if
  rsBuildDrop.Open SQL, connstring, adOpenKeyset, adLockPessimistic, adCmdText
	
		if VarType(pick) = 8 then
		Picked = rsBuildDrop.Fields(FieldName)
		IID = IDF
		IDD = "Please Select"
		SS = ""
		else
		Picked = rsBuildDrop.Fields(ID) 
		IID = ID
		IDD = "0"
		SS = "selected"
		end if
	
	
	Response.write vbnewline & "<select  name=""" & IID & """ " & Action &" >" & vbnewline
	
	Response.Write "<option " &  " value="""& IDD & """  "& SS & " >Please Select</option>" & vbnewline
	

	Do Until rsBuildDrop.EOF

		if VarType(pick) = 8 then
		Picked = rsBuildDrop.Fields(FieldName)
		else
		Picked = rsBuildDrop.Fields(ID) 
		end if
		

		if pick = rsBuildDrop.Fields(0) then
			SS = "selected"
		else
			SS = ""
		End if
		OutputLine = "<option " & SS & " value=""" & Picked &  """ >" & trim(rsBuildDrop.Fields(FieldName)) & "</option>" & vbnewline
		Response.write OutputLine
		rsBuildDrop.movenext
	loop	
	rsBuildDrop.close
	set rsBuildDrop = nothing
	Response.write "</select>" & vbnewline
	
	if VarType(pick) = 8 then
	Response.write "Or <INPUT type=""text"" id=""S" & IDF & """ name=""S" & IDF & """ size=15 value=""" & trim(" ") & """>"
	else
	END IF
End Sub

Some rule are the primerykey must have the same name as the tablename

eg tablename Books

the primary key has to be named booksID

you can make querys to match the Rules

Internet Services

Web Development

Digital Marketing

Consumer Tech