Results 1 to 11 of 11
  1. #1
    Rose88 is offline Member
    Join Date
    Apr 2009
    Posts
    19
    Rep Power
    0

    Default problem with jdbc (sql 2000)

    Hey everybody,

    my problem is when I insert the date it gives me this error!


    Java Code:
    DBconn.s.execute("INSERT INTO tblDoctor VALUES ('"+LicenseNum+"','"+FirstName+"'" +
    					",'"+surName+"','"+Specialization+"','"+StartWorkingDate+"' " );
    and the error I get is :
    Java Code:
    [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near ''.

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,641
    Rep Power
    7

    Default

    Specify the column values:
    INSERT INTO tblDoctor (col1,...coln) VALUES (...)

  3. #3
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,371
    Blog Entries
    1
    Rep Power
    20

    Default

    Syntax error, not a driver error at all.

    Most of the people mess-up this. Actually you can do this in two ways. Followings are the two forms.

    INSERT INTO tblname
    VALUES (val1, val2, val3, ...)
    Here you don't need to specify column names, only the values you want to insert. BUT in that case you've to provide all column values except auto indexing. Basically you must specify equal number of values to the number of columns.

    [QUOTE]INSERT INTO tblname (colu1, colu2, colu3, ...)
    VALUES (val1, val2, val3, ...)/QUOTE]

    This is more generic, you can insert values only you know at the moment. SQL engine insert default values to all other columns.

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Seemingly, one of those variable is an "empty" string, thereby leading to one of the "values" in the query being
    Java Code:
    ''
    . Use a PreparedStatement (do not ever cobble together your statements like this unless you want people to be performing SQL Injection attacks against your DB) and when one of the arguments is an empty string use setNull rather than setString (or whatever is appropriate for the field).

  5. #5
    Rose88 is offline Member
    Join Date
    Apr 2009
    Posts
    19
    Rep Power
    0

    Default

    ok I did this:
    Java Code:
    public void addDoctor(){
    		String msg=String.format("INSERT INTO tblDoctor" +
    				"(LicenseNum,FirstName,surName,Specialization,StartWorkingDate)" 
    	         	+" VALUES (%s,'%s','%s','%s','%s')",LicenseNum,FirstName,surName,Specialization,StartWorkingDate);
    		try{
    			DBconn.s.execute(msg);
    		}
    		catch(Exception e){
    			System.out.print(e.getMessage());
    		}
    	}
    but it keeps showing this error:
    Java Code:
    [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot insert the value NULL into column 'LicenseNum', table 'DB2010-Dentist.dbo.tblDoctor'; column does not allow nulls. INSERT fails.
    relevant classes:
    Java Code:
    public static boolean insertDoctor (String LicenseNum, String FirstName, String surName,String Specialization, String StartWorkingDate) {
    		boolean success=false;
    		tblDoctor details= new tblDoctor(LicenseNum, FirstName, surName, Specialization, StartWorkingDate); 
    		if (details.getDoctor(false)==false) {
    			details.addDoctor();
    			success=true;
    		}
    		return success;
    	}
    Java Code:
      private void add_dr_btActionPerformed(java.awt.event.ActionEvent evt) {
           // TODO add your handling code here:
    	   String p1=License_add_dr_txt.getText();
    		String p2=pName_add_dr_txt.getText();
    		String p3=lName_add_dr_txt.getText();
    		String p4=speci_add_dr_txt.getText();
    		String p5=date_add_dr_txt.getText();
    		if (checkFieldsCorrectness(true))
    			if (Doctor.insertDoctor(p1, p2, p3, p4, p5)==false) 
    				JOptionPane.showMessageDialog(this, "Record with the same LicenseNum (" + p1 + ") already exists.");
    			else
    				JOptionPane.showMessageDialog(this, " LicenseNum (" + p1 + ") was added succesfully.");
    
    		
       }
    p.s. I don't enter null values!!!

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,182
    Rep Power
    20

    Default

    And, since you'll now be using a PreparedStatement, you may as well use a java.sql.Date rather than a String to represent your date...

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,182
    Rep Power
    20

    Default

    You've already been advised, use a PreparedStatement...

  8. #8
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by Rose88 View Post
    p.s. I don't enter null values!!!
    Well, if the exception you posted was the complete exception, then that
    Java Code:
    ''
    portion of the exception indicates that one of the "values" was an empty String, thereby producing
    Java Code:
    ''
    instead of
    Java Code:
    'someValue'
    and in an SQL statement
    Java Code:
    ''
    is usually the same as null, as I already said.


    Now, like I said, use a PreparedStatement.

    P.S. using format is, in this regard, exactly the same as concatenating the String.

  9. #9
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,733
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by masijade View Post
    (do not ever cobble together your statements like this unless you want people to be performing SQL Injection attacks against your DB)
    A nice example.

    kind regards,

    Jos

  10. #10
    Rose88 is offline Member
    Join Date
    Apr 2009
    Posts
    19
    Rep Power
    0

    Default

    what "PreparedStatement" means??

  11. #11
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    It's another Class, just like Statement. See the API docs, and the tutorials (which I have previously linked to).

Similar Threads

  1. 48 bits JPEG 2000
    By user_java in forum Java 2D
    Replies: 0
    Last Post: 03-02-2010, 06:19 AM
  2. Replies: 1
    Last Post: 12-30-2008, 05:39 PM
  3. JSP Connecting to MS SQL SERVER 2000 using JDBC
    By hisouka in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 09-02-2008, 10:23 AM
  4. Help, conect a JSP, SQL SERVER 2000
    By Marcus in forum JavaServer Pages (JSP) and JSTL
    Replies: 1
    Last Post: 07-06-2007, 04:04 PM
  5. SQLServer 2000 EXCEPTION
    By Heather in forum JDBC
    Replies: 3
    Last Post: 06-29-2007, 04:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •