[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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

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))

as
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)

      BEGIN
      update dbo.mkt_data
      SET
      [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
      WHERE
      [project_id] = @projectID
      END

if @@ROWCOUNT = 0
      
      BEGIN
      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
      END
GO
0
anjitajain
Asked:
anjitajain
  • 7
  • 5
  • 5
1 Solution
 
ptjcbCommented:
Does dbo.mkt_data have an Identity column?
0
 
ptjcbCommented:
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.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
also, prefer SCOPE_IDENTITY() over @@IDENTITY.
I agree with ptjcb that only when the table has an identity field, the identity can be returned.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
anjitajainAuthor Commented:
I have projectID as a autogenerated number.
Should I write the code as @projectID=SCOPE_IDENTITY() instead of @projectID=@@IDENTITY?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes
0
 
anjitajainAuthor Commented:
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.

I used SCOPE_IDENTITY()
0
 
ptjcbCommented:
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?


0
 
anjitajainAuthor Commented:
No, the query should update the record if the @projectID already exists and insert new record if @@ROWCOUNT = 0
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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...
0
 
anjitajainAuthor Commented:
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()
            GetUserName()
            Dim prjID As String = Trim(Request.Form.Item("hidprojectid"))
            If prjID = "" Then
                hidprojectid = 0
            Else
                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(prmProjectID)
            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)

            CmdProject.ExecuteNonQuery()
            intProjectID = prmProjectID.Value
            CmdProject.Parameters.Clear()
            CmdProject.Dispose()
            CmdProject = Nothing
End Sub
0
 
ptjcbCommented:
Please give us the structure of the mkt_data table.
0
 
anjitajainAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
does the field project_id            really have the identity property set to true?
0
 
anjitajainAuthor Commented:
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!
0
 
ptjcbCommented:
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.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in which case, points should go to ptjcb's very first comment, asking that !!
0
 
anjitajainAuthor Commented:
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 :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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