1

I'm working on a database project about adding, editing and deleting registries to a Students table which has fields:

Last_names, Names, IcNumber, Average, Entry_mode, Career and Change

In the editing frame i have a field where user types the icnumber of the student to edit its data, asks for the new data and saves it to a "Students" data structure, and then reupdates the registry with the new data:

String stmnt = "Insert Into Students (Last_names, Names, IcNumber, Average, " + 
        "Entry_mode, Career, Change) Values ('" + student.getLastNames() + 
        "', '" + student.getNames() + "', '" + student.getIcNumber() + "', " +
        student.getAverage() + ", '" + student.getEntry() + "', '" +
        student.getCareer() + "', '" + student.getChange() + "') " +
        "Where IcNumber = '" + field.getText() + "'";

statement.execute(stmnt);

And i get this Error message:

[Microsoft][Microsoft Access ODBC Driver] "Query input must contain at least one table or query."

I have tried a similar SQL Instruction in the adding registry area of my program without the "Where" condition and works good, anyone knows about that error?

Improve this question
7
  • Try SELECT on that table, then INSERT in a simple SQL string. Commented Oct 16, 2012 at 4:34
  • And, btw, use params instead of concatenating strings. Commented Oct 16, 2012 at 4:35
  • 1
    Just as a side comment, use Prepared Statements, also make sure that the Students table exist and should you have a where statement on an Insert?? Commented Oct 16, 2012 at 4:35
  • Why do you have a where clause in the insert statement? Commented Oct 16, 2012 at 4:35
  • @MadProgrammer I tried the same when adding a new registry but without the where because it just adds a registry without conditions, i this case i need that where because its gonna insert that data in the registry that matches the icNumber the user inputs its like INSERT Into Students (Names) Values (newNames) Where IcNumber = 'inputIcNumber' Commented Oct 16, 2012 at 4:40

3 Answers 3

Reset to default
2

You should use a subquery, first the SELECT part with WHERE and then the INSERT part Something like:

  if (cond){
             (SELECT.....)
                (INSERT INTO...)}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Why are you using where in a insert statement? Where clause is applicable in select, update and delete statements but not in insert. Also I don't see any need of the where clause in your query.

Simply use the insert statement without where clause.

Improve this answer

3 Comments

I need the where because it's going to update a specific registry that matches the input icNumber, i'ma try update. Thanks!
Then use update statement e.g. update Students set Last_names = student.getLastNames(), ...... where IcNumber = field.getText(). Where you use where, it mean that record is already present in the table and if record is already present, insert doesn't make any sense. If still you have any doubts, please share your requirement with one data example. That will help understand better.
Yeah i just understood what everyone were trying to say, it was a syntax mistake, sorry for my ignorance and thank you very much!
0

Use an INSERT statement to add a new record. A WHERE clause does not belong in an INSERT statement.

If you're editing an existing record, then you should use an UPDATE statement, and a WHERE clause makes sense to identify which record to change.

Improve this answer

4 Comments

Ok, i'm going to try this. I have used Insert With Where in other method and worked, but it was for a multi-valued field (access): statement.execute("Insert Into Students (Teachers.Value) Values ('" + student.getIcNumber() + "') Where IcNumber = '" + professor.getIcNumber() + "';");
Multi-valued fields are odd. Each field "value" is like a separate recordset, and its member values must be referenced differently in SQL than with normal fields. I wouldn't recommend anyone use multi-valued fields. :-(
Believe me when i say that it was the only option i had for the requirements, apart of those fields, the last fields (Professors) is a field that depends on some values located in other table, this field reffers to the professors who make a solicitude for that specific student, so there can be various values on that field for each student, values which are the professors IcNumbers, which ones are located in the "Professors Table". I do the same for the Professors table, it has a Students field that depends on the students table, i have a double relationship in the tables.
And as the values can be 1, 2 or 1000 icnumbers located in another table, i used a multi-valued field