March 7, 2009 at 1:47 pm
I'm trying to insert data into multiple tables with a stored procedure using @CaseID=SCOPE_IDENTITY. When I submit data I get an error message that CaseID in AssetTracking cannot be Null. If I change the CaseID in the AssetTracking Table to allow Nulls and sumit the data I get two records into the AssetTracking table - a Null Record in the CaseID and a record with the correct CaseID generated by SCOPE_IDENTITY.
What is causing this problem and how do I fix it?
Stored Procedure:
ALTER PROCEDURE dbo.CaseDataInsert
@ReportType varchar(50), @createdby varchar(50), @OpenDate smalldatetime, @Territory varchar(10),
@Region varchar(10), @StoreNumber varchar(10), @StoreAddress varchar(200), @TiplineID varchar(50),
@status varchar(50), @CaseType varchar(200), @Offense varchar(200), @CaseID int, @Subject varchar(50), @LastName varchar(50), @FirstName varchar(50), @MiddleInitial varchar(10), @IDType varchar(50), @IDNumber varchar(50), @HireDate smallDateTime, @TermDate smallDateTime, @LastDayWorked smallDateTime, @Phone varchar(50), @Cell varchar(50), @Email varchar(50), @Email2 varchar(50), @JobTitle varchar(50), @Address varchar(200), @City varchar(50), @State varchar(10), @Zip varchar(10), @Sex varchar(10), @BirthDate smallDateTime, @Age varchar(10), @Height varchar(10), @Weight varchar(10), @Eyes varchar(50), @HairColor varchar(50), @Ethnicity varchar(50),@AssetType varchar(50), @Description varchar(200), @Qty varchar(50), @SKU varchar(50), @Saleable varchar(10), @Recovered varchar(10), @Retail smallmoney
AS
IF NOT EXISTS (SELECT 1 FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense)
BEGIN
INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,
Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense)
VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,
@StoreNumber,@StoreAddress,@TiplineID,@Status,@CaseType,@Offense)
SET @CaseID=SCOPE_IDENTITY()
End
ELSE
BEGIN
SELECT @CaseID=CaseId FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense
END
IF NOT EXISTS (SELECT 1 FROM Subject WHERE CaseID=@CaseID AND Subject=@Subject AND LastName=@LastName AND FirstName=@FirstName AND MiddleInitial=@MiddleInitial AND IDType=@IDType AND IDNumber=@IDNumber AND HireDate=@HireDate AND TermDate=@TermDate AND LastDayWorked=@LastDayWorked AND Phone=@Phone AND Cell=@Cell AND Email=@Email AND Email2=@Email2 AND JobTitle=@JobTitle AND Address=@Address AND City=@City AND State=@State AND Zip=@Zip AND Sex=@Sex AND BirthDate=@BirthDate AND Age=@Age AND Height=@Height AND Weight=@Weight AND Eyes=@Eyes AND HairColor=@HairColor AND Ethnicity=@Ethnicity)
BEGIN
INSERT Subject(CaseID, Subject, LastName, FirstName, MiddleInitial, IDTYpe, IDNumber, HireDate, TermDate, LastDayWorked, Phone, Cell, Email, Email2, JobTitle, Address, City, State, Zip, Sex, BirthDate, Age, Height, Weight, Eyes, HairColor, Ethnicity)
VALUES (@CaseID, @Subject, @LastName, @FirstName, @MiddleInitial, @IDType, @IDNumber, @HireDate, @TermDate, @LastDayWorked, @Phone, @Cell, @Email, @Email2, @JobTitle, @Address, @City, @State, @Zip, @Sex, @BirthDate, @Age, @Height, @Weight, @Eyes, @HairColor, @Ethnicity)
End
IF NOT EXISTS(SELECT 1 FROM AssetTracking WHERE @CaseID=CaseID AND AssetType=@AssetType AND Description=@Description AND Qty=@Qty AND SKU=@SKU AND Saleable=@Saleable AND Recovered=@Recovered AND Retail=@Retail)
BEGIN
INSERT AssetTracking(CaseID, AssetType, Description, Qty, SKU, Saleable, Recovered, Retail)
VALUES (@CaseID, @AssetType, @Description, @Qty, @SKU, @Saleable, @Recovered, @Retail)
End
Code to Submit Data to Database:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Message.Text = ""
Dim cs As String = "Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\Database2.mdf;Integrated Security=True;User Instance=True"
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "CaseDataInsert"
cmd.Parameters.AddWithValue("@ReportType", DropDownList1.SelectedValue)
cmd.Parameters.AddWithValue("@CreatedBy", DropDownList2.SelectedValue)
cmd.Parameters.AddWithValue("@OpenDate", TextBox1.Text)
cmd.Parameters.AddWithValue("@Territory", TextBox2.Text)
cmd.Parameters.AddWithValue("@Region", DropDownList3.SelectedValue)
cmd.Parameters.AddWithValue("@StoreNumber", TextBox3.Text)
cmd.Parameters.AddWithValue("@StoreAddress", TextBox4.Text)
cmd.Parameters.AddWithValue("@TiplineID", TextBox5.Text)
cmd.Parameters.AddWithValue("@Status", DropDownList4.SelectedValue)
cmd.Parameters.AddWithValue("@CaseType", DropDownList5.SelectedValue)
cmd.Parameters.AddWithValue("@Offense", DropDownList6.SelectedValue)
cmd.Parameters.AddWithValue("@CaseID", TextBox6.Text)
cmd.Parameters.AddWithValue("@Subject", tbFirstName.Text)
cmd.Parameters.AddWithValue("@LastName", tbLastName.Text)
cmd.Parameters.AddWithValue("@FirstName", tbFirstName.Text)
cmd.Parameters.AddWithValue("@MiddleInitial", tbInitial.Text)
cmd.Parameters.AddWithValue("@IDType", ddlIDType.SelectedValue)
cmd.Parameters.AddWithValue("@IDNumber", tbIDNumber.Text)
cmd.Parameters.AddWithValue("@HireDate", tbHireDate.Text)
cmd.Parameters.AddWithValue("@TermDate", tbTermDate.Text)
cmd.Parameters.AddWithValue("@LastDayWorked", tbLDW.Text)
cmd.Parameters.AddWithValue("@Phone", tbPhone.Text)
cmd.Parameters.AddWithValue("@Cell", tbCell.Text)
cmd.Parameters.AddWithValue("@Email", tbEmail.Text)
cmd.Parameters.AddWithValue("@Email2", tbEmail2.Text)
cmd.Parameters.AddWithValue("@JobTitle", ddlJobTitle.SelectedValue)
cmd.Parameters.AddWithValue("@Address", tbAddress.Text)
cmd.Parameters.AddWithValue("@City", tbCity.Text)
cmd.Parameters.AddWithValue("@State", tbState.Text)
cmd.Parameters.AddWithValue("@Zip", tbZip.Text)
cmd.Parameters.AddWithValue("@Sex", ddlSex.SelectedValue)
cmd.Parameters.AddWithValue("@BirthDate", tbBirthDate.Text)
cmd.Parameters.AddWithValue("@Age", tbAge.Text)
cmd.Parameters.AddWithValue("@Height", tbHeight.Text)
cmd.Parameters.AddWithValue("@Weight", tbWeight.Text)
cmd.Parameters.AddWithValue("@Eyes", ddlEyes.SelectedValue)
cmd.Parameters.AddWithValue("@HairColor", ddlHairColor.SelectedValue)
cmd.Parameters.AddWithValue("@Ethnicity", ddlEthnicity.SelectedValue)
cmd.Parameters.AddWithValue("@AssetType", AssetType_0.SelectedValue)
cmd.Parameters.AddWithValue("@Description", Description_0.Text)
cmd.Parameters.AddWithValue("@Qty", Qty_0.Text)
cmd.Parameters.AddWithValue("@SKU", SKU_0.Text)
cmd.Parameters.AddWithValue("@Saleable", Saleable_0.SelectedValue)
cmd.Parameters.AddWithValue("@Recovered", Recovered_0.SelectedValue)
cmd.Parameters.AddWithValue("@Retail", Retail_0.Text)
cmd.ExecuteNonQuery()
con.Close()
cmd.Dispose()
Message.Text = "Record has been inserted into Database."
End Using
End Sub
March 7, 2009 at 8:30 pm
Check for triggers on the AssetTracking table.
You may also find that you will get more responses if you bother to format your SQL code a little, like your VB code is formatted.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2009 at 6:46 am
I don't think your issue is with SCOPE_IDENTITY(). I'm more inclined to think that you have an issue with the your first ELSE.
Can you post the DDL for the tables? If you allow Nulls in any of the columns then you could potentially fall through to your ELSE and not get a CaseId.
I'd verify that I was not getting a Null value for @CaseId from my Select in the first ELSE.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2009 at 8:28 am
Good point, Jack. I had assumed that CaseID was Ok in the table since it appears to be set by the procedure, but it's not necessarily so. Best to check that too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2009 at 6:04 pm
CaseID is inserted into the first two tables correctly, but I get the error when I try to submit data entered into the third table. I changed the third table to another table and I get the same problem. I have to set allow nulls in the CaseID column of the third table. When I submit I get two identical records except for a NULL value in one records and the correct CaseID in the other.
AssetTracking Table:
AssetID int identity(1,1)
CaseID int NOT NULL
AssetType varchar(50) NOT NULL
Description varchar(200) NULL
Qty varchar(50) NOT NULL
SKU varchar(50) NULL
Saleable varchar(10) NOT NULL
Recoverd varchar(10) NOT NULL
Retail smallmoney NOT NULL
TotalRetail computed column ([Qty]*[Retail])
Recovery computed column (case when[Saleable]='Yes' AND [Recovered]='Yes' then [Qty]*[Retail] else (0) end)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply