insert
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Patrick_Geary
Posted On:   Monday, May 5, 2003 05:06 AM

Selections Hi I have set up a webpage to allow a user to enter in personal details and store them in a table called "personal_details".Each user that registers will receive a unique account id "aid" which is stored in personal_details. A user then gets the option to select 10 teams from drop down menus. I have set up a table called "teams" which stores all the team names and each team name has a unique id called "tno" associated with it. When a user selects a team i want to store the "aid" of the user and the "tno" of the team that they have selected in another table called "jtable" which contains only 2 fields:-aid and tno. I have successfully generated the code to enter the tno o   More>>
Selections

Hi


I have set up a webpage to allow a user to enter in personal details and store them in a table called "personal_details".Each user that registers will receive a unique account id "aid" which is stored in personal_details. A user then gets the option to select 10 teams from drop down menus. I have set up a table called "teams" which stores all the team names and each team name has a unique id called "tno" associated with it. When a user selects a team i want to store the "aid" of the user and the "tno" of the team that they have selected in another table called "jtable" which contains only 2 fields:-aid and tno. I have successfully generated the code to enter the tno of the teams selected in jtable but that was by manually assigning a value to "aid". I want the "aid" to be determined by whoever the user is. The error which comes up on the screen is:-

"org.apache.jasper.JasperException: [Microsoft][ODBC Driver Manager] Invalid cursor state"



I would be very grateful for any help you could give me


Thanks


Patrick




			

<%@ page language="java" contentType="text/html" import="java.sql.*,java.util.*,java.sql.Connection,java.sql.Statement,java.sql.DriverManager,java.sql.SQLException,java.sql.ResultSet,java.net.URL" %>






<% String prem_choices_1 = request.getParameter("prem_choices_1");%>
<% String prem_choices_2 = request.getParameter("prem_choices_2");%>
<% String prem_choices_3 = request.getParameter("prem_choices_3");%>
<% String div1_choices_1 = request.getParameter("div1_choices_1");%>
<% String div1_choices_2 = request.getParameter("div1_choices_2");%>
<% String div1_choices_3 = request.getParameter("div1_choices_3");%>
<% String division2_choices_1 = request.getParameter("division2_choices_1");%>
<% String division2_choices_2 = request.getParameter("division2_choices_2");%>
<% String scot_choices_1 = request.getParameter("scot_choices_1");%>
<% String scot_choices_2 = request.getParameter("scot_choices_2");%>
<% String team_name = request.getParameter("team_name");%>
<% String user_name = request.getParameter("username");%>






<% //load the MySQL JDBC Driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");



//open a connection to the "fantasyfootball" database
Connection con = DriverManager.getConnection("jdbc:odbc:fantasyfootball2","","");

//create a statement object for sending SQL queries
Statement stmt1 = con.createStatement();
Statement stmt2 = con.createStatement();
Statement stmt3 = con.createStatement();
Statement stmt4 = con.createStatement();
Statement stmt5 = con.createStatement();
Statement stmt6 = con.createStatement();
Statement stmt7 = con.createStatement();
Statement stmt8 = con.createStatement();
Statement stmt9 = con.createStatement();
Statement stmt10 = con.createStatement();
Statement stmt1a = con.createStatement();






ResultSet res1=stmt1.executeQuery("SELECT tno from teams where name ='"+prem_choices_1+"'");
ResultSet res1a=stmt1a.executeQuery("SELECT aid from personal_details where username='"+user_name+"'");
if(res1.next()){
int team_no1=res1.getInt("tno");
int aid_1=res1a.getInt("aid");
System.out.println(team_no1);
stmt1.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('"+aid_1+"'','"+team_no1+"')");
}

ResultSet res2=stmt2.executeQuery("SELECT tno FROM teams WHERE name='"+prem_choices_2+"'");
if(res2.next()){
int team_no2=res2.getInt("tno");
System.out.println(team_no2);
stmt2.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no2+"')");
stmt2.close();
}

ResultSet res3=stmt3.executeQuery("SELECT tno FROM teams WHERE name='"+prem_choices_3+"'");
if(res3.next()){
int team_no3=res3.getInt("tno");
System.out.println(team_no3);
stmt3.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no3+"')");
stmt3.close();
}

