Query to save the record is giving "null" value error

Posted on 2007-10-05
Last Modified: 2010-03-19
I have created the stored procedure to save the record. It keeps giving me error that @projectID is null. In the stored procedure, I have set @projectID=@@IDENTITY is @projectID is null. Any help is greatly appreciated.

 CREATE PROCEDURE dbo.mkt_submit_form_pipeline
(@projectID int output,
@prjName varchar(200),
@Stage varchar(50),
@Type varchar(50),
@MfgLoc varchar(50),
@Market varchar(50),
@Customer varchar(200),
@OEM varchar(200),
@SMth int,
@SYr int,
@EMth int,
@EYr int,
@Prob char(10),
@unitPrice int,
@currency char(10),
@comments varchar(300),
@submituser varchar(50))

declare @stageID integer, @typeID integer, @mfgID integer, @mktID integer, @probID integer, @currencyID integer, @submitDate datetime

set @submitDate = getdate()

if @Stage is not null
      set @stageID= (select  stage_id from dbo.mkt_stage where stage_name=@Stage)
if @Type is not null
      set @typeID= (select type_id from dbo.mkt_type where type_name=@Type)

if @MfgLoc is not null
      set @mfgID= (select mfglocation_id from dbo.mkt_mfglocation where mfglocation_name=@MfgLoc)

if @Market is not null
      set @mktID= (select market_id from dbo.mkt_market where market_name=@Market)

if @Prob is not null
      set @probID= (select prob_id from dbo.mkt_probability where probability=@Prob)

if @currency is not null
      set @currencyID= (select currency_id from dbo.mkt_currency where currency_type=@currency)

      update dbo.mkt_data
      [project_name] = @prjName,
      [customer_name] = @Customer,
      [ultimate_oem] = @OEM,
      [stage_id] = @StageID,
      [market_id] = @mktID,
      [mfglocation_id] = @mfgID,
      [type_id] = @typeID,
      [spa_nt_id] = @submituser,
      [prob_id] = @probID,
      [sop_month_id] = @SMth,
      [sop_year] = @SYr,
      [eop_month_id] = @EMth,
      [eop_year] = @EYr,
      [currency_id] = @CurrencyID,
      [update_date] = @submitDate,
      [comments] = @comments
      [project_id] = @projectID

if @@ROWCOUNT = 0
      insert into dbo.mkt_data
      ([project_name], [customer_name], [ultimate_oem], [stage_id], [market_id], [mfglocation_id], [type_id], [spa_nt_id], [prob_id], [sop_month_id], [sop_year], [eop_month_id], [eop_year], [currency_id], [update_date], [comments])
      values (@prjName, @Customer, @OEM, @stageID, @mktID, @mfgID, @typeID, @submituser, @probID, @SMth, @SYr, @EMth, @EYr, @CurrencyID, @submitDate, @comments)
      SET @projectID=@@IDENTITY
