?
Solved

Maintaining Database Tables using Web Pages

Posted on 2003-03-10
28
Medium Priority
?
343 Views
Last Modified: 2010-04-01
Hi

I'm writing some web pages which are used to maintain tables in a database. These tables are used to hold fairly static information such as personUsername, personFullname, personID, or on another table deptCode, deptDescription.

I want to provide as much functionality as possible to save my end users from using Enterprise Manager and the likes to update the tables.

Initially these will provide basic functions to insert, delete, update records of the tables, and I hope to extend this later.

If anyone could provide information on this it would be greatly appreciated, e.g. links to examples where this has been done before.

Thanks
SnO2d
0
Comment
Question by:SnO2d
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 9
  • 3
28 Comments
 
LVL 19

Expert Comment

by:cheekycj
ID: 8103805
do you want to do this dynamically?  Like without having to code sql for each database or do you want to write the sql out and use jdbc?

CJ
0
 

Author Comment

by:SnO2d
ID: 8104121
I want to write out the SQL and use jdbc

Information on both options would be appreciated.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:fargo
ID: 8104311
Hii SnO2d,

What i can understood from ur question is, u want to have some sort of web admin.

The following i suggest.

1) get the connection to the database using the parameters like class, driver, user, password.

2) show a html page with a text area to write queries by the user.

3) have a submit button at the bottom..and set the action to the same page. Then read the content of the user submission.

String sStatement = request.getParameter ("statement");  
Statement stmnt = new Statement ();
stmnt.setSQLStatement (sStatement);
boolean bHasResultSet = sStatement.length () > 0 ? connectionMngr.execute (stmnt) : false;
if (bHasResultSet) {

use database metadata to get the column names and iterate thru the resultset to get the results from the execution of the query.
}

This is the one of the way to get this work done.
Hope this helps

Happy working
fargo
0
 

Author Comment

by:SnO2d
ID: 8104315
I'm on MS SQL Server 7
0
 

Author Comment

by:SnO2d
ID: 8104369
fargo

You hit the nail on the head - I need some kind of web admin page for the database.

BUT
I don't want my end users to have to go & learn SQL either!

At the minute I've written a page which when loaded, displays the entire contents of a chosen table.

Below the table I am working on providing functions to update, delete or add records.

When the user selects say update, I want the record to be broken up and displayed in text boxes which the user can then edit and press an 'Update' button which will execute an UPDATE SQL statement with those new entries for the record.
 - For this will I have to assign identifying tags to the table headers? The rows can be uniquely identified via an ID column.

(NB I will write Javascript functions to validate the inputs)

OR for ADD the user simply inputs the required values etc

Thanks for your help. I would post some code but its very rough and not complete! :o)
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8104382
0
 
LVL 11

Expert Comment

by:fargo
ID: 8106301
Hii SnO2d,

In my perspective, what u can do is make some editable table with the use of javascript and dhtml.. add the rows dynamically to the table (The table ur getting from database). Then user can write the values out in that and then in the backend use the java or jsp to get the string and insert into the database.You can make the table editable too, so that user can have the update option. Well, i don't know about what security measures u are taking. B'coz providing deleting feature could be a major loophole.

You can browse the internet for "editable tables"..and there you go.

Happy working
fargo

0
 

Author Comment

by:SnO2d
ID: 8109827
The delete feature only updates an 'active' field for each record, 0 is visible and 1 is hidden (deleted).

Also I have history tables where each transaction is sent to these, along with who made what changes and at what time/date.

Thanks for the responses, I'll read thru the links and assign the points accordingly.

B-)
0
 

Author Comment

by:SnO2d
ID: 8110667
The below code is an extract from my update page. When loaded, the entire table to be modified is displayed. The line
<td><input type="radio" name="up_tecselect" value='<%=rsSearch.getInt("tecId")%>' ></td>
adds a column to the table which consists of radio buttons, each with a value set to tecId from the table.

