Populating multiple textboxes with multiple records for update page

D

Drew

I have a db table like the following,

UID, int auto-increment
RegNo
Person
Relation
YearsKnown

Now here is some sample data from this table,

1 01292 John Doe Father 39
2 01292 Jane Doe Mother 39
3 01292 Janet Doe Sister 34
4 08982 Dr. Mosely Doctor 15
5 08982 Jack Smith Aide 14
6 01292 Jane Thomas Aide 19

I have a table with 25 rows and 3 columns, with a textbox for name,
relationship and years known. I would like to populate the 1st name textbox
with the 1st name from the db, and the 1st relationship txtbox with the
relationship from the db, etc. for each RegNo. This will be used for an
update page.

How can this be done?

TIA,
Drew
 
R

Ray Costanzo [MVP]

What part are you stuck on? How to structure your query? How to make a
connection to the database? How to write values from a recordset into HTML
in an ASP page? Something else? What code do you have so far?

Ray at work
 
D

Drew

Well, I am currently just trying to think about how this is going to work.
I have the structure built, and I don't think the will be a problem, just
using querystring to pass the RegNo.

The only code that I have is for the page, and most of it is html. This is
the 2nd step of 20 steps, and the page inserts the data from the prior page,
and then if there is data, then it populates the textboxes with the data, if
there isn't any data it displays empty textboxes for inserting.

I haven't figured out how to insert mulitple items, btu that didn't sound
like the problem, I think that populating the textboxes iwll be more of a
problem than inserting the data.

Thanks,
Drew
 
R

Ray Costanzo [MVP]

Can you show us what you have and what you're stuck on? Let's just take it
one step at a time. Select a step please. :]

Ray at work
 
D

Drew

Here is my code for the page, cliELP_step02.asp,

Now I need the results from the recordset, rsPeopleWhoContributed to be
broken down and populated in the textboxes below, named name, relationship,
yearsknown. So the data could look like this,

UID RegNo Name Relationship YearsKnown
1 01292 John Doe Father 39
2 01292 Jane Doe Mother 39
3 01292 Janet Doe Sister 34
4 08982 Dr. Mosely Doctor 15
5 08982 Jack Smith Aide 14
6 01292 Jane Thomas Aide 19

So if the user selected RegNo 01292 on cliELP_step01.asp, and then clicked
Goto Step 2, then it would query the db using RegNo 01292 and that recordset
needs to be populated into the textboxes. So textbox name 1 would have John
Doe, and relationship txtbox 1 would have Father and textbox yearsknown 1
would have 39. So on and so on.

TIA,
Drew

Code below,

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/CliELP.asp" -->
<%
'Insert data from cliELP_step01.asp
If Request.Form("actions") <> 1 Then

'SQL for inserting into SQL Server
sql="INSERT INTO ELPGeneral (RegNo,DesiredName)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("RegNo") & "',"
sql=sql & "'" & Request.Form("DesiredName") & "')"

'execute the insert to SQL Server
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_CliELP_STRING
MM_editCmd.CommandText = sql
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

Else

'Must already be in the DB, so query the DB by RegNo
Dim rsPeopleWhoContributed
Dim rsPeopleWhoContributed_numRows

Set rsPeopleWhoContributed = Server.CreateObject("ADODB.Recordset")
rsPeopleWhoContributed.ActiveConnection = MM_CliELP_STRING
rsPeopleWhoContributed.Source = "SELECT RegNo, PeopleWhoContributed FROM
dbo.ELPPeopleWhoContributed WHERE RegNo = " & Request.Form("RegNo")
rsPeopleWhoContributed.CursorType = 0
rsPeopleWhoContributed.CursorLocation = 2
rsPeopleWhoContributed.LockType = 1
rsPeopleWhoContributed.Open()

rsPeopleWhoContributed_numRows = 0

End If%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>People Who Contributed - Essential Lifestyle Plan - SWVTC
Intranet</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../css/SWVTCintranet.css" rel="stylesheet" type="text/css">
<!--[if gte IE 6.000]>
<script type="text/javascript" src="pngfix.js"></script>
<![endif]-->
<style type="text/css">
<!--
..style1 {font-weight: bold}
..style2 {
font-size: 18px;
font-weight: bold;
}
..style3 {font-size: 14px}
-->
</style>
</head>
<body>
<!--#include file="../include/header.asp" -->
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr bgcolor="#FFFFFF">
<td colspan="3" valign="top"><img src="../img/spacer.gif" width="1"
height="10"></td>
</tr>
<tr bgcolor="#FFFFFF">
<td width="19%" valign="top"><!--#include
file="../include/nav_intranet.asp" --></td>
<td width="1%"><img src="../img/spacer.gif" width="10" height="1"></td>
<td width="80%" valign="top"><div align="center">
</div> <div align="center">
<table width="100%" border="0" cellpadding="0" cellspacing="0"
bgcolor="CCCCCC">
<tr>
<td width="4%"><img src="../img/tl_corner_CCCCCC.jpg" width="26"
height="26"></td>
<td width="92%">&nbsp;</td>
<td width="4%"><img src="../img/tr_corner_CCCCCC.jpg" width="25"
height="26" align="right"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><table width="100%" border="0" cellspacing="0"
cellpadding="0">
<tr>
<td><span class="style3">Back to Main Page </span></td>
</tr>
<tr>
<td><img src="../img/spacer.gif" width="1" height="10"></td>
</tr>
<tr>
<td><div align="center" class="style2">Client Essential
Lifestyle Plan</div></td>
</tr>
<tr>
<td><img src="../img/spacer.gif" width="1" height="10"></td>
</tr>
<tr>
<td><div align="center"><strong>Step 2 - People Who
Contributed </strong></div></td>
</tr>
<tr>
<td><img src="../img/spacer.gif" width="1" height="10"></td>
</tr>
</table>
<form name="form1" method="post" action="cliELP_step03.asp">
<table width="100%" border="0" cellspacing="0"
cellpadding="0">
<tr>
<td><p>Please enter the People who Contributed to
<strong><%=Request.Form("DesiredName")%>'s</strong> Plan.</p>
</td>
</tr>
<tr>
<td width="28%">&nbsp;</td>
</tr>
<tr>
<td valign="top"><table width="100%" border="0"
cellspacing="5" cellpadding="0">
<tr>
<td width="6%">&nbsp;</td>
<td width="44%"><div
align="center"><strong>Name</strong></div></td>
<td width="27%"><div
align="center"><strong>Relationship</strong></div></td>
<td width="23%"><div align="center"><strong>Yrs Known
</strong></div></td>
</tr>
<tr>
<td><strong>1</strong></td>
<td>
<div align="center">
<input name="name" type="text" id="name"
size="25">
</div></td>
<td>
<div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td>
<div align="center">
<input name="yearsknown" type="text"
id="yearsknown" size="8">
</div></td>
</tr>
<tr>
<td><strong>2</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>3</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>4</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>5</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>6</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>7</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>8</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>9</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>10</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>11</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>12</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>13</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>14</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>15</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>16</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>17</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>18</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>19</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>20</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>21</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>22</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>23</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>24</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
<tr>
<td><strong>25</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
size="8">
</div></td>
</tr>
</table></td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td><img src="../img/spacer.gif" width="150" height="1">
<input type="submit" name="Submit" value="Save Changes
and Go To Step 3 >"></td>
</tr>
<tr>
<td><input name="DesiredName" type="hidden"
id="DesiredName" value="<%=Request.Form("DesiredName")%>">
<input name="RegNo" type="hidden" id="RegNo"
value="<%=Request.Form("RegNo")%>">
<input name="actions" type="hidden" id="actions"
value="<%=Request.Form("actions")%>"></td>
</tr>
</table>
</form></td>
<td>&nbsp;</td>
</tr>
<tr>
<td><img src="../img/bl_corner_CCCCCC.jpg" width="26"
height="25"></td>
<td>&nbsp;</td>
<td><img src="../img/br_corner_CCCCCC.jpg" width="25"
height="25" align="right"></td>
</tr>
</table>
</div>
<div align="center"></div> <div align="center"></div> <div
align="center"></div> <div align="center"></div></td>
</tr>
</table>
<!--#include file="../include/footer.asp" -->
</body>
</html>


