Cannot insert the value Null into columns...

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
Hi all

I am getting the above error when I go to update a database table using an sql data adapter and dataset. The steps that I follow are:

1. go to the db and use an sql stored procedure to return the data I need, all of which is being selected from the 1 table.
Code: 
CREATE PROCEDURE dbo.SelectAllChangeControls 

AS

	SELECT ChangeControls.Id, CONVERT(varchar, ChangeControls.Raised, 103) AS [Raised], Owners.ReverseName, Systems.Name, Status.Status, 
             		         ChangeControls.CodeChange, Priority.Priority, CONVERT(varchar, ChangeControls.CompletionDate, 103) AS CompletionDate, ChangeControls.CCNumber, 
             			         ChangeControls.Description, ChangeControls.Resolution, ChangeControls.CCLink, ChangeControls.EstBA, 
			                      ChangeControls.EstDev
	FROM ChangeControls INNER JOIN
                      		Owners ON ChangeControls.Owner = Owners.Id INNER JOIN
                      			Priority ON ChangeControls.Priority = Priority.Id INNER JOIN
	                      			Systems ON ChangeControls.mySystem = Systems.Id INNER JOIN
			                      		Status ON ChangeControls.Id = Status.Id
GO

No problem so far. When the data is returned in a dataset, I place it in a session variable.

2. I then decide that I want to add a new record, so I enter the necessary data in the fields provided and click on the save button. What this does is:
Code: 
dim dsChangeControl as new dataset
dsData = CType(Session("Times"), dataset)

 Private Sub AddNewRecord()
        Create a new datarow for the new record.
        Dim drwAdd As DataRow
        Dim dPrinter As New DatePrinter

        drwAdd = dsChangeControl.Tables("ChangeControls").NewRow

        Insert the relevant values for the columns.
        With drwAdd
            .Item("ID") = "0"
            .Item("Raised") = dPrinter.PrintNumericDate
            .Item("ReverseName") = ddOwners.SelectedItem.Text
            .Item("Name") = ddSystems.SelectedItem.Text
            .Item("Status") = "Open"
            .Item("CodeChange") = ddCodeChange.SelectedItem.Value
            .Item("Priority") = ddPriority.SelectedItem.Text
            .Item("CompletionDate") = ""
            .Item("CCNumber") = txtCCNumber.Text
            .Item("Description") = txtDescription.Text
            .Item("Resolution") = ""
            .Item("CCLink") = txtCCDoc.Text
            .Item("EstBA") = txtBa.Text
            .Item("EstDev") = txtDev.Text
        End With

        Add the row to the rows coolection of the dataset.
        dsChangeControl.Tables("ChangeControls").Rows.Add(drwAdd)

        Go to the database and perform the insert query.
        cControl.UpdateChangeControls(dsChangeControl)
    End Sub
Once the above code has run, I have queried the rows and columns in dsChangeControl.Tables("ChangeControls") and I have confirmed that all my data is stored in the table.

3. I next send the modified dataset to my db code.
(my Stored procedure)
Code: 
CREATE PROCEDURE dbo.InsertNewChangeControl 

	@Raised datetime,
	@Owner nvarchar,
	@System nvarchar,
	@CodeChange bit,
	@Priority nvarchar,
	@CCNumber nvarchar (20),
	@Description nvarchar (150),
	@CCLink nvarchar(200),
	@EstBA decimal,
	@EstDev decimal

AS

	DECLARE @tempOwner int
	DECLARE @tempSystem int
	DECLARE @tempPriority int

	SELECT @tempPriority= (Id)
	FROM dbo.Priority
	WHERE (Priority = @Priority)

	SELECT @tempSystem = (Id)
	FROM dbo.Systems
	WHERE (Name = @System)

	SELECT @tempOwner = (Id)
	FROM dbo.Owners
	WHERE (ReverseName = @Owner)

	INSERT INTO ChangeControls (Raised, Owner, mySystem, Status, CodeChange, Priority, CCNumber, Description, CCLink, EstBA, EstDev)
	VALUES ( @Raised, @tempOwner, @tempSystem, 1, @CodeChange, @tempPriority,  @CCNumber, @Description, @CCLink, @EstBA, @EstDev)
