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

help returning a value from a stored procedure

I have a stored procedure written in sql server. In my ASP.NET page I am using objcmd.executescalar() to run the stored procedure which inserts a row into a database and then should return the contactid of the record just inserted. When I execute the stored procedure I get the error "Input string was not in a correct format". I have looked at the values of all the parameters and they all look correct. The stored procedure was working fine until I started trying to return the contactid. Before I tried to return the contactid I was using objcmd.executenonquery to just do an insert. I have posted the relevant code below. Any ideas?

THE ASP.NET CODE:
Public Sub AddSurveyContact(ByVal objContact As SurveyContactDetails)
        Dim objCmd As New SqlCommand("Insert_SurveyContact1", objConn)
        objCmd.CommandType = CommandType.StoredProcedure
        'Dim objCmdID As New SqlCommand("select max(contactid) from contacts", objConn)
        Dim objCmdID As New SqlCommand("GetContactIDByPhone", objConn)
        Dim x As Integer

        Dim objParam As New SqlParameter("@firstname", SqlDbType.Int)
        objParam.Value = objContact.FirstName
        objCmd.Parameters.Add(objparam)

        objParam = New SqlParameter("@LastName", SqlDbType.VarChar)
        objParam.Value = objContact.LastName
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@Address1", SqlDbType.VarChar)
        objParam.Value = objContact.Address1
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@Address2", SqlDbType.VarChar)
        objParam.Value = objContact.Address2
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@City", SqlDbType.VarChar)
        objParam.Value = objContact.City
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@StateCode", SqlDbType.VarChar)
        objParam.Value = objContact.StateCode
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@zip", SqlDbType.VarChar)
        objParam.Value = objContact.Zip
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@homephone", SqlDbType.VarChar)
        objParam.Value = objContact.HomePhone
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@email", SqlDbType.VarChar)
        objParam.Value = objContact.Email
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@age", SqlDbType.TinyInt)
        objParam.Value = objContact.Age
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@race", SqlDbType.VarChar)
        objParam.Value = objContact.Race
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@Gender", SqlDbType.VarChar)
        objParam.Value = objContact.Gender
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@children", SqlDbType.Bit)
        objParam.Value = objContact.HasChildren
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@NumberOfChildren", SqlDbType.TinyInt)
        objParam.Value = objContact.NumberOfChildren
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@occupation", SqlDbType.VarChar)
        objParam.Value = objContact.Occupation
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@salaryRangeStart", SqlDbType.Money)
        objParam.Value = objContact.SalaryRangeStart
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@SalaryRangeEnd", SqlDbType.Money)
        objParam.Value = objContact.SalaryRangeEnd
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@LocationID", SqlDbType.Int)
        objParam.Value = objContact.LocationID
        objCmd.Parameters.Add(objParam)

        objParam = New SqlParameter("@contactid", SqlDbType.Int)
        objParam.Value = CInt("0")
        objCmd.Parameters.Add(objParam)

        Try
            objConn.Open()
            objContact.ContactID = CType(objCmd.ExecuteScalar(), Integer)     <<< --------- ERROR ON THIS LINE

        Catch ex As Exception
            Throw ex
        End Try

 THE STORED PROCEDURE:

set quoted_identifier off
go
set ansi_nulls on
go

drop procedure insert_surveycontact1;
go

create procedure Insert_SurveyContact1
@Firstname varchar(25),
@lastname varchar(25),
@address1 varchar(50),
@address2 varchar(50),
@city varchar(25),
@statecode varchar(2),
@zip varchar(5),
@homephone varchar(15),
@Email varchar(50),
@age tinyint,
@race varchar(50),
@gender varchar(25),
@children bit,
@numberofchildren tinyint,
@occupation varchar(50),
@salaryrangestart money,
@salaryrangeend money,
@locationid int,
@contactid int output
as
insert into contacts(firstname,lastname,address1,address2,city,statecode,zip,homephone,email,age,race,gender,children,numberofchildren,occupation,salaryrangestart,salaryrangeend,locationid)
values(@firstname,@lastname,@address1,@address2,@city,@statecode,@zip,@homephone,@email,@age,@race,@gender,@children,@numberofchildren,@occupation,@salaryrangestart,@salaryrangeend,@locationid)

select @contactID = contactid from contacts where homephone = @homephone and locationid = @locationid
return
go
0
jkwasson
Asked:
jkwasson
2 Solutions
 
YZlatCommented:
Try
  Dim temp as integer
            objConn.Open()
            temp=objCmd.ExecuteScalar()        
           objContact.ContactID =temp
Catch ex As Exception
            Throw ex
        End Try
0
 
jkwassonAuthor Commented:
tried that and it gives me the same error on the executescalar command.
0
 
HavaganCommented:
Use your existing param object (and the procedure's output var).

objParam = New SqlParameter("@contactid", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output 'set the direction
objCmd.Parameters.Add(objParam)
...
'retrieve the value
objContact.ContactID = Convert.ToIn32(objCmd.Parameters("@contactid").Value)

Paul
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
jkwassonAuthor Commented:
still got the same error. I am beginning to think I should retract my statement that all my input vars are ok. I went back to my original stored procedure and it is giving me the same errors...although I have not changed those input vars since I ran in originally. What is the best way to check those input varialbes at run time to see if they have the values I think they should have?

Thanks,
jk
0
 
jkwassonAuthor Commented:
ok, I found it. It was an input var. I had changed the order and was sending one of the parameters as an int when it should have been a varchar. Both of you guys helped me work through this problem, so I am splitting the points. Thanks for your help!

jk
0
 
Maulik ModiCommented:
ok, I am just replying to your last concern....."What is the best way to check those input varialbes at run time to see if they have the values I think they should have?"

You can debug them if you are using Visual Studio by built-in utility called Server Explorer

Check for null value before accessing directly by comparing it with System.DBNull.VAlue and then use convert function..

Regards
MaulikCE
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

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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