T-SQL STORED PROCEDURE HELP? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL STORED PROCEDURE HELP?

Author

Topic

losstww
Starting Member

13 Posts

Posted - 2009-03-07 : 21:07:19

I have a asp.net web page that a user submits via a stored procedure. When I submit the page I get an error message "Cannot insert the value Null into Column "CaseID, Table AssetTracking';" Column does not allow nulls. If I go into the AssetTracking Table and allow nulls to Column CaseID I do not get the error message but two records are inserted into the table - a null CaseID and the CaseID from the set @CaseID = SCOPE_IDENTITY()

How should I change the stored procedure to get it to work correctly?

Any help would be appreciated!

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:

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", DrownList1.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("@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

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-07 : 23:15:09

As replied on SSC ... http://www.sqlservercentral.com/Forums/Topic670978-338-1.aspx

Most likely trigger; aka the transaction got rolled back. Because SCOPE_IDENTITY is null when there has not been an insert with in the scope...

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 13:10:50

check if you've trigger on tables and also put a SELECT @CaseID after place where ytou get value to check its correctly getting value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources