Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conversion failed when converting the varchar value '099.0' to data type int. Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 6:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:15 AM
Points: 18, Visits: 43
Thanks for your help peple
Post #1545372
Posted Wednesday, February 26, 2014 7:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:37 AM
Points: 1,166, Visits: 3,832
vsamuel (2/26/2014)

I have this script below which initially could execute without problems. Now its giving me this error: "Conversion failed when converting the varchar value '099.0' to data type int.
"


I'm not sure but I think, '099.0' won't be recognised as an integer because of either the preceding 0 and\or the following decimal point. Int has to be a whole number.



On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help
Post #1545381
Posted Wednesday, February 26, 2014 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:48 PM
Points: 2,834, Visits: 8,542
Perhaps you could declare a variable as DECIMAL(10,2) then set the INT column to the value in the decimal variable... a 2 step conversion.


Post #1545388
Posted Wednesday, February 26, 2014 7:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:15 AM
Points: 18, Visits: 43
Many thanks, I have done that and its working
Post #1545398
Posted Wednesday, February 26, 2014 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
You need to look up sql injection. Your code is pretty much a text book case of vulnerable to sql injection.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1545402
Posted Wednesday, February 26, 2014 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
I have a feeling too that you could make this entire thing into a few insert statements with no dynamic sql and no looping. It is so hard to read.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1545405
Posted Wednesday, February 26, 2014 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
I am pretty sure you could get rid of the dynamic sql at the very least. Isn't this this same thing for Check 1?

/* check 1 */
IF @Check1 is null
insert into Discrepancies
(
FormName,
IDNumber,
VisitDate,
ErrorDetail,
ErrVarName1,
Status
)
select @FormName,
@PatientID,
@VisitDate,
'enr_first cannot be null',
'enr_first',
'Open'
where not exists
(
select *
from Discrepancies
where FormName = @FormName
and IDNumber = @PatientID
and VisitDate = @VisitDate
and ErrorDetail = @ErrorDetail
)

It is hard to figure out exactly what your code is doing but I think the loop would be fairly easy to get rid of too.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1545417
Posted Wednesday, February 26, 2014 8:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 3,908, Visits: 8,859
Sean Lange (2/26/2014)

It is hard to figure out exactly what your code is doing but I think the loop would be fairly easy to get rid of too.

And now it's harder because the code is gone.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1545431
Posted Wednesday, February 26, 2014 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
Luis Cazares (2/26/2014)
Sean Lange (2/26/2014)

It is hard to figure out exactly what your code is doing but I think the loop would be fairly easy to get rid of too.

And now it's harder because the code is gone.


Well I guess they "fixed" the issue and don't want to learn that the way they are doing it is far from optimal.

Too bad because this would be a great chance for the OP to learn a LOT about how to handle this type of thing.

I copied the original code before the OP removed it. I generally quote posts to avoid the code being removed. Unless the OP comes back and wants to fix the nightmarish dynamic sql I guess we will have to assume they are satisfied.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1545435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse