can not save(insert) data to MS SQL in JSP

[Jason]

Hi, I have a simple qustion about JSP and MS SQL server,

String sSQLString = "INSERT INTO testDB(EmployeeID,EmployeeName) ";

sSQLString = sSQLString + "VALUES('" + txtemployeeID + "','" + txtemployeeName +
"')";

then in the somewhere of my jsp,

<% try {

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
connection = DriverManager.getConnection(connectionURL);
statement = connection.createStatement();
rs = statement.executeQuery(sSQLString);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (rs != null) rs.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} %>

then after that JSP page executed, there is no any error message showed up, but
after I opened "query analyzer" from MS SQL, the data I just typed did not
save(insert) into database, but if I execute that query directly, it's works.

So, is there someting wrong on my code?

Thanks in advance.
Jason

 

[Axel Seinsche]

Hi, I have a simple qustion about JSP and MS SQL server,

String sSQLString = "INSERT INTO testDB(EmployeeID,EmployeeName) ";

sSQLString = sSQLString + "VALUES('" + txtemployeeID + "','" + txtemployeeName +
"')";

then in the somewhere of my jsp,

<% try {

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
connection = DriverManager.getConnection(connectionURL);
statement = connection.createStatement();
rs = statement.executeQuery(sSQLString);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (rs != null) rs.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} %>

then after that JSP page executed, there is no any error message showed up, but
after I opened "query analyzer" from MS SQL, the data I just typed did not
save(insert) into database, but if I execute that query directly, it's works.

So, is there someting wrong on my code?

Thanks in advance.
Jason

I am not sure, but does your jdbc Driver support AutoCommit? If not, you
should call commit() do write the changes to the DB.

 

[Ryan Stewart]

Hi, I have a simple qustion about JSP and MS SQL server,

String sSQLString = "INSERT INTO testDB(EmployeeID,EmployeeName) ";

sSQLString = sSQLString + "VALUES('" + txtemployeeID + "','" + txtemployeeName +
"')";

then in the somewhere of my jsp,

<% try {

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
connection = DriverManager.getConnection(connectionURL);
statement = connection.createStatement();
rs = statement.executeQuery(sSQLString);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (rs != null) rs.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} %>

then after that JSP page executed, there is no any error message showed up, but
after I opened "query analyzer" from MS SQL, the data I just typed did not
save(insert) into database, but if I execute that query directly, it's works.

So, is there someting wrong on my code?

Thanks in advance.
Jason

How do you know an exception isn't being thrown? The printStackTrace method
(with no arguments) prints to stdout. Where are you looking for your stack
trace? It's possible that this line:
rs = statement.executeQuery(sSQLString);

is throwing an exception. Your SQL String is not a query, so why would you
use executeQuery()? Only some implementations seem to throw exceptions on
that, though the docs for Statement state:
"Throws:
SQLException - if a database access error occurs or the given SQL statement
produces anything other than a single ResultSet object"

An insert statement would not produce a single ResultSet object I think.
Most important to get your issue resolved is to find your output and tell us
what's really happening. Instead of doing "ex.printStackTrace()", try
rethrowing the exception ("throw ex;") so it gets displayed in the browser.
Or you could just not catch it in the first place.

 

[Sudsy]

Ryan Stewart wrote:

How do you know an exception isn't being thrown? The printStackTrace method
(with no arguments) prints to stdout. Where are you looking for your stack
trace? It's possible that this line:
rs = statement.executeQuery(sSQLString);

Ryan hits the nail on the head. OP should be performing an
executeUpdate(). That returns a count of the number of records
updated. Obviously you won't get a ResultSet back from an
insert. From our good friends the javadocs:

int executeUpdate(String sql)
Executes the given SQL statement, which may be an INSERT,
UPDATE, or DELETE statement or an SQL statement that returns nothing,
such as an SQL DDL statement.