INSERT... SELECT... 1

[gregz0012]

The following Access SQL query returns the following error, and I cannot see why:

INSERT INTO
Users (Personnel, FirstName, LastName, Manager)
SELECT
666, 'joe', 'bloggs', Managers.Manager
FROM
Managers
WHERE
Manager.FirstName + ' ' + Manager.LastName = 'James Brown'

The query should insert (666, joe, bloggs, 1234) into the users table which looks like (Personnel: Long Integer, FirstName: String, LastName: String, Manager: Long Integer) the managers table looks like (Manager: Long Integer, FirstName: String, LastName: String)

Any help would be very much appreciated.

 

[gregz0012]

Sorry, forgot to add the error: Too few parameters. Expected 6.

Might help aye

 

[dhookom]

Is this the SQL view of a query or are you attempting to run this from code. If code, please provide more code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gregz0012]

Yes this is a coded SQL statement represented as pure SQL, however I am running all SQL code through MS Access first to ensure that the SQL syntax etc is correct.

I can post the code but it would not really be relevant at this stage becase basically it's not the code messing things up.

 

[gregz0012]

Here's the code anyway. It's written in Delphi and being run off the database using Jet. Might not make much sense if you don't speak Delphi.

with TADOQuery.Create(Application) do
begin
ConnectionString := ConStr;
DisableControls;
Close;
SQL.Clear;
begin
SQL.Text := 'INSERT INTO Users (Personnel, FirstName, LastName, ' +
'Manager) SELECT no, :fn, :ln, Managers.Manager ' +
'FROM Managers WHERE Manager.FirstName +' + Quote(#32) + '+ ' +
'Manager.LastName = :man';
with Parameters do
begin
ParamByName('pno').DataType := ftInteger;
ParamByName('fn').DataType := ftString;
ParamByName('ln').DataType := ftString;
ParamByName('manager').DataType := ftString;
ParamByName('pno').Value := Trim(Edit3.Text);
ParamByName('fn').Value := Trim(Edit1.Text);
ParamByName('ln').Value := Trim(Edit2.Text);
ParamByName('man').Value := Trim(Edit4.Text);
end;
try
SQL.Exec;
finally
Close;
Free;
end;
end;

 

[Golom]

True enough ... I don't speak Delphi. In the languages I do speak however, this

SQL.Text := 'INSERT INTO Users (Personnel, FirstName, LastName, ' +
      'Manager) SELECT :pno, :fn, :ln, Managers.Manager ' +
      'FROM Managers WHERE Manager.FirstName +' + Quote(#32) + '+ ' +
      'Manager.LastName = :man';

Would resolve to

... SELECT :pno, :fn, :ln, ...

where no, :fn and :ln would remain as those text strings rather than having the corresponding variable values substituted for them. Should it be something like

SELECT ' + :pno + ', ' + :fn + ', ' + :ln + ', ' ...

You may also need to put single quotes around those variables because text strings in SQL need to be enclosed in quotes.

 

[gregz0012]

No, the code as I said is not the problem. Using the ADOQuery component in Delphi allows parameters to be passed into an SQL query string symbolised by 'aramname'. The allows variables to be passed exactly as they are into the string

The code is perfect. The SQL query is the problem.

 

[Golom]

Is there any way to display the value of SQL.Text just before SQL.Exec runs?

The error message you are seeing usually means that fields referenced in the SQL statement were not found in the table(s) in the FROM clause and are therefore assumed to be parameter names. Since no values were supplied for those parameters, the SQL parser doesn't know what to do.

Just as an example

Select 'A', 'B', 'C', myTbl.Field1 From myTable

Is valid SQL which will create 3 fields and will select a fourth field (Field1) from myTable. In contrast

Select A, B, C, myTbl.Field1 From myTable

will report "Too few Parameters Expected 3" because A, B, C are assumed to be parameter names (not field values) and no parameter values have been supplied for them.

Perversely enough

Select 1, 2, 3, myTbl.Field1 From myTable

is valid SQL because numeric values do not require quotes around them.

 

[gregz0012]

May I reiterate, I am using the SQL query functionality within MS Access first to make sure all my SQL queries work before I use them in my program.

Take my programming and Delphi completely out of the picture. The SQL query posted in my first post is what I am using. And it doesn't work. I cannot see why as the syntax looks valid and so does the structure of the query.

 

[PHV]

ParamByName('man[!]ager[/!]').DataType := ftString;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gregz0012]

To simplify my problem:

My database:

------------------
Users Table
------------------
Users.Personnel | Long Integer
Users.FirstName | String
Users.LastName | String
Users.Manager | Long Integer
Users.Location | Byte
Users.Department | Byte

------------------
Managers Table
------------------
Managers.Manager | Long Integer
Managers.FirstName | String
Managers.LastName | String
Managers.Location | Byte
Managers.Department | Byte

I want a query to insert Personnel, FirstName, LastName, Manager into Users table. I want Manager to be looked up from the managers table on the insert because the user will be inputing the managers first and last name to get a match.

What would the query be?

 

[gregz0012]

ParamByName('manager').DataType := ftString;"
Was a type when typing the code out to go in the post. This is not in the code for the program.

 

[PHV]

error: Too few parameters. Expected 6.
This is NOT an error you may raise in the access's SQL window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gregz0012]

If that line of programming was at fault I woul still not get 'Too few parameters. Expected 6' I would get the error before my program tried to execute the query 'parameter cannot be found'.

I used a third party SQL debugger called SQLC to figure out the root of the problem so people might be able to help further. Rather than me just saying 'I get an error!'.

 

[PHV]

So, what happen when you use the access's SQL window ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gregz0012]

It asks me to enter data for Managers.FirstName and Managers.LastName (from the WHERE statement) however when valid and existing data for these columns is input the data is still not added to the users table.

 

[PHV]

WHERE
Manager[!]s[/!].FirstName & ' ' & Manager[!]s[/!].LastName = 'James Brown'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gregz0012]

Fantastic.

I don't understand how I and so many others didn't spot that one.

 

[lespaul]

and for future reference you can use:

ShowMessage(SQL.Text);

when you are done building your SQL in Delphi in order to get a message box that displays the SQL that is to be run.

Additionally, unless Delphi has changed since Delphi 6, this:

try
SQL.Exec;
finally

should be:

ExecSQL;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi