Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LittlBUGer
Starting Member
19 Posts |
 Posted - 2009-06-02 : 12:57:37
|
Hello. I've been having a mind boggling strange problem with a stored procedure I'm using within an ASP.NET website.Basically, I'm importing data from an Excel file/template into an existing table in a database (updating data as necessary). The steps are that I use sqlbulkcopy to get the data from the Excel file to a temp table in SQL Server, which is working fine as I can see all data correctly imported into this table. Then I call my stored procedure to parse through the temp table and update the real table based on the information there. This is where the problem is occurring, at least that is what I keep getting for an error via ASP.NET and Visual Studio.The specific error is: "Conversion failed when converting the varchar value '63.10' to data type int." and happens during the call to the SP via the ASP.NET (VB.NET) code. The value '63.10' above resides in a 'varchar(50)' field in the temp table and is being imported/updated to the real table which has column datatype 'float'. The code in the SP (or at least the important snippets) is as follows:[dbo].[sp_MBN_validate_import_EIDs2_admin] @table varchar(100) AS BEGIN DECLARE @strSQL varchar(2000) --CarcassID SET @strSQL = 'UPDATE view_User_Animals SET CarcassID = B.[Carcass ID] FROM view_User_Animals A JOIN ' + @table + ' B ON A.EID=B.EID WHERE B.[Carcass ID] IS NOT NULL' EXEC(@strSQL) --CarcassWeight SET @strSQL = 'UPDATE view_User_Animals SET CarcassWeight = B.[Carcass Weight] FROM view_User_Animals A JOIN ' + @table + ' B ON A.EID=B.EID WHERE B.[Carcass Weight] IS NOT NULL' EXEC(@strSQL)... --DressingPercentage SET @strSQL = 'UPDATE view_User_Animals SET DressingPercentage = B.[Dressing Percentage] FROM view_User_Animals A JOIN ' + @table + ' B ON A.EID=B.EID WHERE B.[Dressing Percentage] IS NOT NULL' EXEC(@strSQL)... END It's nothing special and I'm just passing it the temp table name. The part that is causing the issue is where the 'DressingPercentage' is getting updated. As you can see, based on the datatypes in the temp table and the datatypes in the real table, and how the SP pans out, there's no reason at all for it to be trying to convert the values to an integer. Yet, no matter what I try, I continually get that error. What's even more strange is we've used this feature (and this SP) before in the past without error, though this is the first time the 'DressingPercentage' is actually being updated whereas before all other columns were getting updated.Please, if anyone has any suggestions or sees anything wrong, let me know right away as it's starting to drive me mad. Thanks for your help! :)   |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-02 : 13:12:10
|
| are you sure DressingPercentage is float? error message suggests you're trying to cast it to int value so i suspect if DressingPercentage is int |
 |
|
|
LittlBUGer
Starting Member
19 Posts |
Posted - 2009-06-02 : 13:48:32
|
| It's not an int, but that's besides the point now...I'm sorry to bother you guys, but I think I just found the solution. I didn't even notice this or realize what was happening, but in the SP, right above the DressingPercentage code is some code for another column of data (not shown in this topic). In that code it references the DressingPercentage data and tries to do some math with a couple integer values. As soon as I wrapped a CAST AS FLOAT around the DressingPercentage data, all seemed to work fine. I really don't know how I missed that, but at least it's finally fixed now. Thanks for putting up with this. :) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-02 : 14:09:53
|
Why are you storing numbers and decimals in a varchar column in the first place?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
LittlBUGer
Starting Member
19 Posts |
Posted - 2009-06-02 : 15:07:35
|
| Because of how picky/finicky the process is to grab all of the values from the Excel files, I had to make all columns in the temp table text basically, so either varchar or nvarchar. It has worked great in the past, except for the one column described above. :) |
|
|
|
|
|
|
|
|
Stored Procedure secretly converts value to int?