BDE "Invalid Parameter" on INSERT with constants.

[BazookaJo]

Inserting data into an SQL2000 table from Paradox, but get an "Invalid Parameter" error if I try to use a constant e.g.

Insert into ":SQLDatabase:SQLTable"
(Members_Code, User_Ref)
Select Members_Code, 13
From ParadoxTable

Works fine if I change 13 for an integer fieldname.

Any ideas?

Regards - Paul.

 

[svanels]

The query is looking for a column named 13

it should be:
Select Members_Code, UserRef
From ParadoxTable where UserRef = '13'


Steven van Els
SAvanEls@cq-link.sr

 

[BazookaJo]

That seems to give me the error:
"Type Mismatch in Expression"

 

[Jonah86]

Take the quotes from around 13 perhaps?

 

[VintageWine]

Your error message is "Invalid Parameter". Delphi interprets ":" as the start of a parameter. It therefore thinks ":SQLDatabase" and ":SQLTable" are parameters for which you have not supplied values.

I managed to create a SQL Server table called ":SQLTable", but I would strongly advise starting the names of databases, tables - well everything really - with an alpha character or underscore.

Have fun
Simon

 

[BazookaJo]

Perhaps I have been unclear. There is no user_ref field in the paradox table. I am trying to pass an integer constant in place of this missing field and this is where the problem lies i.e.

a) This works (passing field values from one table to another)
Insert into ":SQLDatabase:SQLTable"
(Members_Code, Co_Ref)
Select Members_Code, Co_Code
From ParadoxTable

b) This also works (passing a field value and a string constant)
Insert into ":SQLDatabase:SQLTable"
(Members_Code, Co_Ref)
Select Members_Code, 'X001'
From ParadoxTable

c) This doesn't work (passing a field value and an integer constant results in "Invalid Parameter&quot
Insert into ":SQLDatabase:SQLTable"
(Members_Code, User_Ref)
Select Members_Code, 13
From ParadoxTable

If I put quotes round the integer e.g. '13' I then get the error "Type Mismatch in Expression".

Regards - Paul.

 

[Jonah86]

So, you're trying to insert a value into a field that isn't already in the database? I'm not sure I understand.

 

[BazookaJo]

That's right.

The Paradox table contains the records of the employees who are to receive a letter. This contains the Employees Code, and other general details.

The SQL Table contains records for Employees who are to receive letters. This contains the Employees Code plus the details of the letter to be sent i.e. which letter, when it was sent, and who sent it.

Only the Employee code is common between the 2 tables, therefore I need to feed these extra Letter details as constants in my insert statement (via my Delphi application). The result being a Letter record for each employee in the Paradox table.

This seems to work fine as long I don't try to pass a numeric constant (i.e. the userref), in which case I get the problems mentioned previously.

Regards - Paul.

 

[BazookaJo]

Ah - Sussed it!

The SQL field was a tinyint not an integer value.

Therefore casting it as a smallint i.e.
CAST(13 AS SmallInt) did the job.

Thanks to everyone for your help and inspiration.

Regards - Paul.