Ray Costanzo said:
Can you show us what you have and what you're stuck on? Let's just take
it one step at a time. Select a step please. :]

Ray at work


Drew said:
Well, I am currently just trying to think about how this is going to
work. I have the structure built, and I don't think the will be a
problem, just using querystring to pass the RegNo.

The only code that I have is for the page, and most of it is html. This
is the 2nd step of 20 steps, and the page inserts the data from the prior
page, and then if there is data, then it populates the textboxes with the
data, if there isn't any data it displays empty textboxes for inserting.

I haven't figured out how to insert mulitple items, btu that didn't sound
like the problem, I think that populating the textboxes iwll be more of a
problem than inserting the data.
Click to expand...
Click to expand...
 
R

Ray Costanzo [MVP]

Okay, what you're trying to do is not an uncommon thing. That does not mean
that it is a trivial task, however. But, you're using Dreamweaver. If you
continue to use Dreamweaver for ASP editing, then it really becomes a
question of how to use Dreamweaver to create this functionality. I'd
imagine that it has the ability to do this, although you won't really find
too many Dreamweaver users around here.

If you choose to do ASP development by hand-coding (at least the ASP part),
then it's a matter of determining what needs to appear on your page, how you
get it to appear on your page, what data needs to be sent to the server, how
to ensure that data is sent to the server, and then what to do with that
data when it is sent to the server.

Do you want the ability to edit all the records at the same time? That's a
little more complex than one at a time, so for the time being, I'll assume
that you only want to do one at a time. If you want to do all at a time,
that's fine; just post back.

For the one at a time method, I'll break it out into multiple pages for
simplicity's sake. The process here is:

showall.asp:
Create a page that lists records and provides a link to an edit page, which
will display the records with the editable fields in text boxes. It will
pass the UID in the querystring to edit.asp.

edit.asp:
Pull the id from the querystring, and get the corresponding record in the
database from it. Then display current values in a form with the values
being displayed in textboxes. The ID will be passed to the next page in a
hidden input.

save.asp:
Get the data posted from the form and execute an UPDATE [non]query with that
information using a WHERE clause containing the UID that was passed in the
hidden form field on edit.asp.




showall.asp:
<%
sSQL = "SELECT UID,RegNo,[Name],Relationship,YearsKnown FROM ELPGeneral"
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
Set oRS = yourADOObject.Execute(sSQL)
If oRS.EOF Then
Response.Write "No items.
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
End If
%>

<table>
<%
''.GetRows is for another day!
Do While Not oRS.EOF
%>
<tr>
<td><a
href="edit.asp?id=<%=oRS.Fields.Item(0).Value)%>"><%=oRS.Fields.Item(1).Value%></a></td>
<td><%=oRS.Fields.Item(2).Value%></td>
<td><%=oRS.Fields.Item(3).Value%></td>
<td><%=oRS.Fields.Item(4).Value%></td>
</tr>
<%
oRS.MoveNext
Loop
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
%>
</table>


edit.asp:
<%
Dim iID
iID = Request.Querystring("id") ''validate this
sSQL = "SELECT RegNo,[Name],Relationship,YearsKnown FROM ELPGeneral WHERE
UID=" & iID
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
Set oRS = yourADOObject.Execute(sSQL)
If oRS.EOF Then
Response.Write "Not found."
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
End If
%>

<form method="post" action="save.asp">
Reg No: <input name="txtRegNo" type="text"
value="<%=oRS.Fields.Item(0).Value%>"><br>
Name: <input name="txtName" type="text"
value="<%=oRS.Fields.Item(1).Value%>"><br>
Relationship: <input name="txtRelationship" type="text"
value="<%=oRS.Fields.Item(2).Value%>"><br>
Years Known: <input name="txtYearsKnown" type="text"
value="<%=oRS.Fields.Item(3).Value%>"><br>
<input name="hidID" type="hidden" value="<%=iID%>">
<input type="submit">
</form>



save.asp:
<%
Dim iID, sRegNo, sName, sRelationship, iYearsKnown
Dim sSQL

iID = Request.Form("hidID")
sRegNo = Request.Form("txtRegNo")
sName = Request.Form("txtName")
sRelationship = Request.Form("txtRelationship")
iYearsKnown = Request.Form("txtYearsKnown")

