I am writing a java program which connects with Ms Access using ODBC-JDBC Driver. The Select and Insert Into Statement are working correctly but delete and update statement are showing syntax error. I have tried all possible solutions but nothing seems to work. Can someone suggest what is wrong with my syntax?
import java.sql.*;
public class Lab{
public static void main(String[] args) {
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn=DriverManager.getConnection("jdbc:odbc:Test");
Statement st=conn.createStatement();
String sql="SELECT * FROM Playlist ";
ResultSet rs=st.executeQuery(sql);
while(rs.next()){
System.out.println("\n "+rs.getString("S No")+"\t"+rs.getString("Song name")+"\t"+rs.getString("Album")+"\t"+rs.getString("Singer"));
}
st.executeUpdate("INSERT INTO Playlist"
+ " VALUES (6, 'Nebraa', 'WesteStars', 'Bre')");
sql = "update Playlist "
+ " set Song name='Nebula' "
+ " where S No=6";
st.executeUpdate(sql);
sql = "DELETE FROM Playlist " +
"WHERE S No = 6 ";
st.executeUpdate(sql);
conn.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
}
}
Field names Song Name
and S No
have space so enclose in [ ]
delimiters - [Song Name]
and [S No]
. Advise not to use spaces nor punctuation/special characters in database naming convention.