Maintaining Database Tables using Web Pages

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

I want to write out the SQL and use jdbc

Information on both options would be appreciated.

what database?

here are some jdbc basics:
http://java.sun.com/docs/books/tutorial/jdbc/basics/
http://java.sun.com/docs/books/tutorial/jdbc/
http://www.jspin.com/home/tutorials/database
http://ugweb.cs.ualberta.ca/~c391/tutorial/jdbcbasics.html#SEC5
http://www.developer.com/java/data/article.php/611591

CJ

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

I'm on MS SQL Server 7

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)

read this:
http://www.jspin.com/home/tutorials/database/editreco

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

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-)

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>

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>

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?

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)

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

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

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

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

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

stating that 'data' is undefined  :-S

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

stating that 'data' is undefined  :-S

this happens when i select a radio button of a record from the table

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

Error on
data[d][j] = tb.rows(getRowIndex(cell)).cells(j).innerText;

tb is null or not an object

SOrry scrap the above, the error is on

tb=document.getElementById("db_table");

'Object expected'

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

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!

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?

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