Editable Datagrid 1

[dpk136]

I have a lot of tables that are going to be used as lists. The other asp pages will access these tables to populate a listbox or combobox. However, the people that are going to need to change what is available in the list are not going to have access to the database. I need for them to be able to change these tables via ASP but i don't want to make a new asp page for every list (there are 23). Is there a way to make a datagrid that would dynamically go and get the structure of a table and then update into appropriate fields?

David Kuhn
------------------

http://www.davidpkuhn.com

 

[emozley]

I would loop through the recordset and for each row make a form.

Let's say I have a table with the following columns:

UserID
FirstName
Surname
Extension

<%
TBL.Open "SELECT UserID, FirstName, Surname, Extension FROM Users", DB
%>

<table>
<tr>
<td>First Name</td>
<td>Surname</td>
<td>Extension</td>
</tr>

<%
Do While Not TBL.EOF
%>

<tr>
<td><form method="post" action="updaterecord.asp">
<input type="text" name="FirstName" value="<% = TBL("FirstName") %>">
</td>

<td>
<input type="text" name="Surname" value="<% = TBL("Surname") %>">
</td>

<td>
<input type="text" name="Extension" value="<% = TBL("Extension") %>">
</td>

<td><input type="hidden" name="UserID" value="<% = TBL("UserID") %>">
<input type="submit" value="Update">
</form>
</td>
</tr>

<%
TBL.MoveNext
Loop
%>

</table>

<%
TBL.Close
Set TBL=Nothing
Set DB=Nothing
%>

 

[dpk136]

I know how to do this. I was saying what if i have more than 1 table and only want to use 1 script to process all of it.

Table Users
  UserName
  Password

Table MyTable
  PONum
  Address
  ZipCode
  State

Select * FROM %tablename%

I would want the page to look like this when Users is the table:

| UserName | Password |
|  -DATA - |  -DATA-  | EDIT BUTTON
|  -DATA - |  -DATA-  | EDIT BUTTON

I would want the page to look like this when MyTable is the table:

|  PONum   | Address  | ZipCode | State  |
|  -DATA - |  -DATA-  | -DATA-  | -DATA- | EDIT BUTTON
|  -DATA - |  -DATA-  | -DATA-  | -DATA- | EDIT BUTTON

These would both be able to be done on the same page...basically a totally dynamic way of editing the data for the whole database.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[DotNetGnat]

this can be easily done using a datagrid inside a repeater in ASP.NET...havent done this in classic ASP...

-DNG

 

[Sheco]

You could make each row its own HTML form or you could do an AJAX solution.

 

[QatQat]

Here is a solution using multidimensional arrays

<%
strSQL = "select * from table"
set RS = server.createobject("ADODB.recordset")
RS.open strSQL, conn, 3, 3

dim arRS
srRes = RS.getrows()

dim c  'looping variable for columns
dim r  ' looping variable for rows


'print table headers

for h = 0 to RS.fields.count - 1

%>
<TD><B><%=RS.Fields(iHead).Name%></b></TD>

<%
Next
%>
</TR>

<%

'loop for all rows
For r = 0 to uBound(arRes, 2)
%>

<TR>
<%
'loop for all columns
for c = 0 to uBound(arRes, 1)
for x = 0 to RS.fields.count - 1


'now display a textbox named dynamically using the name of the column and the numerical ID of the row
%>
<TD><input type="text" name="<%=RS.Fields(x).Name & arRS(0, r)%>" value="<%=arRS(c,r)%>"></TD>

<%
Next
x = x + 1
%>

</TR>
<%
Next
%>
</table>

<% end if %>

Now you can replace strSQL with any sql query and it will be displayed in a editable table.


if you note, my code names all textboxes dynamically using a combination of

column_name + rowID

This will allow you to uniquely identify a textbox when you collect updated values looping through all rows and updating relevat fields.


Cheers

QatQat



Life is what happens when you are making other plans.

 

[QatQat]

sorry there are a few typo in my posting here is a better version of that code

<%
strSQL = "select * from table"
set RS = server.createobject("ADODB.recordset")
RS.open strSQL, conn, 3, 3

dim arRS
arRS = RS.getrows()

dim c  'looping variable for columns
dim r  ' looping variable for rows


'print table headers

for h = 0 to RS.fields.count - 1

%>
<TD><B><%=RS.Fields(iHead).Name%></b></TD>

<%
Next
%>
</TR>

<%

'loop for all rows
For r = 0 to uBound(arRS, 2)
%>

<TR>
<%
'loop for all columns
for c = 0 to uBound(arRS, 1)
for x = 0 to RS.fields.count - 1


'now display a textbox named dynamically using the name of the column and the numerical ID of the row
%>
<TD><input type="text" name="<%=RS.Fields(x).Name & arRS(0, r)%>" value="<%=arRS(c,r)%>"></TD>

<%
Next
x = x + 1
%>

</TR>
<%
Next
%>
</table>

Better now.


Cheers

QatQat


Life is what happens when you are making other plans.

 

[Tarwn]

DataGrids don't do what your saying. With a DataGrid it knows it is going to be bound to a single table/view/sproc and you build all of the fields for editing and all of the code for submission.

The first problem with this method:
How does your table know what the primary key is?

Second problem:
Are you going to output textboxes for everything or will you have some mechanism for knowing that you need a dropdown for some values?

Third Problem:
How are you going to have users enter data for records that are foreign keys to other tables?

Fourth problem:
How does they system know when to hide certain fields? Or will everything be displayed and all of it in plain text? (think password fields, email, etc)

I was going to draw up an example of how you could do it, but I couldn't let myself. You couldn't do this all in one table editing page with a DataGrid without a great deal of code behind it. I don't think it's a good idea to do it in ASP 3.0 either.

Basically you will not want to do this unless you are doing a bunch of schema queries behind the scenes to help you determine field types, keys, relationships, etc. We build seperate database editing pages because the end user needs the context we give them, need the dropdowns, validation, hints, fields descriptions, etc. Otherwise tey woudl be doing your job and you would be off bulding the next phpMyAdmin.

-T

 

[QatQat]

Hi Tarwn,

obviosly this is a very quick fix, I am aware that there are proper (commercial) datagrid components available on the net.

My Method assumes that you can rely on a numericalID for every table as a first column, it assumes that you can modify without fear all records the same way and it assumes a number of other things, as well as having still an error which can be fixed (removing the second loop "for x = 0 to RS.fields.count - 1").

Addressing your points
point 1
you can use a sequential numbering to dynamically name the form controls

Point 2
if you want you can build logics to make sure that it does not display everything in a textbox.

point 3
see point 4

point 4
you can filter write a query that does not include password fields to prevent the script from displaying them.

Point 3 may be the most difficult to address if you really want to allow a user to edit fields that are foreign keys from a different table. The script above in fact get its data feed through one only query and therefore does not know what table foreign key values came from.


I did not say this is "the solution", I said here is "a solution using multidimensional arrays". it is quick and perhaps addresses the needs of dpk136.


Cheers

QatQat

Life is what happens when you are making other plans.

 

[dpk136]

Tarwn, i understand what you are saying...but you didn't read my post all the way. This editor will only edit tables that hold values for lists. so there will be no list lookup, only text boxes.

Also, Primary key will not matter since it is in numerical order and will be the first field in every table.

No fields will be hidden, this table will be in the administrative section and will not deal with passwords because, again, this will only be for lists.

What i am trying to create is an easy way to edit tables that just hold values for a list to lookup.

For instance, one table will be ShipmentType and will hold, in numerical order, UPS, DHL, FEDEX, SEA. This will be used as a dropdown on some of the forms.

Thanks QatQat for the answer. I guess i just looked to much into it and didn't look at what was right there in front of me. You get a star.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Tarwn]

QatQat: first, don't take my above posting as a critique of your post, I didn't see your post until just now so you probably posted after I started typing up my response and I didn't notice it after I submitted mine.

And in fact when I started to write up an example it worked similar to yours (except I used GetString instead of GetRows). I am just uneasy giving end users tools that don't hold their hand a little more. If this was a quick and dirty tool to use for the developers rather than having to deal with the database directly, then I would have fewer problems.

Oh, and as a general comment, not to QatQat, just because a lookup table may be used to populate a listbox doesn't mean that it can't have further foreign relations to child records in another table. I'm staring at a database diagram hanging from my cube wall that has a lookup table called lu_Category that has a further lookup table called lu_CalculationCategory. Basically many categories in that particular system can be grouped into a few defined calculation categories. But that lu_Category table is still a lookup table in my system. Lookup does not necesarally equate to standalone.

-T

 

[QatQat]

No offense taken Tarwn, I do not mind observations, and even critiques, if reasonable and properly formulated, that is why I took time to reply to your posting which I read with interest and attention.


Cheers




QatQat




Life is what happens when you are making other plans.

 

[a6m1n0]

This post might be useful in your endeavours. Read henna's second reply.

-a6m1n0

Curiosity only kills cats.