Cannot insert the value Null into columns...

[mike55]

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.

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:

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 row's 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)

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)

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.

[Nerseus]

I'm not sure which column it is, but it doesn't seem too hard to figure out on your end. Here's what I'd look at:

 

Looking at your ChangeControls table and see which columns don't 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