Solved

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

Posted on 2004-08-19
30
651 Views
Last Modified: 2008-02-01
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...

0
Comment
Question by:Vasi04
  • 15
  • 15
30 Comments
 
LVL 3

Expert Comment

by:alexpreston
ID: 11839429
try this.

set @sqlstr = @sqlstr + @comma + '''' @bank_name + ''', ' + cast(@bank_assets as float(8)) + ', ''' + @changed_date + '''';
 
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11839438
hang on let me think. I don't usually build up SQL like this, so it's a bit out of my box :)
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11839446
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
0
 

Author Comment

by:Vasi04
ID: 11839505
will get back soon. thanks
0
 

Author Comment

by:Vasi04
ID: 11839537
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
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11839577
Not quite sure what you mean! Since you are inserting, what do you want to retain?
0
 

Author Comment

by:Vasi04
ID: 11839586
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
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11839615
oops, missed a "+"

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

Author Comment

by:Vasi04
ID: 11839792
syntax is fine.,
I get error msg:
server msg 170, state1,.... incorrect syntax nere '19'
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11839824
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.
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11839826
values ( @sqlstr )          :-)
0
 

Author Comment

by:Vasi04
ID: 11839848
ok
0
 

Author Comment

by:Vasi04
ID: 11839990
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.
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11840007
That's a new one on me. I'll have a think.
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11840024
Are you trying to copy from EM? Try it in QA.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Vasi04
ID: 11840075
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!!
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11840117
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!!!
0
 

Author Comment

by:Vasi04
ID: 11840202
Thanks.
one more thing, are we inserting the values, instead of updating. Insert creates new rows. DO I have replace insert with update?
0
 
LVL 3

Accepted Solution

by:
alexpreston earned 500 total points
ID: 11840240
That depends what you want to do!!!

If you do need an update you'll want to change it to something like

UPDATE tablename SET numberfield = value, stringfield = 'value'
WHERE somefield = criteria

0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11840247
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?
0
 

Author Comment

by:Vasi04
ID: 11840268
You are right, delete old values and insert new values.
inserting or overwriting new values to already existing rmp rows
0
 

Author Comment

by:Vasi04
ID: 11840341
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.
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11840369
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.
0
 

Author Comment

by:Vasi04
ID: 11840874
I tried, but gives me same error.
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11840933
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 !?!?
0
 

Author Comment

by:Vasi04
ID: 11841450
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.
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11841903
sorry, bit busy, not sure I'll have time to look at this for a while.
0
 

Author Comment

by:Vasi04
ID: 11842428
anyway thanks. but If you could help, I can start a new thread
0
 

Author Comment

by:Vasi04
ID: 11849720
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;
0
 

Author Comment

by:Vasi04
ID: 11858029
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now