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
241 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

16 Experts available now in Live!

Get 1:1 Help Now