sSQL = "UPDATE ELPGeneral SET RegNo=" & TextIn(sRegNo) & ",[Name]=" &
TextIn(sName) & ",Relationship=" & TextIn(sRelationship) & ",YearsKnown=" &
iYearsKnown & " WHERE UID=" & iID
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
yourADOObject.Execute sSQL,,129
yourADOObject.Close
Set yourADOObject = Nothing

Response.Write "Saved."

Function TextIn(s)
TextIn = "'" & Replace(s, "'", "''")
End Function
%>

Ray at home
 
D

Drew

I have gotten the saving part down pat, but my problem lies in populating
the multiple fields with records from the recordset. If my data looks like
this,

UID RegNo Name Relationship YearsKnown
1 01292 John Doe Father 39
2 01292 Jane Doe Mother 39
3 01292 Janet Doe Sister 34
4 08982 Dr. Mosely Doctor 15
5 08982 Jack Smith Aide 14
6 01292 Jane Thomas Aide 19

Then RegNo 01292 has 4 records, if RegNo 01292 was selected on the first
page I would like for the page to look like this, (this is only a snippet of
the code)

<table width="100%" border="0" cellspacing="5" cellpadding="0">
<tr>
<td width="6%">&nbsp;</td>
<td width="44%"><div
align="center"><strong>Name</strong></div></td>
<td width="27%"><div
align="center"><strong>Relationship</strong></div></td>
<td width="23%"><div align="center"><strong>Yrs Known
</strong></div></td>
</tr>
<tr>
<td><strong>1</strong></td>
<td>
<div align="center">
<input name="name" type="text" id="name"
value="John Doe" size="25">
</div></td>
<td>
<div align="center">
<input name="relationship" type="text"
id="relationship" value="Father" size="15">
</div></td>
<td>
<div align="center">
<input name="yearsknown" type="text"
id="yearsknown" value="39" size="8">
</div></td>
</tr>
<tr>
<td><strong>2</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" value="Jane
Doe" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" value="Mother" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
value="39" size="8">
</div></td>
</tr>
<tr>
<td><strong>3</strong></td>
<td><div align="center">
<input name="name" type="text" id="name"
value="Janet Doe" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" value="Sister" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
value="34" size="8">
</div></td>
</tr>
<tr>
<td><strong>4</strong></td>
<td><div align="center">
<input name="name" type="text" id="name" value="Jane
Thomas" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" value="Aide" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text" id="yearsknown"
value="19" size="8">
</div></td>
</tr>

So in other words, there are 25 rows of textboxes, with 3 columns of
textboxes. If there are 25 records pertaining to the RegNo, then all the
boxes will be filled with data.

My question is How do you break down the RS so the textboxes can be
populated?

Thanks,
Drew




Ray Costanzo said:
Okay, what you're trying to do is not an uncommon thing. That does not
mean that it is a trivial task, however. But, you're using Dreamweaver.
If you continue to use Dreamweaver for ASP editing, then it really becomes
a question of how to use Dreamweaver to create this functionality. I'd
imagine that it has the ability to do this, although you won't really find
too many Dreamweaver users around here.

If you choose to do ASP development by hand-coding (at least the ASP
part), then it's a matter of determining what needs to appear on your
page, how you get it to appear on your page, what data needs to be sent to
the server, how to ensure that data is sent to the server, and then what
to do with that data when it is sent to the server.

Do you want the ability to edit all the records at the same time? That's
a little more complex than one at a time, so for the time being, I'll
assume that you only want to do one at a time. If you want to do all at a
time, that's fine; just post back.

For the one at a time method, I'll break it out into multiple pages for
simplicity's sake. The process here is:

showall.asp:
Create a page that lists records and provides a link to an edit page,
which will display the records with the editable fields in text boxes. It
will pass the UID in the querystring to edit.asp.

edit.asp:
Pull the id from the querystring, and get the corresponding record in the
database from it. Then display current values in a form with the values
being displayed in textboxes. The ID will be passed to the next page in a
hidden input.

save.asp:
Get the data posted from the form and execute an UPDATE [non]query with
that information using a WHERE clause containing the UID that was passed
in the hidden form field on edit.asp.




showall.asp:
<%
sSQL = "SELECT UID,RegNo,[Name],Relationship,YearsKnown FROM ELPGeneral"
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
Set oRS = yourADOObject.Execute(sSQL)
If oRS.EOF Then
Response.Write "No items.
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
End If
%>

<table>
<%
''.GetRows is for another day!
Do While Not oRS.EOF
%>
<tr>
<td><a
href="edit.asp?id=<%=oRS.Fields.Item(0).Value)%>"><%=oRS.Fields.Item(1).Value%></a></td>
<td><%=oRS.Fields.Item(2).Value%></td>
<td><%=oRS.Fields.Item(3).Value%></td>
<td><%=oRS.Fields.Item(4).Value%></td>
</tr>
<%
oRS.MoveNext
Loop
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
%>
</table>


edit.asp:
<%
Dim iID
iID = Request.Querystring("id") ''validate this
sSQL = "SELECT RegNo,[Name],Relationship,YearsKnown FROM ELPGeneral WHERE
UID=" & iID
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
Set oRS = yourADOObject.Execute(sSQL)
If oRS.EOF Then
Response.Write "Not found."
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
End If
%>

<form method="post" action="save.asp">
Reg No: <input name="txtRegNo" type="text"
value="<%=oRS.Fields.Item(0).Value%>"><br>
Name: <input name="txtName" type="text"
value="<%=oRS.Fields.Item(1).Value%>"><br>
Relationship: <input name="txtRelationship" type="text"
value="<%=oRS.Fields.Item(2).Value%>"><br>
Years Known: <input name="txtYearsKnown" type="text"
value="<%=oRS.Fields.Item(3).Value%>"><br>
<input name="hidID" type="hidden" value="<%=iID%>">
<input type="submit">
</form>



save.asp:
<%
Dim iID, sRegNo, sName, sRelationship, iYearsKnown
Dim sSQL

iID = Request.Form("hidID")
sRegNo = Request.Form("txtRegNo")
sName = Request.Form("txtName")
sRelationship = Request.Form("txtRelationship")
iYearsKnown = Request.Form("txtYearsKnown")

sSQL = "UPDATE ELPGeneral SET RegNo=" & TextIn(sRegNo) & ",[Name]=" &
TextIn(sName) & ",Relationship=" & TextIn(sRelationship) & ",YearsKnown="
& iYearsKnown & " WHERE UID=" & iID
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
yourADOObject.Execute sSQL,,129
yourADOObject.Close
Set yourADOObject = Nothing

Response.Write "Saved."

Function TextIn(s)
TextIn = "'" & Replace(s, "'", "''")
End Function
%>

Ray at home


Drew said:
Here is my code for the page, cliELP_step02.asp,

Now I need the results from the recordset, rsPeopleWhoContributed to be
broken down and populated in the textboxes below, named name,
relationship, yearsknown. So the data could look like this,

UID RegNo Name Relationship YearsKnown
1 01292 John Doe Father 39
2 01292 Jane Doe Mother 39
3 01292 Janet Doe Sister 34
4 08982 Dr. Mosely Doctor 15
5 08982 Jack Smith Aide 14
6 01292 Jane Thomas Aide 19

So if the user selected RegNo 01292 on cliELP_step01.asp, and then
clicked Goto Step 2, then it would query the db using RegNo 01292 and
that recordset needs to be populated into the textboxes. So textbox name
1 would have John Doe, and relationship txtbox 1 would have Father and
textbox yearsknown 1 would have 39. So on and so on.

TIA,
Drew

Code below,

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/CliELP.asp" -->
<%
'Insert data from cliELP_step01.asp
If Request.Form("actions") <> 1 Then

'SQL for inserting into SQL Server
sql="INSERT INTO ELPGeneral (RegNo,DesiredName)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("RegNo") & "',"
sql=sql & "'" & Request.Form("DesiredName") & "')"

'execute the insert to SQL Server
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_CliELP_STRING
MM_editCmd.CommandText = sql
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
Click to expand...
Click to expand...
 
R

Ray Costanzo [MVP]

Take what I used in showall.asp, but instead of writing the values in
between <td></td> tags, write the values into textboxes.

Ray at work
Drew said:
I have gotten the saving part down pat, but my problem lies in populating
the multiple fields with records from the recordset. If my data looks like
this,

UID RegNo Name Relationship YearsKnown
1 01292 John Doe Father 39
2 01292 Jane Doe Mother 39
3 01292 Janet Doe Sister 34
4 08982 Dr. Mosely Doctor 15
5 08982 Jack Smith Aide 14
6 01292 Jane Thomas Aide 19

Then RegNo 01292 has 4 records, if RegNo 01292 was selected on the first
page I would like for the page to look like this, (this is only a snippet
of the code)

<table width="100%" border="0" cellspacing="5" cellpadding="0">
<tr>
<td width="6%">&nbsp;</td>
<td width="44%"><div
align="center"><strong>Name</strong></div></td>
<td width="27%"><div
align="center"><strong>Relationship</strong></div></td>
<td width="23%"><div align="center"><strong>Yrs Known
</strong></div></td>
</tr>
<tr>
<td><strong>1</strong></td>
<td>
<div align="center">
<input name="name" type="text" id="name"
value="John Doe" size="25">
</div></td>
<td>
<div align="center">
<input name="relationship" type="text"
id="relationship" value="Father" size="15">
</div></td>
<td>
<div align="center">
<input name="yearsknown" type="text"
id="yearsknown" value="39" size="8">
</div></td>
</tr>
<tr>
<td><strong>2</strong></td>
<td><div align="center">
<input name="name" type="text" id="name"
value="Jane Doe" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" value="Mother" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text"
id="yearsknown" value="39" size="8">
</div></td>
</tr>
<tr>
<td><strong>3</strong></td>
<td><div align="center">
<input name="name" type="text" id="name"
value="Janet Doe" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" value="Sister" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text"
id="yearsknown" value="34" size="8">
</div></td>
</tr>
<tr>
<td><strong>4</strong></td>
<td><div align="center">
<input name="name" type="text" id="name"
value="Jane Thomas" size="25">
</div></td>
<td><div align="center">
<input name="relationship" type="text"
id="relationship" value="Aide" size="15">
</div></td>
<td><div align="center">
<input name="yearsknown" type="text"
id="yearsknown" value="19" size="8">
</div></td>
</tr>

So in other words, there are 25 rows of textboxes, with 3 columns of
textboxes. If there are 25 records pertaining to the RegNo, then all the
boxes will be filled with data.

My question is How do you break down the RS so the textboxes can be
populated?

Thanks,
Drew




Ray Costanzo said:
Okay, what you're trying to do is not an uncommon thing. That does not
mean that it is a trivial task, however. But, you're using Dreamweaver.
If you continue to use Dreamweaver for ASP editing, then it really
becomes a question of how to use Dreamweaver to create this
functionality. I'd imagine that it has the ability to do this, although
you won't really find too many Dreamweaver users around here.

If you choose to do ASP development by hand-coding (at least the ASP
part), then it's a matter of determining what needs to appear on your
page, how you get it to appear on your page, what data needs to be sent
to the server, how to ensure that data is sent to the server, and then
what to do with that data when it is sent to the server.

Do you want the ability to edit all the records at the same time? That's
a little more complex than one at a time, so for the time being, I'll
assume that you only want to do one at a time. If you want to do all at
a time, that's fine; just post back.

For the one at a time method, I'll break it out into multiple pages for
simplicity's sake. The process here is:

showall.asp:
Create a page that lists records and provides a link to an edit page,
which will display the records with the editable fields in text boxes.
It will pass the UID in the querystring to edit.asp.

edit.asp:
Pull the id from the querystring, and get the corresponding record in the
database from it. Then display current values in a form with the values
being displayed in textboxes. The ID will be passed to the next page in
a hidden input.

save.asp:
Get the data posted from the form and execute an UPDATE [non]query with
that information using a WHERE clause containing the UID that was passed
in the hidden form field on edit.asp.




showall.asp:
<%
sSQL = "SELECT UID,RegNo,[Name],Relationship,YearsKnown FROM ELPGeneral"
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
Set oRS = yourADOObject.Execute(sSQL)
If oRS.EOF Then
Response.Write "No items.
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
End If
%>