GO

(vb.net code)
Code: 
Public Function UpdateChangeControls(ByVal ccData As DataSet) As Boolean

        Dim sqlReturn As Boolean = True

        Try
            daAdapter.InsertCommand = New SqlCommand
            daAdapter.InsertCommand.Connection = cnConn
            daAdapter.InsertCommand.CommandText = "InsertNewChangeControl"
            daAdapter.InsertCommand.CommandType = CommandType.StoredProcedure

  Inserting a new record to the database.
            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@Raised", SqlDbType.DateTime))
            daAdapter.InsertCommand.Parameters("@Raised").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@Raised").SourceColumn = "Raised"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@Owner", SqlDbType.NVarChar))
            daAdapter.InsertCommand.Parameters("@Owner").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@Owner").SourceColumn = "ReverseName"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@System", SqlDbType.NVarChar))
            daAdapter.InsertCommand.Parameters("@System").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@System").SourceColumn = "Name"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@CodeChange", SqlDbType.Bit))
            daAdapter.InsertCommand.Parameters("@CodeChange").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@CodeChange").SourceColumn = "CodeChange"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@Priority", SqlDbType.NVarChar))
            daAdapter.InsertCommand.Parameters("@Priority").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@Priority").SourceColumn = "Priority"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@CCNumber", SqlDbType.NVarChar))
            daAdapter.InsertCommand.Parameters("@CCNumber").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@CCNumber").SourceColumn = "CCNumber"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@Description", SqlDbType.NVarChar))
            daAdapter.InsertCommand.Parameters("@Description").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@Description").SourceColumn = "Description"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@CCLink", SqlDbType.NVarChar))
            daAdapter.InsertCommand.Parameters("@CCLink").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@CCLink").SourceColumn = "CCLink"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@EstBA", SqlDbType.Decimal))
            daAdapter.InsertCommand.Parameters("@EstBA").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@EstBA").SourceColumn = "EstBA"

            daAdapter.InsertCommand.Parameters.Add(New SqlParameter("@EstDev", SqlDbType.Decimal))
            daAdapter.InsertCommand.Parameters("@EstDev").Direction = ParameterDirection.Input
            daAdapter.InsertCommand.Parameters("@EstDev").SourceColumn = "EstDev"

  Perform the database update.
            daAdapter.Update(ccData, "ChangeControls")

            Return sqlReturn

        Catch ex As Exception
            sqlReturn = False
        End Try
    End Function

Any suggestions? Problem is really driving me confused: and :mad:. I have used the same approach in similar projects previously with no problems. The only thing that I can think is wrong is the stored procedure.

Mike55.
 
Im not sure which column it is, but it doesnt seem too hard to figure out on your end. Heres what Id look at:

Looking at your ChangeControls table and see which columns dont allow NULL (marked as NOT NULL). If there are only a few, this makes things easier.

In method UpdateChangeControls, look at the parameters and see which of the columns have value DBNull.Value instead of a valid value.

That should pinpoint the current issue. Some columns seem fine, like "Status" which are hard-coded to have a value like "Open"

To help find these types of issues earlier, you can define the columns in your DataSet to have the same properties as those of your DB table. Suppose your table has column "Name" as NOT NULL. When you define your DataSet, just make sure to have the column not allow nulls as well. You set this through the "MinOccurs" property - using a value of zero will make it not allow nulls. If you need to temporarily allow nulls, you can set the nillable property to true. That will allow you to put DBNull.Value in the non-null column until you try and commit - then the nillible=false property will prevent you from saving.

-ner
 
Back
Top