ResultSet res4=stmt4.executeQuery("SELECT tno FROM teams WHERE name='"+div1_choices_1+"'");
if(res4.next()){
int team_no4=res4.getInt("tno");
System.out.println(team_no4);
stmt4.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no4+"')");
stmt4.close();
}

ResultSet res5=stmt5.executeQuery("SELECT tno FROM teams WHERE name='"+div1_choices_2+"'");
if(res5.next()){
int team_no5=res5.getInt("tno");
System.out.println(team_no5);
stmt5.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no5+"')");
stmt5.close();
}

ResultSet res6=stmt6.executeQuery("SELECT tno FROM teams WHERE name='"+div1_choices_3+"'");
if(res6.next()){
int team_no6=res6.getInt("tno");
System.out.println(team_no6);
stmt6.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no6+"')");
stmt6.close();
}

ResultSet res7=stmt7.executeQuery("SELECT tno FROM teams WHERE name='"+division2_choices_1+"'");
if(res7.next()){
int team_no7=res7.getInt("tno");
System.out.println(team_no7);
stmt7.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no7+"')");
stmt7.close();
}

ResultSet res8=stmt8.executeQuery("SELECT tno FROM teams WHERE name='"+division2_choices_2+"'");
if(res8.next()){
int team_no8=res8.getInt("tno");
System.out.println(team_no8);
stmt8.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no8+"')");
stmt8.close();
}

ResultSet res9=stmt9.executeQuery("SELECT tno FROM teams WHERE name='"+scot_choices_1+"'");
if(res9.next()){
int team_no9=res9.getInt("tno");
System.out.println(team_no9);
stmt9.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no9+"')");
stmt9.close();
}

ResultSet res10=stmt10.executeQuery("SELECT tno FROM teams WHERE name='"+scot_choices_2+"'");
if(res10.next()){
int team_no10=res10.getInt("tno");
System.out.println(team_no10);
stmt10.executeUpdate("INSERT INTO jtable(aid,tno)VALUES('1','"+team_no10+"')");
stmt10.close();
}



//clean up
con.close(); %>





Your Selections Are As Follows



Team 1:
<%if(!request.getParameter("prem_choices_1").equals(""))
{out.print(request.getParameter("prem_choices_1"));}%>




Team 2:
<%if(!request.getParameter("prem_choices_2").equals(""))
{out.print(request.getParameter("prem_choices_2"));}%>




Team 3:
<%if(!request.getParameter("prem_choices_3").equals(""))
{out.print(request.getParameter("prem_choices_3"));}%>




Team 4:
<%if(!request.getParameter("div1_choices_1").equals(""))
{out.print(request.getParameter("div1_choices_1"));}%>




Team 5:
<%if(!request.getParameter("div1_choices_2").equals(""))
{out.print(request.getParameter("div1_choices_2"));}%>




Team 6:
<%if(!request.getParameter("div1_choices_3").equals(""))
{out.print(request.getParameter("div1_choices_3"));}%>




Team 7:
<%if(!request.getParameter("division2_choices_1").equals(""))
{out.print(request.getParameter("division2_choices_1"));}%>




Team 8:
<%if(!request.getParameter("division2_choices_2").equals(""))
{out.print(request.getParameter("division2_choices_2"));}%>




Team 9:
<%if(!request.getParameter("scot_choices_1").equals(""))
{out.print(request.getParameter("scot_choices_1"));}%>




Team 10:
<%if(!request.getParameter("scot_choices_2").equals(""))
{out.print(request.getParameter("scot_choices_2"));}%>











   <<Less

Re: insert

Posted By:   Benoit_Quintin  
Posted On:   Monday, May 5, 2003 02:32 PM

You're doing too much work with your statements before closing them... I think you need to close your resultset AND your statement before using it to execute the updates, which is probably why you're getting an "invalid cursor state".

If I might suggest a little style exercise, try removing the database calls from your JSPs... I know you can leave it like that, but I think it's _usually_ both bad style and bad design, besides, it's usually easier to unit-test and debug your code itf it's in a class and not within a JSP.

About | Sitemap | Contact