Gin_a
asked on
JSP and MS SQL database (Urgent help needed)!
When i tried to insert my data into the jsp page, i was not unable to insert the data successfully.For example, I tried to paste a whole chunk of articles into a textarea and i got the error "Error: 414 Location: /" or when i tried to insert half the article, i was not able to insert successfully.. My datatype for the database is text.
ASKER
This is my method in my database class
"
public boolean addNews(String title, int day, int month, int year, String source, String content, String editor, String postedBy, String lastUpdatedBy)
{
System.out.println(".."+co ntent+">>" );
int row = 0;
try
{
Statement stmt = con.createStatement();
String sql = "Insert into News (Title, Day, Month, Year, Source, Content, Editor, Postedby, Lastupdatedby) values('"+title+"',"+day+" ,"+month+" ,"+year+", '"+source+ "','"+cont ent+"','"+ editor+"', '"+postedB y+"','"+la stUpdatedB y+"')";
row = stmt.executeUpdate(sql);
if(row == 1)
{
return true;
}
}//try
catch(SQLException e)
{
System.out.println(e);
}
return false;
}
"
i have a database class and a servlet class respectively
"
public boolean addNews(String title, int day, int month, int year, String source, String content, String editor, String postedBy, String lastUpdatedBy)
{
System.out.println(".."+co
int row = 0;
try
{
Statement stmt = con.createStatement();
String sql = "Insert into News (Title, Day, Month, Year, Source, Content, Editor, Postedby, Lastupdatedby) values('"+title+"',"+day+"
row = stmt.executeUpdate(sql);
if(row == 1)
{
return true;
}
}//try
catch(SQLException e)
{
System.out.println(e);
}
return false;
}
"
i have a database class and a servlet class respectively
Try this,
String sql = "Insert into News(.., content, ...) values(.., ?, ..)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(6, content);
pstmt.executeUpdate();
String sql = "Insert into News(.., content, ...) values(.., ?, ..)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(6, content);
pstmt.executeUpdate();
ASKER
sorry,i insert the wrong method
if update.. then how?
just now that method was insert data.. when i update..
public boolean updateNews(News n)
{
try
{
Statement stmt = con.createStatement();
String sql = "Update News set Title = '"+n.getTitle()+"', Day = "+n.getDay()+", Month = "+n.getMonth()+", Year = "+n.getYear()+", Source = '"+n.getSource()+"', Content = '"+n.getContent()+"', Editor = '"+n.getEditor()+"', Lastupdatedby = '"+n.getLastUpdatedBy()+"' where NewsID = "+n.getID();
int row = stmt.executeUpdate(sql);
if (row == 1)
{
return true;
}
}
catch(SQLException e)
{
e.printStackTrace();
}
return false;
}
if update.. then how?
just now that method was insert data.. when i update..
public boolean updateNews(News n)
{
try
{
Statement stmt = con.createStatement();
String sql = "Update News set Title = '"+n.getTitle()+"', Day = "+n.getDay()+", Month = "+n.getMonth()+", Year = "+n.getYear()+", Source = '"+n.getSource()+"', Content = '"+n.getContent()+"', Editor = '"+n.getEditor()+"', Lastupdatedby = '"+n.getLastUpdatedBy()+"'
int row = stmt.executeUpdate(sql);
if (row == 1)
{
return true;
}
}
catch(SQLException e)
{
e.printStackTrace();
}
return false;
}
Similar method.
String sql = "Update News set .., Content = ?, ...";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, n.getContent());
pstmt.executeUpdate();
String sql = "Update News set .., Content = ?, ...";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, n.getContent());
pstmt.executeUpdate();
ASKER
sorry,i insert the wrong method
if update.. then how?
just now that method was insert data.. when i update..
public boolean updateNews(News n)
{
try
{
Statement stmt = con.createStatement();
String sql = "Update News set Title = '"+n.getTitle()+"', Day = "+n.getDay()+", Month = "+n.getMonth()+", Year = "+n.getYear()+", Source = '"+n.getSource()+"', Content = '"+n.getContent()+"', Editor = '"+n.getEditor()+"', Lastupdatedby = '"+n.getLastUpdatedBy()+"' where NewsID = "+n.getID();
int row = stmt.executeUpdate(sql);
if (row == 1)
{
return true;
}
}
catch(SQLException e)
{
e.printStackTrace();
}
return false;
}
if update.. then how?
just now that method was insert data.. when i update..
public boolean updateNews(News n)
{
try
{
Statement stmt = con.createStatement();
String sql = "Update News set Title = '"+n.getTitle()+"', Day = "+n.getDay()+", Month = "+n.getMonth()+", Year = "+n.getYear()+", Source = '"+n.getSource()+"', Content = '"+n.getContent()+"', Editor = '"+n.getEditor()+"', Lastupdatedby = '"+n.getLastUpdatedBy()+"'
int row = stmt.executeUpdate(sql);
if (row == 1)
{
return true;
}
}
catch(SQLException e)
{
e.printStackTrace();
}
return false;
}
Then,
String sql = "Update News set .., Content = ?, ...";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, n.getContent());
pstmt.executeUpdate();
Does it work?
String sql = "Update News set .., Content = ?, ...";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, n.getContent());
pstmt.executeUpdate();
Does it work?
ASKER
still cannot work.
ASKER
does that mean i have to use another data type. one that allows the inserting of large information.
Still the same error?
---Error: 414
Where does it come from? db or web server?
---Error: 414
Where does it come from? db or web server?
Did you try CLOB datatype.
ASKER
does that mean i have to use another data type. one that allows the inserting of large information.
ASKER
when i lesser the information of my data, i was able to insert.. so i also not sure where the error is from.
ASKER
i use ms sql 2000. there is no clob data type
ASKER
I try to insert 1668 characters with spaces into the textarea and i managed to insert successfully however if more than that, i will not be able to insert into the database. Besides that, when there are ' in my articles, i am not able to insert as well..
It sounds like the problem may be with your driver. Which driver are you using?
ASKER
u mean the database driver?
Yes. Which database driver are you using?
ASKER
Class.forName("com.microso ft.jdbc.sq lserver.SQ LServerDri ver").newI nstance();
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As an expiriment, try using PreparedStatement and setString() to insert the large String instead of one long update statement. If that doesn't work, try setCharacterStream(). The simple change of binding your variables may make all the difference. I'll modify your update for you:
PreparedStatement pst = con.prepareStatement("Upda te News set Title = ?, Day = ?, Month = ?, Year = ?, Source = ?, Content = ?, Editor = ?, Lastupdateby = ? WHERE NewsID = ?");
pst.setString(1, n.getTitle());
pst.setInt(2, n.getDay());
pst.setInt(3, n.getMonth());
pst.setInt(4, n.getYear());
pst.setString(5, n.getSource());
pst.setString(6, n.getContent());
pst.setString(7, n.getEditor());
pst.setString(8, n.getLastUpdatedBy());
pst.setInt(9, n.getID());
int row = stmt.executeUpdate();
If that doesn't work, change the
pst.setString(6, n.getContent());
to:
Reader rdr = new StringReader(n.getContent( ));
pst.setCharacterStream(6, n.getContent(), n.getContent().length());
-------------------------- ---------- ---------- -------
BTW your datatype is text, right?
PreparedStatement pst = con.prepareStatement("Upda
pst.setString(1, n.getTitle());
pst.setInt(2, n.getDay());
pst.setInt(3, n.getMonth());
pst.setInt(4, n.getYear());
pst.setString(5, n.getSource());
pst.setString(6, n.getContent());
pst.setString(7, n.getEditor());
pst.setString(8, n.getLastUpdatedBy());
pst.setInt(9, n.getID());
int row = stmt.executeUpdate();
If that doesn't work, change the
pst.setString(6, n.getContent());
to:
Reader rdr = new StringReader(n.getContent(
pst.setCharacterStream(6, n.getContent(), n.getContent().length());
--------------------------
BTW your datatype is text, right?
good catch, kotan.
Gina, the .newInstance() call on your Class.forname is not necessary.
Gina, the .newInstance() call on your Class.forname is not necessary.
ASKER
i manage to insert the data already. however, if in the information, there are 's in between, i was not able to insert the information into the database. By the way, the data type is text.
My method will let you insert the ' characters as is. That's one of the main advantages to it.
ASKER
I solved all the problems already. Thank for all the help
Maybe the text got same special character: " or ' which give you the error.
Maybe you can try to use
PreparedStatement.setStrin
to insert text.