When a user wants to update a record, I want them to select the appropriate radio button from the record on the table, and the details of this record will be displayed in the text boxes below. Can someone provide information on how to do this. Will I have to give my table cells id's ? etc etc!

Many thanks
:o)

<%@page import="java.sql.*"
..open connection to database...%>


<html>

<head>
</head>

<title>Update page</title>

<body>
<FORM name="tbltecupdateform" method="GET" action="uamupdatedtbltec.jsp">
<%

String [] mArFields = {""};
String [] mArHeaderNames = {""};
String mSQL = "";


ResultSet rs = stat.executeQuery( "select * from tblTec " );


{
mArFields = new String [] { "tecId", "tecUserName", "tecFullName", "tecDeleteIndicator" };
mArHeaderNames = new String [] { "Id", "Userame", "Full Name",  "Active" };
mSQL = "select * from tblTec";
out.println("Technician Table");
}

ResultSet rsSearch = stat.executeQuery( mSQL );


%>
<br>
<br>

<table bgcolor="lightblue" border="1">
 <tr>
   <td></td>
     <%     for( int i=0; i < mArHeaderNames.length; i++ )
        { %>
   
         
     <td><% out.print(mArHeaderNames[i]); %></td>
     
     <%     } %></tr><%
   
     while ( rsSearch.next() )
    {
     %><tr>
     
     <td><input type="radio" name="up_tecselect" value='<%=rsSearch.getInt("tecId")%>' ></td>
         
    <% for ( int ii = 0; ii < mArFields.length; ii++ )
        {%>
       
       
        <td>
     <% try {
         if (rsSearch.getString(mArFields[ii]) == null)
            out.print ( "NULL" );
               else
            out.print( rsSearch.getString(mArFields[ii]) );
         }
         
         catch (Exception ee)
         { out.print(" "); }
               %></td><%
         }
       
           %></tr>

 <% } %>
 </table>
<br>

<input type="radio" value="upd_tecselect" name="modify_tec" >Update a Record<br>
<input type="radio" value="add_tecselect" name="modify_tec">Add a Record<br>
<input type="radio" value="del_tecselect" name="modify_tec">Delete a Record<br>
<br>
<br>

<b>Update Technicians Details</b><br>
ID <input type=text name="up_tecId" size="3" maxlength="3" value=""><br>
Username <input type=text name="up_tecUserName" size="8" maxlength="12" value=""><br>
FullName <input type=text name="up_tecFullName" size="18" maxlength="50" value=""><br>
Active <select name="up_active" >
<option value="0">Yes</option>
<option value="1">No</option>
</select><br><br>
<input type ="submit" value ="Update">
<input type ="reset" value ="Reset">

<br>
<br>

</form>
<% rsSearch.close(); stat.close(); conn.close(); %>
</body>

</html>
0
 
LVL 11

Expert Comment

by:fargo
ID: 8110839
Hello SnO2d,

The following is the one possible way to do it...i just make some rough code...i didn't test it...but, with small alterations this should work.

Moreover, for "25" points i took more than worth pain...Hope u will increase the points.

Take a look...

<html>

<head>
<script>

function use(cell){
 tb=document.getElementById("db_table");
 for(var d=0; d<1; d++){
  for(var j=0; j<4; j++){
     data[d][j] = tb.rows(getRowIndex(cell)).cells(j).innerText;
  }//end of assigning values to data
}

// the following is the way to fill the data to the form
up_tecId = document.all['up_tecId'];
up_tecId.value = data[][]
// like this do for other form elements...
// assign ids to the form elements like the name

}

function getRowIndex (cell) {
  return document.all ? cell.parentElement.rowIndex : cell.parentNode.rowIndex;
}



</script>
</head>

<title>Update page</title>

<body>
<FORM name="tbltecupdateform" method="GET" action="uamupdatedtbltec.jsp">
<%

String [] mArFields = {""};
String [] mArHeaderNames = {""};
String mSQL = "";


ResultSet rs = stat.executeQuery( "select * from tblTec " );


