Hi. I'm receiving the error "Error converting data type varchar to numeric" whenever I try to enter a user leaves a certain textbox empty.

I am using a stored procedure to update the TransferTaxRate field in a SQL Server 2000 table. The field is a decimal (9,4) field with NULLs allowed.

The form sends the parameter to the stored procedure as VarChar (9).

I don't know enough about datatype conversions to know why the VarChar to Decimal implicit conversion works with numbers, but not NULL values or character data.

Where do I need to make the explicit conversion? On the form or in the stored procedure?

Here is the stored procedure:

CREATE PROCEDURE sproc_UpdateMuniInfo @MuniID Numeric (9), @TaxRate VarChar (9), @Prereg Char (3), @PreregURL VarChar (50),
@DateVerified SmallDateTime AS
UPDATE Locality
SET
TransferTaxRate = @TaxRate,
DeedPreregistration =
    CASE @Prereg
        WHEN 'Yes' THEN 1
        WHEN 'No' THEN 0
        END,
DeedPreregFormURL = @PreregURL,
URLVerified = @DateVerified
WHERE LocaleOID = @MuniID
GO


Here is the form function that updates the database:

Sub Update_Click(Sender As Object, E As EventArgs)
        ' Set County ID
        Dim strUpdateCountyID As String
        strUpdateCountyID = ddlCounty.SelectedItem.Value
        ' Update Municipality info by looping through Repeater object
        Dim Item As RepeaterItem
        Dim intMuniInfoRepeaterIndex As Integer
        For Each Item in objMuniInfoRepeater.Items
                ' Update by ContactID
                Dim strMuniInfoConnection As String = ConfigurationSettings.AppSettings("Locality")
                Dim objMuniInfoConnection As New SqlConnection(strMuniInfoConnection)
                Dim objMuniInfoCommand As New SqlCommand("sproc_UpdateMuniInfo", objMuniInfoConnection)
                objMuniInfoCommand.CommandType = CommandType.StoredProcedure
                Dim objMuniInfoParameter1 As New SqlParameter("@MuniID", SqlDbType.VarChar, 9)
                Dim objMuniInfoParameter2 As New SqlParameter("@TaxRate", SqlDbType.VarChar, 9)
                Dim objMuniInfoParameter3 As New SqlParameter("@Prereg", SqlDbType.Char, 3)
                Dim objMuniInfoParameter4 As New SqlParameter("@PreregURL", SqlDbType.VarChar, 50)
                Dim objMuniInfoParameter5 As New SqlParameter("@DateVerified", SqlDbType.SmalldateTime, 4)
                objMuniInfoCommand.Parameters.Add(objMuniInfoParam eter1)
                objMuniInfoCommand.Parameters.Add(objMuniInfoParam eter2)
                objMuniInfoCommand.Parameters.Add(objMuniInfoParam eter3)
                objMuniInfoCommand.Parameters.Add(objMuniInfoParam eter4)
                objMuniInfoCommand.Parameters.Add(objMuniInfoParam eter5)
                objMuniInfoParameter1.Direction = ParameterDirection.Input
                objMuniInfoParameter2.Direction = ParameterDirection.Input
                objMuniInfoParameter3.Direction = ParameterDirection.Input
                objMuniInfoParameter4.Direction = ParameterDirection.Input
                objMuniInfoParameter5.Direction = ParameterDirection.Input
                Dim objMuniIDLabel As Object
                Dim objTaxRateTextBox As Object
                Dim objPreregTextBox As Object
                Dim objPreregURLTextBox As Object
                Dim objDateVerified AS Object
                objMuniIDLabel = objMuniInfoRepeater.Items(intMuniInfoRepeaterIndex ).FindControl("lblMuniID")
                objTaxRateTextBox = objMuniInfoRepeater.Items(intMuniInfoRepeaterIndex ).FindControl("tbxMuniTaxRate")
                objPreregTextBox = objMuniInfoRepeater.Items(intMuniInfoRepeaterIndex ).FindControl("tbxPreregistration")
                objPreregURLTextBox = objMuniInfoRepeater.Items(intMuniInfoRepeaterIndex ).FindControl("tbxPreregFormURL")
                objDateVerified = objMuniInfoRepeater.Items(intMuniInfoRepeaterIndex ).FindControl("tbxURLDateVerified")
                objMuniInfoParameter1.Value = objMuniIDLabel.Text
                objMuniInfoParameter2.Value = objTaxRateTextBox.Text
                objMuniInfoParameter3.Value = objPreregTextBox.Text
                objMuniInfoParameter4.Value = objPreregURLTextBox.Text
                objMuniInfoParameter5.Value = objDateVerified.Text
                objMuniInfoConnection.Open()
                objMuniInfoCommand.ExecuteReader()
                objMuniInfoConnection.Close()
                intMuniInfoRepeaterIndex = intMuniInfoRepeaterIndex + 1
        Next
        ' Go back to County Update Page
        intCountyID = ddlCounty.SelectedItem.Value
        Response.Redirect("county_update.aspx")
End Sub


Any help would be very much appreciated. I have had a very hard time locating discussions of practical datatype conversions and the processing of NULL values in ASP.NET / SQL Server 2000 applications.

Dave

Can you give an example of the data you are entering for each field(parameter)? That would help find the error.

Yes. The error does not occur if a number or a zero is entered. If the field is left blank in the Tax Rate textbox. So it's the absence of data (a NULL) that causes the procedure to break down.