Database String... whats wrong?

[neosnokia]

Hi all,

I am trying to make an SQL statement which inserts values in to a database. So far, i have typed this in but it doesnt work and Netbeans gives a red error line underneath the line which reads ResultSet theResult=theStatement.executeQuery("INSERT INTO etc etc

    public void inserttoDb ()
    {
        try
        {
          //Directory may need to be changed for differnt computer
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          String myDB = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ= F:/JDB/sicksys.mdb";
          theConnection = DriverManager.getConnection(myDB);
          Statement theStatement=theConnection.createStatement();
          ResultSet theResult=theStatement.executeQuery("INSERT INTO Table1 (list_1,DayOff,MonthOff,YearOff,DayBack,MonthBack,YearBack VALUES("+"'"+ list_1 +"'"+ DayOff +""+"'"+ MonthOff +""+"'"+ YearOff +""+"'"+ DayBack+""+"'"+ MonthBack +""+"'"+ YearBack +")));

        }

        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }

Can anyone see anything majorly wrong with this?

[neilcoffey]

I'm sure Netbeans also TELLS you WHY it's put a red line under it, but my guesses would be either you've got your quotes muddled or you haven't imported a class.

[neosnokia]

Yes Netbeans does say "unclosed string literal" which means nothing to me as this is the first time i have done something with Java.

[neilcoffey]

Aha! OK, what it's telling you (albeit in a fundamentally stupid way) is that you've got your quotes muddled.

So you need to go very carefully through your string. Each time you want a particular piece of text added verbatim to your query, surround that text in exactly one quote either side. When/if you want an actual quote character in your string, you write either a double quote, or \". (In many variants of SQL, you actually usually use single quotes, so maybe this doesn't apply.)

It might help you to write your code more like this:

String query = "SELECT Blah FROM Bluu WHERE Bling = ";
query += "'" + theValueToCheck + "'";
query += " AND Status = 1";

rather than as one big long confusing line.

[neosnokia]

i dont get what you mean :-( I am trying to insert values form a few variables :
-list_1
-DayOff
-MonthOff
-YearOff
-DayBack
-MonthBack
-YearBack

[mtyoung]

ResultSet theResult = theStatement.executeQuery("INSERT INTO Table1 (list_1,DayOff,MonthOff,YearOff,DayBack,MonthBack,YearBack [COLOR="Red"])[/COLOR]VALUES(" + "'" + list_1 + "'" + DayOff + "[COLOR="Red"]'[/COLOR]" + "'"+ MonthOff +""+"'"+ YearOff +""+"'"+ DayBack+""+"'"+ MonthBack +""+"'"+ YearBack +")));

you missed some ")", ",", "'" in the sql
please check it

[masijade]

See your thread in the database forum.

Please don't cross-post like this.

[vrk]

hey,
u r inserting values into db,
so...u need to put st.executeUpdate("INSERT......."); instead of executeQuery();.....this is for SELECT stmt's ok.
now u can chk for values getting inserted r nt.

[masijade]

That is also a problem, yes, but the syntax is what caused the current problem. This would have been the next. ;-)

[Tolls]

String query = "SELECT Blah FROM Bluu WHERE Bling = ";
query += "'" + theValueToCheck + "'";
query += " AND Status = 1";

To extend this, since we may as well get you on the correct path from the start, you should be using a PreparedStatement, which allows you to declare placeholders for the values you're inserting/selecting on...so the above becomes:

String query = "SELECT Blah FROM Bluu WHERE Bling = ? AND Status = 1";

The question mark is the placeholder.
To use this you would do:

PreparedStatement ps = theConnection.prepareStatement();
ps.setString(1,theValueToCheck); // This sets the first '?' in your query to the value of theValueToCheck.
// Then get the results.
ResultSet theResult = ps.executeQuery();

[RamyaSivakanth]

Hi,
Use PreparedStatement as suggested by Toll.If you just build query with quotes and n number of values,surely u will end with error.That too preparedStatement are good in performance.

Gothru the attached piece of code and execute and also gothru article related to PreparedStatement.

 public void inserttoDb ()
    {
        try
        {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          String myDB = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ= F:/JDB/sicksys.mdb";
          theConnection = DriverManager.getConnection(myDB);
		  StringBuffer insertQueryBuffer = new StringBuffer();
		  insertQueryBuffer.append("INSERT INTO TABLE1(LIST_1,DAYOFF,MONTHOFF,YEAROFF,DAYBACK, "); 
		  insertQueryBuffer.append("MONTHBACK,YEARBACK VALUES(?,?,?,?,?,?,?)");	
          //Directory may need to be changed for differnt computer
          PreparedStatement theStatement=theConnection.prepareStatememt(insertQueryBuffer.toString());
		  theStatement(1,list_1);
	  	  theStatement(2,DayOff);
		  theStatement(3,MonthOff);
		  theStatement(4,YearOff);
		  theStatement(5,DayBack);
		  theStatement(6,MonthBack);
		  theStatement(7,YearBack);
          int insertCount=theStatement.executeUpdate();
		  System.out.println("InsertCount  "+ insertCount);
         }

        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }