problem with jdbc (sql 2000)

[Rose88]

Hey everybody,

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

DBconn.s.execute("INSERT INTO tblDoctor VALUES ('"+LicenseNum+"','"+FirstName+"'" +
					",'"+surName+"','"+Specialization+"','"+StartWorkingDate+"' " );

and the error I get is :

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near ''.

[doWhile]

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

[Eranga]

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.

[masijade]

Seemingly, one of those variable is an "empty" string, thereby leading to one of the "values" in the query being

''

. 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).

[Rose88]

ok I did this:

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:

[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:

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;
	}

  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!!!

[Tolls]

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...

[Tolls]

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

[masijade]

p.s. I don't enter null values!!!

Well, if the exception you posted was the complete exception, then that

''

portion of the exception indicates that one of the "values" was an empty String, thereby producing

''

instead of

'someValue'

and in an SQL statement

''

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.

[JosAH]

(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

[Rose88]

what "PreparedStatement" means??

[masijade]

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