Link to home
Start Free TrialLog in
Avatar of Vasi04
Vasi04

asked on

Trigger code: ....Error converting data type varchar to real

Alex, please this thread to "trigger error"
------------------------------------------------------------
Rmp table structure:
_sow_bank2, varchar (40)
_sow_bank3, varchar (40)
_sow_bank4, varchar (40)
_sow_bank5, varchar (40)

_sow_money2, float(8)
_sow_money3, float(8)
_sow_money4, float(8)
_sow_money5, float(8)

_sow_date2, smalldatetime
_sow_date3, smalldatetime
_sow_date4, smalldatetime
_sow_date5, smalldatetime
--------------------------------

I got this error msg...
....error converting data type varchar to real
SQL Error msg 8114, state 5...

Avatar of alexpreston
alexpreston

try this.

set @sqlstr = @sqlstr + @comma + '''' @bank_name + ''', ' + cast(@bank_assets as float(8)) + ', ''' + @changed_date + '''';
 
hang on let me think. I don't usually build up SQL like this, so it's a bit out of my box :)
yes, it's not the @bank_assets that's the problem.
It's the lack of quotes around @bank_name. (which I had spotted but not mentioned!)

try this

set @sqlstr = @sqlstr + @comma + '''' @bank_name + ''', ' + @bank_assets + ', ''' + @changed_date + '''';

i.e. the only change is the inclusion of singles quotes around the bank name
Avatar of Vasi04

ASKER

will get back soon. thanks
Avatar of Vasi04

ASKER

One more question:
suppose if there is no value to insert (say bank_name), then can I retain whatever was there in the rmp field. can I have this in my code? Sorry too many questions
Not quite sure what you mean! Since you are inserting, what do you want to retain?
Avatar of Vasi04

ASKER

CREATE TRIGGER [myRMP] ON [BANKREL]
FOR INSERT, UPDATE, DELETE
AS

BEGIN

DECLARE query CURSOR FOR
select _bank, convert(varchar, _change_date), _assets
from bankrel where bankrel._active = 'Y' and _siteid in (select _siteid from inserted)
order by bankrel._assets desc ;
declare @bank_name varchar(500);
declare @changed_date varchar(500);
declare @bank_assets varchar(500);
declare @counter int;
declare @sqlstr varchar (4000);
declare @comma varchar;

set @sqlstr = 'insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3, _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values (';
set @counter = 0;
set @comma = ' ';

OPEN query;
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;
WHILE (@@FETCH_STATUS <> -1 and @counter <> 4 )
BEGIN
      set @counter = @counter + 1;
           set @sqlstr = @sqlstr + @comma + '''' @bank_name + ''', ' + @bank_assets + ', ''' + @changed_date + '''';
           set @comma = ',';
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;
END;

CLOSE query;
DEALLOCATE query;

set @sqlstr = @sqlstr + ')';
exec(@sqlstr);
END;

----------------------------------------------------------------
Error msg:

Incorrect syntax near @bank_name
oops, missed a "+"

set @sqlstr = @sqlstr + @comma + '''' + @bank_name + ''', ' + @bank_assets + ', ''' + @changed_date + '''';
Avatar of Vasi04

ASKER

syntax is fine.,
I get error msg:
server msg 170, state1,.... incorrect syntax nere '19'
Could you maybe write out @sqlstr instead of executing it. Then I can see exactly what SQL it has produced.
Maybe just set up a table with one varchar(8000) field.
Instead of the exec(@sqlstr) line, do insert into [table] values @sqlstr.
Then look up what has been inserted and post the result.
Ta.
values ( @sqlstr )          :-)
Avatar of Vasi04

ASKER

ok
Avatar of Vasi04

ASKER

Hi
It has written complete sqlstr into first 3 rows but I cannot copy the field
. Says "coinitialize not found" when try to copy the content of the row.
That's a new one on me. I'll have a think.
Are you trying to copy from EM? Try it in QA.
Avatar of Vasi04

ASKER

Here it goes (results from QA)
.................................

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Aargauische Kantonalbank', Aug 19 2004 12:00AM, '90','ABN-AMRO', Aug 19 2

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Bank of Bermuda', Aug 19 2004 12:00AM, '99','Aargauische Kantonalbank', A

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Bank of Bermuda', Aug 19 2004 12:00AM, '99','Aargauische Kantonalbank', A
insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Bank of Bermuda', Aug 19 2004 12:00AM, '99','Aargauische Kantonalbank', A

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale du Valais', Aug 19 2004 12:00AM, '100')

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale du Valais', Aug 19 2004 12:00AM, '100','Bank Rothschild'

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale de Zurich', Aug 19 2004 12:00AM, '120','Banque Cantonale

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale de Zurich', Aug 19 2004 12:00AM, '120','Banque Cantonale

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale de Zurich', Aug 19 2004 12:00AM, '120','Banque Cantonale
...
...
(13 row(s) affected)