{
mArFields = new String [] { "tecId", "tecUserName", "tecFullName", "tecDeleteIndicator" };
mArHeaderNames = new String [] { "Id", "Userame", "Full Name",  "Active" };
mSQL = "select * from tblTec";
out.println("Technician Table");
}

ResultSet rsSearch = stat.executeQuery( mSQL );


%>
<br>
<br>

<table id="db_table" bgcolor="lightblue" border="1">
<tr>
  <td></td>
    <%     for( int i=0; i < mArHeaderNames.length; i++ )
       { %>
   
         
    <td><% out.print(mArHeaderNames[i]); %></td>
   
    <%     } %></tr><%
   
    while ( rsSearch.next() )
   {
    %><tr>
   
    <td><input type="radio" name="up_tecselect" value='<%=rsSearch.getInt("tecId")%>' onclick="use(this)"></td>
         
   <% for ( int ii = 0; ii < mArFields.length; ii++ )
       {%>
       
       
       <td>
    <% try {
        if (rsSearch.getString(mArFields[ii]) == null)
           out.print ( "NULL" );
              else
           out.print( rsSearch.getString(mArFields[ii]) );
        }
       
        catch (Exception ee)
        { out.print(" "); }
              %></td><%
        }
       
          %></tr>

<% } %>
</table>
<br>

<input type="radio" value="upd_tecselect" name="modify_tec" >Update a Record<br>
<input type="radio" value="add_tecselect" name="modify_tec">Add a Record<br>
<input type="radio" value="del_tecselect" name="modify_tec">Delete a Record<br>
<br>
<br>

<b>Update Technicians Details</b><br>
<form name="db_form" id="db_form" action="">
ID <input type=text name="up_tecId" size="3" maxlength="3" value=""><br>
Username <input type=text name="up_tecUserName" size="8" maxlength="12" value=""><br>
FullName <input type=text name="up_tecFullName" size="18" maxlength="50" value=""><br>
Active <select name="up_active" >
<option value="0">Yes</option>
<option value="1">No</option>
</select><br><br>
<input type ="submit" value ="Update">
<input type ="reset" value ="Reset">
</form>

<br>
<br>

</form>
<% rsSearch.close(); stat.close(); conn.close(); %>
</body>

</html>



0
 

Author Comment

by:SnO2d
ID: 8111308
Thanks for the reply fargo

Just a couple of things:

1. You have used nested forms in the above answer. Can I get away with doing this? - form name="db_form"

2. For the radio buttons such as:
ID <input type=text name="up_tecId" size="3" maxlength="3" value=""><br>
Should these have a value associated with them or will they be assigned when the table button is selected?

3. In the java script, you have

// the following is the way to fill the data to the form
up_tecId = document.all['up_tecId'];
up_tecId.value = data[][]
// like this do for other form elements...
// assign ids to the form elements like the name

For the other form elements, do I have to add something like:
up_tecFullName = document.all['up_tecFullName'];
up_tecFullName.value = data[][]

Thanks again
:o)

Have set the points to 100 - is this enough?
0
 

Author Comment

by:SnO2d
ID: 8111595
fargo

I'm having trouble with the following lines

up_tecId.value = data[][]
Syntax error

<td><input type="radio" name="up_tecselect" value='<%=rsSearch.getInt("tecId")%>' onclick="use(this)"></td>
Object expected

Can you take a look at this please ;o)
0
 
LVL 11

Expert Comment

by:fargo
ID: 8111905
1. You have used nested forms in the above answer. Can I get away with doing this? - form name="db_form"
----------> yes...u can get away with the nested one.

2. For the radio buttons such as:
ID <input type=text name="up_tecId" size="3" maxlength="3" value=""><br>
Should these have a value associated with them or will they be assigned when the table button is selected?
-----> this should be assigned when the radio button is selected..otherwise how u will know which record is associated with which id??

3. In the java script, you have

// the following is the way to fill the data to the form
up_tecId = document.all['up_tecId'];
up_tecId.value = data[][]
// like this do for other form elements...
// assign ids to the form elements like the name

For the other form elements, do I have to add something like:
up_tecFullName = document.all['up_tecFullName'];
up_tecFullName.value = data[][]
---> yeah u need to do this for the other elements also..and in data[][]...u need to assign which data element u are assigning to which element...it should not be data[][](this definitely leads to syntax error)..but indexes.

<td><input type="radio" name="up_tecselect" value='<%=rsSearch.getInt("tecId")%>' onclick="use(this)"></td>
Object expected
---> take out the onclick from the input and put it in the td element of table.

Hope this helps.
fargo


0
 

Author Comment

by:SnO2d
ID: 8112016
Can you be clearer on this point :

---> yeah u need to do this for the other elements also..and in data[][]...u need to assign which data element u are assigning to which element...it should not be data[][](this definitely leads to syntax error)..but indexes.

At the minute I have
up_tecId = document.all['up_tecId'];
up_tecId.value = data[][]
up_tecUserName = document.all['up_tecUserName'];
up_tecUserName.value = data[][]
up_tecFullName = document.all['up_tecFullName'];
up_tecFullName.value = data[][]

What should be in [][] ?

Thanks
0
 
LVL 11

Expert Comment

by:fargo
ID: 8112067
function use(cell){
tb=document.getElementById("db_table");
for(var d=0; d<1; d++){
 for(var j=0; j<2; j++){
    data[d][j] = tb.rows(getRowIndex(cell)).cells(j).innerText;
 }//end of assigning values to data
}

// the following is the way to fill the data to the form
up_tecId = document.all['up_tecId'];
up_tecId.value = data[0][0]
up_tecUserName = document.all['up_tecUserName'];
up_tecUserName.value = data[0][1]
up_tecFullName = document.all['up_tecFullName'];
up_tecFullName.value = data[0][2]

}


if you have only 3 elments to fill up..then the loop for j will go upto 3. And then u will assign the values of data[][] array to the form elements.

Hope this helps.
It is not an optimised way...but this should work for you.

happy working
fargo
0
 

Author Comment

by:SnO2d
ID: 8112070
If it helps here is what the output table is like:

  Id Userame   Full Name Active
+ 1  username1 Fullname1 0
+ 2  username2 Fullname2 0
 
(the +'s are radio buttons)
etc
0
 

Author Comment

by:SnO2d
ID: 8112108
I am getting the error on line
data[d][j] = tb.rows(getRowIndex(cell)).cells(j).innerText;

stating that 'data' is undefined  :-S
0
 

Author Comment

by:SnO2d
ID: 8112127
I am getting the error on line
data[d][j] = tb.rows(getRowIndex(cell)).cells(j).innerText;

stating that 'data' is undefined  :-S
0
 

Author Comment

by:SnO2d
ID: 8112132
this happens when i select a radio button of a record from the table
0
 
LVL 11

Expert Comment

by:fargo
ID: 8114443
well..as in ur case..it's only the for a single row update...u can make use of single dimensional array and have them initialise like

 var data = new Array(3);

hope this will solve ur problem.
happy working
fargo
0
 

Author Comment

by:SnO2d
ID: 8118015
Error on
data[d][j] = tb.rows(getRowIndex(cell)).cells(j).innerText;

tb is null or not an object
0
 

Author Comment

by:SnO2d
ID: 8118224
SOrry scrap the above, the error is on

tb=document.getElementById("db_table");

'Object expected'
0
 
LVL 11

Expert Comment

by:fargo
ID: 8118458
hii,

var tb = document.getElementById("db_table");

this must not be a problem...have u assigned the id as db_table or not??? u can try..

document.all['db_table'];

hw
fargo



0
 
LVL 11

Accepted Solution

by:
fargo earned 400 total points
ID: 8118791
Hii SnO2d,

I just did a sample to do ur things...this is the basic one to have ur things done...try it..and make some modifications for ur structure..

<html>

<head>
<script>

function use(cell){
frm = document.getElementById("tbltecupdateform");

 for(var j=1; j<4; j++){
     frm.elements[j+3].value = frm.elements[j].value;
}//end of assigning values to data

}

function getRowIndex (cell) {
 return document.all ? cell.parentElement.rowIndex : cell.parentNode.rowIndex;
}



</script>
</head>

<title>Update page</title>

<body>
<FORM name="tbltecupdateform" method="GET" action="uamupdatedtbltec.jsp">
<table id="db_table" name="db_table">
<tr>
<td>&nbsp;&nbsp;</td>
<td>Id</td>
<td>UserName</td>
<td>Full Name</td>
</tr>
<tr>

  <td onclick="use(this)"><input type="radio" name="tec_radio"></td>
   <td><input type="text" name="tecsId" value="1"></td>
  <td><input type="text" name="tecusername" value="Fargo" ></td>
  <td><input type="text" name="tecfullname" value="Fargo Webby" ></td>
</tr>
</table>
<br><br>


<b>Update Technicians Details</b><br>
ID <input type=text name="up_tecId" size="3" maxlength="3" value=""><br>
Username <input type=text name="up_tecusername" size="8" maxlength="12" value=""><br>
FullName <input type=text name="up_tecfullname" size="18" maxlength="50" value=""><br>
<br><br>
<input type ="submit" value ="Update">
<input type ="reset" value ="Reset">
</form>

<br>
<br>
</body>

</html>


happy working
fargo
0
 

Author Comment

by:SnO2d
ID: 8121063
Thanks for sticking with me on this one, it does exactly what I want! :o)
Just have to edit it a little to suit my situation now, I'll keep you updated!
0
 

Author Comment

by:SnO2d
ID: 8126488
Fargo

This solution is not adequate. I know I have assigned the points already, but could you take a look please?

You have the line:

    frm.elements[j+3].value = frm.elements[j].value;

My table is built out dynamically. Therefore using a fixed value of j+3 simply wont do! At the minute, I have:

frm.elements[j+53].value = frm.elements[j].value

As I have about 10 records. Instead of j+3 it should be j+some variable.

Also, whenever I click on any radio button beside the table, its only ever record 1 gets copied down to the text boxes below?
0
 
LVL 11

Expert Comment

by:fargo
ID: 8130498
Hii SnO2d,

Take a look at this..

this will solve ur problem...i have divided ur form into two..so that u can have any dynamic stuff in first table...

<html>

<head>
<script>

function use(cell){
frm = document.getElementById("tbltecupdateform");
newfrm = document.getElementById("updateform");

for(var j=1; j<4; j++){
    newfrm.elements[j-1].value = frm.elements[j].value;
}//end of assigning values to data

}

function getRowIndex (cell) {
return document.all ? cell.parentElement.rowIndex : cell.parentNode.rowIndex;
}



</script>
</head>

<title>Update page</title>

<body>
<FORM name="tbltecupdateform" method="GET" action="uamupdatedtbltec.jsp">
<table id="db_table" name="db_table">
<tr>
<td>&nbsp;&nbsp;</td>
<td>Id</td>
<td>UserName</td>
<td>Full Name</td>
</tr>
<tr>

 <td onclick="use(this)"><input type="radio" name="tec_radio"></td>
  <td><input type="text" name="tecsId" value="1"></td>
 <td><input type="text" name="tecusername" value="Fargo" ></td>
 <td><input type="text" name="tecfullname" value="Fargo Webby" ></td>
</tr>
</table>
<br><br>
</form>

<form name="updateform" method="post" action="uamupdatedtbltec.jsp">
<b>Update Technicians Details</b><br>
ID <input type=text name="up_tecId" size="3" maxlength="3" value=""><br>
Username <input type=text name="up_tecusername" size="8" maxlength="12" value=""><br>
FullName <input type=text name="up_tecfullname" size="18" maxlength="50" value=""><br>
<br><br>
<input type ="submit" value ="Update">
<input type ="reset" value ="Reset">
</form>

<br>
<br>
</body>

</html>

happy working....
fargo
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
The Summer 2017 Scholarship Winners have been announced!
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month12 days, 18 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question