Solved

How to take form info, check a table, then write to another table or go back to form?

Posted on 2004-08-24
8
240 Views
Last Modified: 2010-04-01
Hi,

I have a form that has about 7 fields.  I am doing some error checking with javascript on this form.  Then a record is created on table1.  

Now I have a new requirement to take the form information and check if 4 of the field's information matches an existing record on table2.  
If the record matches, write the new record to table1.  
If the record does not match with table2, go back to the form and give the user a message saying the record does not match on table2.

Could someone help with the code syntax for doing this, and maybe a simple example?

Cheers
0
Comment
Question by:g118481
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:sompol_kiatkamolchai
ID: 11883985
Your problem can be done by using JSP and JDBC to insert/update/delete/select from database. I have some useful example for you to study and apply to your need.

http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_21081218.html#11711760

best luck,
0
 
LVL 1

Author Comment

by:g118481
ID: 11884166
Your suggestion is not what I asked for.

I know how to do a select statement to a database table.  
What I need is an example of using JSP to query the table, then return the finding to the action JSP page and based on that data, return to the form with a message to the user, or process and write the new record.
0
 
LVL 1

Expert Comment

by:TomBruser
ID: 11884571
You need to perform the following steps:

Create a Connection to your database

Create a PreparedStatement for checking your database for the existance of a certain record in Table2

Populate the PreparedStatement with fields retrieved from the user's form post

Execute the PreparedStatement to get a ResultSet

See if the ResultSet contains any records

If the ResultSet contains records, create a new Statement to insert a new record into Table1, optionally send a message to the user telling him/her that the data has been posted

If the ResultSet contains no records, generate the html for a form with a message and send it back to the user

Are there any of these steps that you do not already know how to do?
0
 
LVL 1

Author Comment

by:g118481
ID: 11885003
Tom,

I know how to do your 1-5 steps, but do not understand how to do steps 6 & 7:

6.  If the ResultSet contains records, create a new Statement to insert a new record into Table1, optionally send a message to the user telling him/her that the data has been posted

7.  If the ResultSet contains no records, generate the html for a form with a message and send it back to the user

Here is what what I have so far, for connecting to the table, and running the query for the record.  Just need steps 6 & 7.

<%
try {
  Class.forName("ibm.sql.DB2Driver");
  java.sql.Connection conn = java.sql.DriverManager.getConnection("JDBC:db2os390:DB2T");
  java.sql.Statement stat = conn.createStatement();
  java.sql.ResultSet cols = stat.executeQuery("SELECT * FROM TABLE2 WHERE  STAGE2_NAME='"+STAGE+"' ENVIRONMENT='"+ENV+"' SYSTEM='"+SYS+"' SUBSYSTEM='"+SUBSYS+"'");
 while(cols.next()) {
%>

/// I THINK THE IF STATEMENT SHOULD GO HERE.

<% } // close while loop %>

<%
cols.close();
stat.close();
conn.close();

} catch (SQLException sqle) { sqle.printStackTrace(); }
%>


0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Expert Comment

by:TomBruser
ID: 11885089
You shouldn't use concantenated strings as your query strings.  It's not very secure.  Use a PreparedStatement instead (it's also faster because your database gets to remember/cache the prepared statement for future use).  Replace the middle part of your code with this instead:

java.sql.PreparedStatement stat = conn.prepareStatement("SELECT * FROM TABLE2 WHERE  STAGE2_NAME=? ENVIRONMENT=? SYSTEM=? SUBSYSTEM=?);
stat.setString(1,STAGE);
stat.setString(2,ENV);
stat.setString(3,SYS);
stat.setString(4,SUBSYS);

ResultSet cols = stat.executeQuery();
if (cols.next()) {
// insert code here to inset a new record into your table

} else {
// insert code here to generate a form with error message

}

0
 
LVL 1

Author Comment

by:g118481
ID: 11885774
Tom,

I am getting several errors in trying to test your suggestion.
Would you mind incorporating your code within my code above, so it will run cleanly.
I think I am not joining them correctly.

Thank you.
0
 
LVL 1

Accepted Solution

by:
TomBruser earned 150 total points
ID: 11886097
<%
try {
  Class.forName("ibm.sql.DB2Driver");
  java.sql.Connection conn = java.sql.DriverManager.getConnection("JDBC:db2os390:DB2T");
java.sql.PreparedStatement stat = conn.prepareStatement("SELECT * FROM TABLE2 WHERE  STAGE2_NAME=? ENVIRONMENT=? SYSTEM=? SUBSYSTEM=?");
stat.setString(1,STAGE);
stat.setString(2,ENV);
stat.setString(3,SYS);
stat.setString(4,SUBSYS);

java.sql.ResultSet cols = stat.executeQuery();
if (cols.next()) {
// insert code here to insert a new record into your table

} else {
// insert code here to generate a form with error message

}
cols.close();
stat.close();
conn.close();

} catch (SQLException sqle) { sqle.printStackTrace(); }
%>
0
 
LVL 1

Author Comment

by:g118481
ID: 11886872
Tom,

Your suggestion is working for me.

Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
A Short Story about the Best File Recovery Software – Acronis True Image 2017
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now