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: Tuesday, March 25, 2014 11:39 PM
Points: 13, Visits: 34
Thanks for your help peple
Post #1545372
Posted Wednesday, February 26, 2014 7:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 385, Visits: 1,877
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
Post #1545381
Posted Wednesday, February 26, 2014 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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: Tuesday, March 25, 2014 11:39 PM
Points: 13, Visits: 34
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:44 PM
Points: 11,970, Visits: 10,995
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:44 PM
Points: 11,970, Visits: 10,995
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:44 PM
Points: 11,970, Visits: 10,995
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:00 PM
Points: 2,763, Visits: 5,905
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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:44 PM
Points: 11,970, Visits: 10,995
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