Update with ADO

gra8ful10

Hello,

I have successfully used Miklos' reading external databases with ADO to read an external SQL database from another SQL database, however when I try to change the SELECT statement:

SQLString := 'select texttest,[decimal test] as decimaltest,datetest from [Randolph EMC$testdata]';

to an UPDATE statement:

name := 'BRIAN';
number := 34.41;

SQLString := STRSUBSTNO('UPDATE [Randolph EMC$testdata] SET texttest = "%1" WHERE [decimal test] = "%2";',name,number);

I get the following error:

The call to member Execute failed. Microsoft OLE DB Driver for SQL Server returned the following message:
Invalid column name '34.41'.

What am I doing wrong? I believe it is just a formatting issue, but I have restructured the Update statement in many ways and can't figure it out.

Thanks,
Brian.

aci

gra8ful10 wrote:

SQLString := STRSUBSTNO('UPDATE [Randolph EMC$testdata] SET texttest = "%1" WHERE [decimal test] = "%2";',name,number);

Try removing the double quotes from around %2, because you don't need them with numbers.

Also, it's always useful to show the SQL statement in a message box, so you could see the actual string passed to ADO.

gra8ful10

Thanks for the reply and the suggestion about the MESSAGE box, that's a good idea. I removed the quotes around %2 (which I believe is correct) and the error changed to:

Invalid column name 'BRIAN'.

Any other ideas?
Brian.

aci

Change the double quotes to single quotes. See the samples here:

http://msdn.microsoft.com/library/en-us ... z_82n9.asp