Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Not getting identity returned


Below is my stored proc that is inserting but is not returning the identity
value.  Below is the sp. Can anyone see what is wrong?  Thanks.

CREATE PROCEDURE [dbo].[rfp_insProperty]

(@Address nvarchar(100),

@BuildingName nvarchar(100) = NULL,

@BuildingPark nvarchar(50) = NULL,

@Submarket varchar(100) = NULL,

@City nvarchar(40) = NULL,

@State varchar(2) = 'WI',

@ZipCode varchar(50) = NULL,

@County varchar(50) = NULL,

@GroupCode char(1) = NULL,

@LeaseSaleCode char(1) = NULL,

@newPropID int output)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statement for creating Pproperties record

INSERT INTO [RFPData].[dbo].[Properties]

([Address], [BuildingName], [BuildingPark], [Submarket], [City], [State],
[ZipCode], [County], [GroupCode], [LeaseSaleCode])

VALUES

(@Address, @BuildingName, @BuildingPark, @Submarket, @City, @State,
@ZipCode, @County, @GroupCode, @LeaseSaleCode);

SELECT @newPropID = CAST(SCOPE_IDENTITY() AS int);

END

David

Are you expecting to SELECT the value from @newPropID, or are you properly
using it as an OUTPUT?  Can you show the code where the value "is not
returning"?

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"David C" <dlch@lifetimeinc.com> wrote in message

news:OrQudaupHHA.4544@TK2MSFTNGP02.phx.gbl...

It is ASP.Net and below is the code.

Using conRFPData

cmd = New SqlCommand

cmd.CommandText = "rfp_insProperty"

cmd.CommandType = Data.CommandType.StoredProcedure

cmd.Connection = conRFPData

cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar)

cmd.Parameters("@Address").Value = txtAddress.Text

cmd.Parameters.Add("@BuildingName", Data.SqlDbType.VarChar)

cmd.Parameters("@BuildingName").Value = txtBuildingName.Text

cmd.Parameters.Add("@BuildingPark", Data.SqlDbType.VarChar)

cmd.Parameters("@BuildingPark").Value = txtBuildingPark.Text

cmd.Parameters.Add("@Submarket", Data.SqlDbType.VarChar)

cmd.Parameters("@Submarket").Value = ddlSubmarket.SelectedValue

cmd.Parameters.Add("@City", Data.SqlDbType.VarChar)

cmd.Parameters("@City").Value = txtCity.Text

cmd.Parameters.Add("@State", Data.SqlDbType.VarChar)

cmd.Parameters("@State").Value = txtState.Text

cmd.Parameters.Add("@ZipCode", Data.SqlDbType.VarChar)

cmd.Parameters("@ZipCode").Value = txtZipCode.Text

cmd.Parameters.Add("@County", Data.SqlDbType.VarChar)

cmd.Parameters("@County").Value = txtCounty.Text

cmd.Parameters.Add("@GroupCode", Data.SqlDbType.Char)

cmd.Parameters("@GroupCode").Value = ddlGroupCode.SelectedValue

cmd.Parameters.Add("@LeaseSaleCode", Data.SqlDbType.Char)

cmd.Parameters("@LeaseSaleCode").Value = ddlLeaseSaleCode.SelectedValue

cmd.Parameters.Add("@newPropID", Data.SqlDbType.Int)

cmd.Parameters("@newPropID").Value = 0

Try

conRFPData.Open()

newPropID = Convert.ToInt32(cmd.ExecuteScalar())

Catch ex As Exception

LblMsg.Text = "Adding the new property failed due to error below<br />" &
ex.Message

End Try

End Using

LblMsg.Text = "New PropertyID = " & newPropID.ToString

The LblMsg.Text returns "New PropertyID = 0"

David
"Aaron Bertrand [SQL Server MVP]" <ten.@dnartreb.noraa> wrote in message
news:e5uFFeupHHA.4196@TK2MSFTNGP06.phx.gbl...

In the ASP.NET code you have to set the @newProdID parameter to an output
parameter.  I am not sure of the property but it should be on the parameter
object.

"David C" <dlch@lifetimeinc.com> wrote in message

news:eWqT%23iupHHA.1244@TK2MSFTNGP04.phx.gbl...

David C (dlch@lifetimeinc.com) writes:
> newPropID = Convert.ToInt32(cmd.ExecuteScalar())

Here you are expecting the id to be returned as a result set, but
the procedure is written to return in an output parameter.

Change the above to:

  newProdId = cmd.Parameters("@newPropID").Value

You also need

   cmd.Parameters("@newPropID").Direction = ParameterDirection.InputOutput

(You need to check the name of the enumeration class. I did it from
memory, and I don't program SqlClient that often.)

--
Erland Sommarskog, SQL Server MVP, esq@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc