Solved

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

Posted on 2010-11-10
6
1,470 Views
Last Modified: 2012-05-10
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
Comment
Question by:bobcann
  • 3
  • 2
6 Comments
 
LVL 3

Accepted Solution

by:
tomnorra earned 250 total points
ID: 34103002
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
 
LVL 32

Assisted Solution

by:Erick37
Erick37 earned 250 total points
ID: 34103007
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
 
LVL 3

Expert Comment

by:tomnorra
ID: 34104273
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 32

Expert Comment

by:Erick37
ID: 34104555
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
 

Author Closing Comment

by:bobcann
ID: 34104637
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34104693
Yep, the code was falling through to the error handler and the err.number was Nothing.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now