--

Its not writing ')' at the end!!
The assets and the date are in the wrong order.

Change your initial select to..
select _bank, _assets, convert(varchar, _change_date)

QA has a maximum number of characters. I suspect this is set too low. Hence it's cutting off a lot of each statement.
You can change this by
Tools
Options
Results
Maximum characters per column.

But I suspect by correcting the order of assets and date this might fix the original issue!!!
Avatar of Vasi04

ASKER

Thanks.
one more thing, are we inserting the values, instead of updating. Insert creates new rows. DO I have replace insert with update?
ASKER CERTIFIED SOLUTION
Avatar of alexpreston
alexpreston

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If I have the correct view of what you are trying to do (update the top 4 values when a change is made), then you probably want to delete the existing top 4, then insert the new top 4. Is this right?
Avatar of Vasi04

ASKER

You are right, delete old values and insert new values.
inserting or overwriting new values to already existing rmp rows
Avatar of Vasi04

ASKER

I replaced insert into table1...
 with.. exec ( @sqlstr);
I get this error:
...Canboot insert null´into column _SQL, tabeldbo.rmo, column does not allow null. INSERT fails.
I think that's because sometimes there aren't 4 rows, so it doesn't go around the loop 4 times. The simple solution is to either set the columns to allow nulls, or give them a default value.
Avatar of Vasi04

ASKER

I tried, but gives me same error.
Where has this column _SQL come from? It says it's in table rmo. I don't know what's going on with this table, I thought we were inserting into table rmp !?!?
Avatar of Vasi04

ASKER

I thnk, I am also lost. We need to update fields in rmp. I think that would solve the problem, could you please give me update code instead of insert into?
I thank you a lot.
sorry, bit busy, not sure I'll have time to look at this for a while.
Avatar of Vasi04

ASKER

anyway thanks. but If you could help, I can start a new thread
Avatar of Vasi04

ASKER

Hi ALex, I have tried to use update statements in our code.
Could you pls have a look. correct any incorrect code?
I get this error msg ... "cannot convert data type varchar to float".
Is it ok to use table "inserted", even there is update or delete action on bankrel?
Thanks
---------------------------------------------------------------

CREATE TRIGGER [myRMP] ON [BANKREL]
FOR INSERT, DELETE, UPDATE
AS

BEGIN

DECLARE query CURSOR FOR
select Top 4 bankrel._bank, bankrel. _change_date, bankrel._assets, bankrel._internid
from bankrel where bankrel._active = 'Y' and bankrel._internid in (select _internid from inserted)
order by bankrel._assets desc;
declare @bank_name varchar(500);
declare @changed_date varchar(500);
declare @bank_assets varchar(500);
declare @internid varchar(500);
declare @counter int;
set @counter = 0;

OPEN query;
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date, @internid;
WHILE (@@FETCH_STATUS <> -1 and @counter <> 4 )

BEGIN

UPDATE RMP SET _sow_bank2 = @bank_name, _sow_money2 = @bank_assets, _sow_date2 = @changed_date  
where rmp._internID = @internID and rmp._year = Year (today)
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;

UPDATE RMP SET _sow_bank3 = @bank_name, _sow_money3 = @bank_assets, _sow_date3 = @changed_date
where rmp._internID = @internID and rmp._year = Year (today)
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;

UPDATE RMP SET _sow_bank4 = @bank_name, _sow_money4 = @bank_assets, _sow_date4 = @changed_date
where rmp._internID = @internID  and rmp._year = Year (today)
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;

UPDATE RMP SET _sow_bank5 = @bank_name, _sow_money5 = @bank_assets, _sow_date5 = @changed_date
where rmp._internID = @internID  and rmp._year = Year (today)
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;

END;

CLOSE query;
DEALLOCATE query;

END;
Avatar of Vasi04

ASKER

Hi ALex, Your earlier code "with insert into" works fine when I try to insert into a temp tabel. But I try to insert into rmp tbale, sql server comes up with converting error msg like ...cannot convert data type varchar to real or data type float to ..

Actually insert into statment overwrites existing data in rmp, it does not append. so this is fine.

Also I was getting this error:
...Cannot insert null into column _SQL, table dbo.rmp, column does not allow null. INSERT fails.

That was reason I was thinking about update instead of insert into. But if you help me me with converting data type error, I guess I would be almost solving the problem

Any idea? Thanks so much