[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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