<table>
<%
''.GetRows is for another day!
Do While Not oRS.EOF
%>
<tr>
<td><a
href="edit.asp?id=<%=oRS.Fields.Item(0).Value)%>"><%=oRS.Fields.Item(1).Value%></a></td>
<td><%=oRS.Fields.Item(2).Value%></td>
<td><%=oRS.Fields.Item(3).Value%></td>
<td><%=oRS.Fields.Item(4).Value%></td>
</tr>
<%
oRS.MoveNext
Loop
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
%>
</table>


edit.asp:
<%
Dim iID
iID = Request.Querystring("id") ''validate this
sSQL = "SELECT RegNo,[Name],Relationship,YearsKnown FROM ELPGeneral WHERE
UID=" & iID
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
Set oRS = yourADOObject.Execute(sSQL)
If oRS.EOF Then
Response.Write "Not found."
oRS.Close : Set oRS = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
End If
%>

<form method="post" action="save.asp">
Reg No: <input name="txtRegNo" type="text"
value="<%=oRS.Fields.Item(0).Value%>"><br>
Name: <input name="txtName" type="text"
value="<%=oRS.Fields.Item(1).Value%>"><br>
Relationship: <input name="txtRelationship" type="text"
value="<%=oRS.Fields.Item(2).Value%>"><br>
Years Known: <input name="txtYearsKnown" type="text"
value="<%=oRS.Fields.Item(3).Value%>"><br>
<input name="hidID" type="hidden" value="<%=iID%>">
<input type="submit">
</form>



save.asp:
<%
Dim iID, sRegNo, sName, sRelationship, iYearsKnown
Dim sSQL

iID = Request.Form("hidID")
sRegNo = Request.Form("txtRegNo")
sName = Request.Form("txtName")
sRelationship = Request.Form("txtRelationship")
iYearsKnown = Request.Form("txtYearsKnown")

sSQL = "UPDATE ELPGeneral SET RegNo=" & TextIn(sRegNo) & ",[Name]=" &
TextIn(sName) & ",Relationship=" & TextIn(sRelationship) & ",YearsKnown="
& iYearsKnown & " WHERE UID=" & iID
Set yourADOObject = CreateObject("ADODB.Connection")
yourADOObject.Open "your connection string here"
yourADOObject.Execute sSQL,,129
yourADOObject.Close
Set yourADOObject = Nothing

Response.Write "Saved."

Function TextIn(s)
TextIn = "'" & Replace(s, "'", "''")
End Function
%>

Ray at home


Drew said:
Here is my code for the page, cliELP_step02.asp,

Now I need the results from the recordset, rsPeopleWhoContributed to be
broken down and populated in the textboxes below, named name,
relationship, yearsknown. So the data could look like this,

UID RegNo Name Relationship YearsKnown
1 01292 John Doe Father 39
2 01292 Jane Doe Mother 39
3 01292 Janet Doe Sister 34
4 08982 Dr. Mosely Doctor 15
5 08982 Jack Smith Aide 14
6 01292 Jane Thomas Aide 19

So if the user selected RegNo 01292 on cliELP_step01.asp, and then
clicked Goto Step 2, then it would query the db using RegNo 01292 and
that recordset needs to be populated into the textboxes. So textbox
name 1 would have John Doe, and relationship txtbox 1 would have Father
and textbox yearsknown 1 would have 39. So on and so on.

TIA,
Drew

Code below,

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/CliELP.asp" -->
<%
'Insert data from cliELP_step01.asp
If Request.Form("actions") <> 1 Then

'SQL for inserting into SQL Server
sql="INSERT INTO ELPGeneral (RegNo,DesiredName)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("RegNo") & "',"
sql=sql & "'" & Request.Form("DesiredName") & "')"

'execute the insert to SQL Server
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_CliELP_STRING
MM_editCmd.CommandText = sql
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Download the JAVA , .NET and SQL Server interview with answers 0

Members online

Total: 30 (members: 1, guests: 29)
Robots: 615

Forum statistics

Threads
473,769
Messages
2,569,582
Members
45,057
Latest member
KetoBeezACVGummies

Latest Threads

Top