Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
248 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
[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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

An overview of cyber security, cyber crime, and personal protection against hackers. Includes a brief summary of the Equifax breach and why everyone should be aware of it. Other subjects include: how cyber security has failed to advance with technol…
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

618 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