Question by:anjitajain
    LVL 27

    Accepted Solution

    Does dbo.mkt_data have an Identity column?
    LVL 27

    Expert Comment

    I ask because @@IDENTITY contains the last identity value generated by an INSERT, SELECT INTO, or bulk copy statement.

    If your INSERT on dbo.mkt_data is into a table without identity columns, then @@IDENTITY returns a NULL.

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    also, prefer SCOPE_IDENTITY() over @@IDENTITY.
    I agree with ptjcb that only when the table has an identity field, the identity can be returned.

    Author Comment

    I have projectID as a autogenerated number.
    Should I write the code as @projectID=SCOPE_IDENTITY() instead of @projectID=@@IDENTITY?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]

    Author Comment

    it still gives me the same error in my application. see the error below:

    Cannot insert the value NULL into column 'project_id', table 'AATS.dbo.mkt_data'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    LVL 27

    Expert Comment

    Are the other values being inserted?

    Add a local variable to capture @@ROWCOUNT. There is a flaw in your stored procedure because with

    IF @@ROWCOUNT = 0

    you may not do the insert into mkt_data and therefore no return an identity value. Are you sure that you will always have a @@ROWCOUNT = 0?


    Author Comment

    No, the query should update the record if the @projectID already exists and insert new record if @@ROWCOUNT = 0
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >I have projectID as a autogenerated number.
    that actually does not fit with the message:
    >Cannot insert the value NULL into column 'project_id', table 'AATS.dbo.mkt_data';

    can you double/triple check that you look into the same database/table?
    ie it needs to be a int / identity column...

    Author Comment

    I converted the string value (projectID) to integer as it kept giving me error that string cannot be converted to integer
    Here is the code in my application:

     Public Sub submitForm()
                Dim prjID As String = Trim(Request.Form.Item("hidprojectid"))
                If prjID = "" Then
                    hidprojectid = 0
                    hidprojectid = CType(prjID, Int32)
                End If
                'hidprojectid = Trim(Request.Form.Item("hidprojectid"))
                'hidprojectid2 = CType(prjID, Integer)
                txtntid = strUserName
                txtCurrDate = Trim(Request.Form.Item("txtCurrDate"))
                txtPrjName = Trim(Request.Form.Item("txtPrjName"))
                DDStage = Trim(Request.Form.Item("DDStage"))
                DDType = Trim(Request.Form.Item("DDType"))
                DDMfgLoc = Trim(Request.Form.Item("DDMfgLoc"))
                DDMkt = Trim(Request.Form.Item("DDMkt"))
                txtCustomer = Trim(Request.Form.Item("txtCustomer"))
                txtOEM = Trim(Request.Form.Item("txtOEM"))
                DDSOPMonth = Trim(Request.Form.Item("DDSOPMonth"))
                txtSOPYear = Trim(Request.Form.Item("txtSOPYear"))
                DDEOPMonth = Trim(Request.Form.Item("DDEOPMonth"))
                txtEOPYear = Trim(Request.Form.Item("txtEOPYear"))
                DDProb = Trim(Request.Form.Item("DDProb"))
                txtPrice = Trim(Request.Form.Item("txtPrice"))
                DDCurrency = Trim(Request.Form.Item("DDCurrency"))

                Dim CmdProject As New SqlClient.SqlCommand("dbo.mkt_submit_form_pipeline", SqlConn)
                CmdProject.CommandType = CommandType.StoredProcedure
                Dim prmProjectID As New SqlClient.SqlParameter("@projectID", SqlDbType.Int)
                prmProjectID.Direction = ParameterDirection.InputOutput
                prmProjectID.Value = (hidprojectid)
                CmdProject.Parameters.Add("@prjName", SqlDbType.VarChar, 200).Value = makeNull("text", txtPrjName)
                CmdProject.Parameters.Add("@Stage", SqlDbType.VarChar, 50).Value = makeNull("text", DDStage)
                CmdProject.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = makeNull("text", DDType)
                CmdProject.Parameters.Add("@MfgLoc", SqlDbType.VarChar, 50).Value = makeNull("text", DDMfgLoc)
                CmdProject.Parameters.Add("@Market", SqlDbType.VarChar, 50).Value = makeNull("text", DDMkt)
                CmdProject.Parameters.Add("@Customer", SqlDbType.VarChar, 200).Value = makeNull("text", txtCustomer)
                CmdProject.Parameters.Add("@OEM", SqlDbType.VarChar, 200).Value = makeNull("text", txtOEM)
                CmdProject.Parameters.Add("@SMth", SqlDbType.Int).Value = makeNull("text", DDSOPMonth)
                CmdProject.Parameters.Add("@SYr", SqlDbType.Int).Value = makeNull("text", txtSOPYear)
                CmdProject.Parameters.Add("@EMth", SqlDbType.Int).Value = makeNull("text", DDEOPMonth)
                CmdProject.Parameters.Add("@EYr", SqlDbType.Int).Value = makeNull("text", txtEOPYear)
                CmdProject.Parameters.Add("@Prob", SqlDbType.Char, 10).Value = makeNull("text", DDProb)
                CmdProject.Parameters.Add("@unitPrice", SqlDbType.Int).Value = makeNull("text", txtPrice)
                CmdProject.Parameters.Add("@currency", SqlDbType.Char, 10).Value = makeNull("text", DDCurrency)
                CmdProject.Parameters.Add("@comments", SqlDbType.VarChar, 300).Value = makeNull("text", txtComments)
                CmdProject.Parameters.Add("@submituser", SqlDbType.VarChar, 50).Value = makeNull("text", strUserName)

                intProjectID = prmProjectID.Value
                CmdProject = Nothing
    End Sub
    LVL 27

    Expert Comment

    Please give us the structure of the mkt_data table.

    Author Comment

    Here is the structure of the table

    Column Name               Data Type        Length    Primary/Foreign Key
        project_id                   int                   4                  x
        project_name             varchar               75

        customer_name         varchar               75
        ultimate_oem              varchar               75

        stage_id                     int                   4                    x
        market_id                   int                   4                   x

        mfglocation_id            int                   4                     x
        type_id                       int                   4                    x

        spa_nt_id                 varchar               100            x
        prob_id                   tinyint               1                    x

        sop_month_id              int                   4                x
        sop_year                  int                   4    
        eop_month_id              int                   4                x

        eop_year                  int                   4
        unit_price                numeric               9

        currency_id               int                   4                     x
        update_date               datetime              8

        comments                  varchar               100
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    does the field project_id            really have the identity property set to true?

    Author Comment

    Ops... No, the Identity property was not turned on. Sorry. I just turned it on. I'll try and see if this solves the problem. I really appreciate your help!
    LVL 27

    Expert Comment

    Laughing, good to know that the problem is (probably) solved.

    As I said in my second message:

    If your INSERT on dbo.mkt_data is into a table without identity columns, then @@IDENTITY returns a NULL.

    and angelIII has asked several times if the column really is an identity colum.

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    in which case, points should go to ptjcb's very first comment, asking that !!

    Author Comment

    I am sorry for the confusion. I have not worked a lot on sql. Thanks a lot for all your help!! It solved my problem :)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    734 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

    21 Experts available now in Live!

    Get 1:1 Help Now