Getting A Data Conversion error
-
Hey guys, I have a new problem. I'm now trying to use a stored procedure to post to the database but I'm getting the error below.
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Error converting data type varchar to numeric.
/MySubmitPageExceptionsSQLServerB.asp, line 123
How I'm calling the Stored Procedure in the page
sql_update = Conn.Execute ("qry_spInsertRecord @PostDate='" & CleanInput("txtDated_" & id) & _
"', @ClientCode='" & CleanInput("txtDataTable_" & id) & _
"', @MonthID='" & CleanInput("txtMonths_" & id) & _
"', @YearID='" & CleanInput("txtYears_" & id) & _
"', @shift='" & CleanInput("txtShifts_" & id) & _
"', @Correspondence='" & CleanInput("txtCorrespondence_" & id) & _
"', @Unbankables='" & CleanInput("txtUnbankables_" & id) & _
"', @Lookups='" & CleanInput("txtLookups_" & id) & _
"', @Attempts='" & CleanInput("txtAttempts_" & id) & _
"', @Returns='" & CleanInput("txtReturns_" & id) & _
"', @ReturnsNF='" & CleanInput("txtReturnsNF_" & id) & _
"', @ExtractOnly='" & CleanInput("txtExtractOnly_" & id) & _
"', @PostalCards='" & CleanInput("txtPostalCards_" & id) & _
"', @ReturnEnv='" & CleanInput("txtReturnEnv_" & id) & _
"', @BackDate='" & CleanInput("txtBackDate_" & id) & _
"', @AddressChange='" & CleanInput("txtAddressChange_" & id) & _
"', @PostMark='" & CleanInput("txtPostMark_" & id) & _
"', @CreditCard='" & CleanInput("txtCreditCard_" & id) & _
"', @EIP='" &CleanInput("txtEIP_" & id) & "' ")
Here is the Stored Procedure. The datatype in the table for everything past @ClientCode is set to integer. I tried numeric but got the same error. Whats wrong?
CREATE PROCEDURE qry_spInsertRecord
@PostDate datetime, @MonthID varchar(50), @YearID varchar(50), @shift varchar (50),
@ClientCode varchar(6), @Correspondence varchar(50), @Unbankables varchar(50), @Lookups varchar(50), @Attempts varchar(50), @Returns varchar(50),
@ReturnsNF varchar(50), @ExtractOnly varchar(50), @PostalCards varchar(50), @ReturnEnv varchar(50), @BackDate varchar(50), @AddressChange varchar(50),
@PostMark varchar(50), @CreditCard varchar(50), @EIP varchar(50)
AS
BEGIN
SET NOCOUNT ON
DECLARE @tableID INT
INSERT tblImportedVolume
(
VolDate, MonthID, YearID, Shift, ClientCode, Correspondence, Unbankables, Lookups, Attempts, Returns, ReturnsNF, ExtractOnly, PostalCards, ReturnEnv,
BackDate, AddressChange, PostMark, CreditCard, EIP
)
Values
(
@PostDate, @ClientCode, @MonthID, @YearID, @shift,
CONVERT(numeric, @Correspondence, 0), CONVERT(numeric, @Unbankables, 0), CONVERT(numeric, @Lookups, 0),
CONVERT(numeric, @Attempts, 0), CONVERT(numeric, @Returns, 0), CONVERT(numeric, @ReturnsNF, 0),
CONVERT(numeric, @ExtractOnly, 0), CONVERT(numeric, @PostalCards, 0), CONVERT(numeric, @ReturnEnv, 0),
CONVERT(numeric, @BackDate, 0), CONVERT(numeric, @AddressChange, 0), CONVERT(numeric, @PostMark, 0),
CONVERT(numeric, @CreditCard, 0), CONVERT(numeric, @EIP, 0)
)
SELECT @tableID = @@IDENTITY
SELECT ClientCode = @tableID
END
GO
-
the issue is proably your CleanInput() recordset; if it is returning an empty string instead of NULL or ZERO for things that expect an integer value(like ATTEMPTS) then you'll get that error.
i'm betting that CleanInput is just replacing single quotes to avoid issues with names like O'Malley, so maybe you need two functions, one for strings and one for numeric values, which might return null or zero, depending on your requirements:
sql_update = Conn.Execute ("qry_spInsertRecord @PostDate='" & CleanInput("txtDated_" & id) & _
"', @ClientCode='" & CleanInput("txtDataTable_" & id) & _
"', @MonthID='" & NullIfEmpty(CleanInput("txtMonths_" & id)) & _
"', @YearID='" & NullIfEmpty(CleanInput"txtYears_" & id)) & _
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply