Solve Null Value Error? - 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

.NET Inside SQL Server (2005)

Solve Null Value Error?

Author

Topic

losstww
Starting Member

13 Posts

Posted - 2009-03-04 : 21:06:15

I have a asp.net web page that a user submits via a stored procedure. When I submit the page on get an error message "Cannot insert the value Null into Column "CaseID, Table AssetTracking';" Column does not allow nulls. I am trying to use @CaseID=SCOPE_IDENTITY to insert the CaseID into three tables. Below is the Stored Procedure and the vb code I used:

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

VB 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("CaseID", tbCaseID.Text)
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("@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

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-05 : 07:42:47

I would have to guess that your first If statement that ends with @caseid being assigned a value isn't actually getting hit and so @caseid is ending up as null. Your statement that tries to insert it into asset tracking has no check to see whether @caseid actually has a value and so it tries to run and stuff the null value into your case id column for that table and it has no value. To test this theory I would add a test to see whether @caseid has a value. Something like if @caseid is null begin raiseerror(16,1,'Case ID was not assigned a value). Try this right after your first "END" statement

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources