Parameter SP return value problem 2

[Dashley]

I'm passing 2 params to a Stored Procedure bla bla bla.

I know the SP works as I can call it in a sql editor using
exec sp_isintrastate2 37923, 38544
It returns a correct value at every time.

The params are populating correctly. I can view them with a watch

When I call the sp in the code below I get a -1 back.
I can't seem to find what I goofed on. Can I get another set of eyes on this code HA.

Thanks

    Public Shared Function ckintrastate(ByVal fzip As String, ByVal tzip As String) As Int32
        Dim isintra As Int32 = 5
        Dim sqlconn As New SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings("fmsconn"))
        Dim zipfrom As New SqlParameter
        Dim zipto As New SqlParameter
        Dim cmdintra As New SqlCommand

        With cmdintra
            .CommandText = ("sp_isintrastate2")
            .Connection = sqlconn
            .CommandType = CommandType.StoredProcedure
        End With

        With zipfrom
            .Direction = ParameterDirection.Input
            .DbType = DbType.Int32
            .Value = fzip
            .ParameterName = ("zipfrom")
        End With

        With zipto
            .Direction = ParameterDirection.Input
            .DbType = DbType.Int32
            .Value = tzip
            .ParameterName = ("zipto")
        End With

        cmdintra.Parameters.Add(zipfrom)
        cmdintra.Parameters.Add(zipto)

        sqlconn.Open()

        Try
            isintra = cmdintra.ExecuteNonQuery

        Catch ex As System.Exception
            sqlconn.Close()

        End Try

        If sqlconn.State = ConnectionState.Open Then
            sqlconn.Close()
        End If

        Return isintra
    End Function

 

[ArtieChoke]

The return value is for the number of rows affected by an insert, delete or update statement. Otherwise it's a -1. What are you trying to get back?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[jmeckley]

you never dispose of your connection object, this will lead to problems when an exception is thrown.
simple example:

using(SqlConnection cnn = new SqlConnection(...))
using(SqlCommand cmd = new SqlCommand(cnn))
{
   cmd.CommandType = CommandType.StoredProc;
   cmd.CommandText = "exec stored_procedure @foo, @bar"
   cmd.Parameters.AddWithValue("foo", value1);
   cmd.Parameters.AddWithValue("bar", value2);
   
   cnn.Open();
   cmd.ExecuteNonQuery();
}

note you don't need to explicitly close/dispose the connection, this is done at the closing brace.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

If you are just getting one value back, you need to use ExecuteScalar, otherwise, you will need to use output parameters.

 

[Dashley]

I first tried it with executescalar and then tried using a DR and finally tried the ExecuteNonQuery.

I'm trying to return a value of zero or 1. I'm getting a -1 back regarledd of whichI use though I wanted the scalsr.

The SP is below

CREATE PROCEDURE [dbo].[sp_isintrastate2]
	@zipfrom int,
	@zipto int
AS

DECLARE @statefrom varchar(75)
DECLARE @stateto varchar(75)
DECLARE @RTNVAL INT

BEGIN

	SET NOCOUNT ON;

 	SELECT @statefrom = (SELECT distinct(state) from dbo.uszips where zipcode = @zipfrom)
	SELECT @stateto = (SELECT distinct(state) from dbo.uszips where zipcode = @zipto)

IF @statefrom = @stateto
	Begin
		SELECT @RTNVAL = 0
	end
else
	Begin
		SELECT @RTNVAL = 1
	end
--PRINT @RTNVAL
RETURN @RTNVAL
END

 

[jmeckley]

this could be alot simpler.

select distinct state from dbo.uszips where zipcode = @zipfrom or zipcode = @zipto

DataTable results = new DataTable();
results.Load(command.ExecuteReader());
return results.Rows.Count != 2;

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

Return values are NOT the same as OUTPUT parameters. You need to use an output param.

 

[Dashley]

Ok I modifired the code with using a return parameter but always got a zero back. So I modified the SP by adding a return param and taking the "IF" logic out and just running a clean select statement. Its working now Thanks to you both.

If a 1 is returned I know its an itrasate shipment. Anything else is a Intrastate.

        cmdintra.Parameters.AddWithValue("zipfrom", fzip)
        cmdintra.Parameters.AddWithValue("zipto", tzip)
        cmdintra.Parameters.AddWithValue("@rtnval", 0)

        Try
            isintra = cmdintra.ExecuteScalar()

        Catch ex As System.Exception

CREATE PROCEDURE [dbo].[sp_isintrastate2]
	@zipfrom int,
	@zipto int,
	@rtnval int OUTPUT
AS

BEGIN

	SET NOCOUNT ON;
SELECT     COUNT(DISTINCT state) AS Expr1
FROM         uszips
WHERE     (ZipCode = @zipfrom) OR
                      (ZipCode = @zipto)
 
END

 

[jmeckley]

you may also want to add logic to ensure that both zipcodes are valid (exist). right now if 1 is invalid your count will return 1 thus appearing that the zip codes are in the same state. or if both are invalid you return 0 which shouldn't be possible with valid zipcodes.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Dashley]

I've trapped that earlier in the process before it ever gets to this SP. Guess it wouldn't hurt to put it here either. Nothing like bullet proof HA. Thanks