• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1696
  • Last Modified:

ADO.NET "Argument 'Number' is not a valid value." << "Number" Does Not Exist

No, it is not a typo error in my code. There are no numbers in the parameters, so this came out of nowhere. The parameter "Number" is nowhere to be found.  This is odd and I have no idea how to fix this. I tried dropping the stored proc and re-creating, but the error persists.

Can anyone offer a suggestion on how ti fix this? Code is below.

Thanks for any help.


"System.ArgumentException: Argument 'Number' is not a valid value.
   at Microsoft.VisualBasic.ErrObject.Raise(Int32 Number, Object Source, Object Description, Object HelpFile, Object HelpContext)
   at Personnel.EmployeeEmergencyContactsUpdate(String strSSN, String strEmergencyRelation, String strEmergencyName, String strEmergencyAddress, String strEmergencyCity, String strEmergencyState, String strEmergencyZipCode, String strEmergencyApartment, String strEmergencyPhone, String strEmergencyWorkPhone, String strEmergencyWorkPhoneExt, String strEnteredBy) in C:\Documents\STS_Rewrite\STS (to DOT(60FE).NET) ()\App_Code\Personnel.vb:line 1472
   at WebService._EmployeeEmergencyContactsUpdate() in C:\Documents\STS_Rewrite\STS (to DOT(60FE).NET) ()\App_Code\WebService.vb:line 85"
VB.NET-----------------------------------------

Public Sub EmployeeEmergencyContactsUpdate( _
        ByVal strSSN As String, _
        ByVal strEmergencyRelation As String, _
        ByVal strEmergencyName As String, _
        ByVal strEmergencyAddress As String, _
        ByVal strEmergencyCity As String, _
        ByVal strEmergencyState As String, _
        ByVal strEmergencyZipCode As String, _
        ByVal strEmergencyApartment As String, _
        ByVal strEmergencyPhone As String, _
        ByVal strEmergencyWorkPhone As String, _
        ByVal strEmergencyWorkPhoneExt As String, _
        ByVal strEnteredBy As String)

        On Error GoTo ErrorTrap

        Dim cmd As New SqlCommand
        cmd.CommandText = "ps__employee_emergency_contacts_UPDATE_ContactInfo"
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@ssn", strSSN)
        cmd.Parameters.AddWithValue("@emergency_relation", strEmergencyRelation)
        cmd.Parameters.AddWithValue("@emergency_name", strEmergencyName)
        cmd.Parameters.AddWithValue("@emergency_address", strEmergencyAddress)
        cmd.Parameters.AddWithValue("@emergency_city", strEmergencyCity)
        cmd.Parameters.AddWithValue("@emergency_state", strEmergencyState)
        cmd.Parameters.AddWithValue("@emergency_zipcode", strEmergencyZipCode)
        cmd.Parameters.AddWithValue("@emergency_apartment", strEmergencyApartment)
        cmd.Parameters.AddWithValue("@emergency_phone", strEmergencyPhone)
        cmd.Parameters.AddWithValue("@emergency_work_phone", strEmergencyWorkPhone)
        cmd.Parameters.AddWithValue("@emergency_work_phone_ext", strEmergencyWorkPhoneExt)
        cmd.Parameters.AddWithValue("@entered_by", strEnteredBy)

        Dim cnn As New Connection
        cnn.OpenConnection()
        cmd.Connection = cnn.GetConnection
        cmd.ExecuteNonQuery().ToString()
        cnn.CloseConnection()

ErrorTrap:
        Err.Raise(Err.Number, "Personnel.EmployeeEmergencyContactsUpdate > " & Err.Source, Err.Description)
        On Error GoTo 0
    End Sub

SP----------------------------------

CREATE PROCEDURE [dbo].[ps__employee_emergency_contacts_UPDATE_ContactInfo]
(
@ssn					char(11),
@emergency_relation			char(2),
@emergency_name				varchar(36),
@emergency_address			varchar(36),
@emergency_city				varchar(20),
@emergency_state			char(2),
@emergency_zipcode			varchar(10),
@emergency_apartment		        varchar(4),
@emergency_phone			varchar(12),
@emergency_work_phone		        varchar(12),
@emergency_work_phone_ext	        varchar(7),
@entered_by				char(7)
)
AS
UPDATE employee_emergency_contacts
SET	emergency_relation = @emergency_relation,
	emergency_name = @emergency_name,
	emergency_address = @emergency_address,
	emergency_city = @emergency_city,
	emergency_state = @emergency_state,
	emergency_zipcode = @emergency_zipcode,
	emergency_apartment = @emergency_apartment,
	emergency_phone = @emergency_phone,
	emergency_work_phone = @emergency_work_phone,
	emergency_work_phone_ext = @emergency_work_phone_ext,
	entered_by = @entered_by
WHERE
	ssn = @ssn

Open in new window

0
bobcann
Asked:
bobcann
  • 3
  • 2
2 Solutions
 
tomnorraCommented:
your issue may very well be your Parameters.AddWithValue and adding the SSN, phone, extension and zip code all being numbers and SQL translating that into numerical instead of varchars. Try changing this  cmd.Parameters.AddWithValue("@emergency_zipcode", strEmergencyZipCode)
to
            Dim prmZip As New SqlParameter("@emergency_zipcode", SqlDbType.Char)
            prmContent.Value = strEmergencyZipCode
            cmd.Parameters.Add(prmContent)
And so on and so forth for each parameter that SQL can miscontrue as a number.

I know it doesn't look much different to but to SQL it does.
0
 
Erick37Commented:
I would get rid of the ON ERROR syntax and replace it with .Net's Try..Catch.
	Public Sub EmployeeEmergencyContactsUpdate( _
		   ByVal strSSN As String, _
		   ByVal strEmergencyRelation As String, _
		   ByVal strEmergencyName As String, _
		   ByVal strEmergencyAddress As String, _
		   ByVal strEmergencyCity As String, _
		   ByVal strEmergencyState As String, _
		   ByVal strEmergencyZipCode As String, _
		   ByVal strEmergencyApartment As String, _
		   ByVal strEmergencyPhone As String, _
		   ByVal strEmergencyWorkPhone As String, _
		   ByVal strEmergencyWorkPhoneExt As String, _
		   ByVal strEnteredBy As String)

		Try
			Dim cmd As New SqlCommand
			cmd.CommandText = "ps__employee_emergency_contacts_UPDATE_ContactInfo"
			cmd.CommandType = CommandType.StoredProcedure

			cmd.Parameters.AddWithValue("@ssn", strSSN)
			cmd.Parameters.AddWithValue("@emergency_relation", strEmergencyRelation)
			cmd.Parameters.AddWithValue("@emergency_name", strEmergencyName)
			cmd.Parameters.AddWithValue("@emergency_address", strEmergencyAddress)
			cmd.Parameters.AddWithValue("@emergency_city", strEmergencyCity)
			cmd.Parameters.AddWithValue("@emergency_state", strEmergencyState)
			cmd.Parameters.AddWithValue("@emergency_zipcode", strEmergencyZipCode)
			cmd.Parameters.AddWithValue("@emergency_apartment", strEmergencyApartment)
			cmd.Parameters.AddWithValue("@emergency_phone", strEmergencyPhone)
			cmd.Parameters.AddWithValue("@emergency_work_phone", strEmergencyWorkPhone)
			cmd.Parameters.AddWithValue("@emergency_work_phone_ext", strEmergencyWorkPhoneExt)
			cmd.Parameters.AddWithValue("@entered_by", strEnteredBy)

			Dim cnn As New Connection
			cnn.OpenConnection()
			cmd.Connection = cnn.GetConnection
			cmd.ExecuteNonQuery().ToString()
			cnn.CloseConnection()

		Catch ex As Exception

			Throw New Exception("Personnel.EmployeeEmergencyContactsUpdate > " & ex.Message)

		End Try

	End Sub

Open in new window

0
 
tomnorraCommented:
typo in my last comment. This is what I meant to put. Forgot to change everything to your terminology.

           Dim prmZip As New SqlParameter("@emergency_zipcode", SqlDbType.Char)
            prmZip.Value = strEmergencyZipCode
            cmd.Parameters.Add(prmZip)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Erick37Commented:
I'm still thinking the error in in the error handler:

Err.Raise(Err.Number, ...

The Raise method's first parameter is named Number.

If you test this
Err.Raise(65538, "Test")
you will get the error message "Argument 'Number' is not a valid value."
0
 
bobcannAuthor Commented:
Both solutions worked.

tomnorra: Your solution worked in spite of my omitting

        On Error GoTo 0
        Exit Sub

Erick37: I missed that omission--DOH :)

Thanks guys.

-Bob
0
 
Erick37Commented:
Yep, the code was falling through to the error handler and the err.number was Nothing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now