Access Not allowing INSERT or UPDATE of null values in SQL-Server column (Access Run-time Error 3162)

Question

Hello the wide world of stackoverflow,

ISSUE:
I am currently working on developing a sql-server database that uses access forms to get and insert data. Prior to yesterday everything was working fine. The issue now is that I am getting the following error when trying to run my insert query.

Access Runtime Error '3162': You tried to assign the Null value to a variable that is not a Variant data type.

ISSUE DETAILS:
This seems to be a result of Access not letting me INSERT or UPDATE null values in a SQL table column that does allow nulls.
Further supporting this if I run the same queries in Microsoft SQL Server Management Studio I am able to INSERT and UPDATE Null values.
You can see the INSERT query I am currently using under OLD ISSUE DETAILS.

SOLUTION 4/24/19 1:35 PM PST:
It seems that the access table link was not using the latest version of the SQL table. After refreshing the link everything worked as it should

OLD ISSUE DETAILS:
UPDATE: 4/24/19, 10:33 AM PST: Realized I forgot to provide where the error happens. The error occurs in the InsertProject function at QDF.Execute

As far as I have seen I am not doing this. All of the table columns that can be null are set as such and the variables inside of vba seem to be set correctly from what I can see.

I have provided the code, queries, and other information that are involved with this issue.

Here is the query I am using:


    INSERT INTO ci_project 
    (project_id, 
    project_group, 
    project_projecttype, 
    project_hasmilestones, 
    project_hasgantt, 
    project_category, 
    project_difficulty, 
    project_notes, 
    project_completiondetails, 
    project_statuscode, 
    project_inprogressdate, 
    project_completeddate, 
    project_datecreated, 
    project_lastupdated, 
    project_canceleddate, 
    project_isActive) 
    VALUES (@projectID
    ,@projectGroup
    ,@projectType
    ,@projectHasMilestones
    ,@projectHasGantt
    ,@projectCategory
    ,@projectDifficulty
    ,@projectNotes
    ,@projectCompletionDetails
    ,@projectStatus
    ,@projectDateInProgress
    ,@projectDateComplete
    ,@projectDateCreated
    ,@projectDateUpdated
    ,@projectCanceledDate
    ,@projectIsActive);

Here is the function I made to make it easier to call it multiple times:


    'This is located in another Module called OtherFunctions
    Dim dbs as DAO.Database

    Public Function InsertProject(projectID As String, _
                                  projectGroup As Integer, _
                                  projectType As Integer, _
                                  projectHasMilestones As Boolean, _
                                  projectHasGantt As Boolean, _
                                  projectCategory As Integer, _
                                  projectDifficulty As Integer, _
                                  projectNotes As Variant, _
                                  projectCompletionDetails As Variant, _
                                  projectStatus As Integer, _
                                  projectDateInProgress As Variant, _
                                  projectDateComplete As Variant, _
                                  projectCanceledDate As Variant, _
                                  projectIsActive As Integer)

        OtherFunctions.Initialize

        Dim QDF As DAO.QueryDef

        If FindQuery("InsertProject") = True Then OtherFunctions.dbs.QueryDefs.Delete "InsertProject"

        Set QDF = OtherFunctions.dbs.CreateQueryDef("InsertProject", SQLInsertProject)

        QDF.Parameters("@projectID").Value = projectID
        QDF.Parameters("@projectGroup").Value = projectGroup
        QDF.Parameters("@projectType").Value = projectType
        QDF.Parameters("@projectHasMilestones").Value = projectHasMilestones
        QDF.Parameters("@projectHasGantt").Value = projectHasGantt
        QDF.Parameters("@projectCategory").Value = projectCategory
        QDF.Parameters("@projectDifficulty").Value = projectDifficulty
        QDF.Parameters("@projectNotes").Value = projectNotes
        QDF.Parameters("@projectCompletionDetails").Value = projectCompletionDetails
        QDF.Parameters("@projectStatus").Value = projectStatus
        QDF.Parameters("@projectDateInProgress").Value = ConvertDateToUnix(projectDateInProgress)
        QDF.Parameters("@projectDateComplete").Value = ConvertDateToUnix(projectDateComplete)
        QDF.Parameters("@projectDateCreated").Value = ConvertDateToUnix(Now())
        QDF.Parameters("@projectDateUpdated").Value = ConvertDateToUnix(Now())
        QDF.Parameters("@projectIsActive").Value = projectIsActive
        QDF.Parameters("@projectCanceledDate").Value = ConvertDateToUnix(projectCanceledDate)

        QDF.Execute

        If FindQuery("InsertProject") = True Then OtherFunctions.dbs.QueryDefs.Delete "InsertProject"

        Set QDF = Nothing

    End Function

Here is where I am calling the function:


    'These are set in the same sub as the insert project call
    Dim projectID As String
    Dim CancelDate As Variant
    Dim canceledStatus As Integer

    'These are located in a different module called OtherFunctions
    Public IDEASUGGESTION_HASGANT As Boolean
    Public IDEASUGGESTION_HASMILESTONES As Boolean
    Public IDEASUGGESTION_PROJECTTYPE As Integer


    ' /\/\/\ THERE IS CODE ABOVE THIS /\/\/\
        canceledStatus = 12
    If Me.IdeaStatus = canceledStatus And DatabaseQueries.CheckIdeaSuggestion(Me.IdeaID) = True Then
       CancelDate = Now()
       If MsgBox("Are you sure you want to do this? Canceling a idea will make it un-editable.", vbYesNo) = vbYes Then
           GoTo IdeaCancel
       Else
           GoTo GotoEnd
       End If
    ElseIf Me.IdeaStatus = canceledStatus And DatabaseQueries.CheckIdeaSuggestion(Me.IdeaID) = False Then
       MsgBox "You cannot cancel an idea that does not exist.", vbExclamation
       CancelDate = Null
       GoTo GotoEnd
    Else
       'other code run here not pertaining to the insert
    End If

    Call DatabaseQueries.InsertProject(
         projectID, _
         Me.IdeaGroup, _
         OtherFunctions.IDEASUGGESTION_PROJECTTYPE, _
         OtherFunctions.IDEASUGGESTION_HASMILESTONES, _
         OtherFunctions.IDEASUGGESTION_HASGANT, _
         Me.IdeaCategory, _
         Me.IdeaDifficulty, _
         Null, _
         Null, _
         Me.IdeaStatus, _
         Me.IdeaInprogressDate, _
         Me.IdeaCompleteDate, _
         CancelDate, _
         1)

    ' \/\/\/ THERE IS CODE BELOW THIS \/\/\/

When I am running this, these are the form values:


    Me.ideaID = Null
    Me.IdeaGroup = 1
    Me.IdeaCategory = 2
    Me.IdeaDifficulty = 1
    Me.IdeaStatus = 1
    Me.IdeaInprogressDate = Null
    Me.IdeaCompleteDate = Null
    OtherFunctions.IDEASUGGESTION_PROJECTTYPE = 1
    OtherFunctions.IDEASUGGESTION_HASMILESTONES = False
    OtherFunctions.IDEASUGGESTION_HASGANT = False

The Table structure is:

    Column Name                 Data Type   Can be Null
    project_id                  varchar(45) No
    project_group               int         No
    project_projecttype         int         No
    project_hasmilestones       bit         No
    project_hasgantt            bit         No
    project_category            int         No
    project_difficulty          int         No
    project_notes               text        Yes
    project_completiondetails   text        Yes
    project_statuscode          int         No
    project_inprogressdate      bigint      Yes
    project_completeddate       bigint      Yes
    project_datecreated         bigint      No
    project_lastupdated         bigint      No
    project_canceleddate        bigint      Yes
    project_isActive            int         No

My apologies for the wall of code blocks.

If anyone knows why or can figure out why this error is happening I would be very grateful.

UPDATE 4/24/19 11:10 AM PST: Based on commends from HansUp I created an alternate insert method using the RST.AddNew method More details can be found here. After doing this I found that the variable that is causing grief is projectCanceledDate in the InsertProject function. Only issue is I don't know why, the variable is defined as a Variant.

UPDATE 4/24/19 11:43 AM PST: After doing some more testing. I have discovered that I am able to insert and update NULL values for project_cancleddate inside of the Microsoft SQL Server Management Studio.

Why is INSERT action needed? Are you not using bound forms? Step debug, which variable is throwing the error? — June7
– June7, Commented Apr 24, 2019 at 17:08
@June7 There are multiple tables involved in the insert this is just one of them that is having issues. I also need to use unbound columns since I am changing the format of the microsoft time code to the unix time code which, as far as I am aware, you cannot display as a date. — EliSauder
– EliSauder, Commented Apr 24, 2019 at 17:12
That error means that a primary key field, or required field, is trying to be assigned a NULL value and it's not happy. I would start debugging with ensuring the keys you're INSERTing are all populated with values, then work your way to required fields. — Fink
– Fink, Commented Apr 24, 2019 at 17:17
Does ideaID feed projectID variable? projectID expects a string yet you say ideaID is Null. Only Variant type can hold null. Advise again, step debug. — June7
– June7, Commented Apr 24, 2019 at 17:17
@HansUp Thank you for all your help. :-) Turns out it was just that the access table link needed to be refreshed. Still not sure why since I don't think I ever set the table to be NOT NULL. — EliSauder
– EliSauder, Commented Apr 24, 2019 at 20:37

EliSauder · Accepted Answer · 2019-04-24 20:32:24Z

1

After doing some more experimentation I found the problem. It seems that a change in the SQL server had not been reflected in the access database (namely making the project_canceleddate accept nulls). After refreshing the link on the ci_project table everything worked fine.

answered Apr 24, 2019 at 20:32

EliSauder

1382 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

donPablo · Accepted Answer · 2019-04-24 18:05:03Z

0

In the Call InsertProject, the fields projectID and CancelDate are not given any values. What is used for them?

Add this simple code to look at what is there in the parameters.

Dim parm As DAO.Parameter
For Each parm In QDF.Parameters
    Debug.Print parm.Name, parm.Value
Next parm

Here is what I got

  @projectID                  MyProjId
  @projectGroup               1
  @projectType                1
  @projectHasMilestones       0
  @projectHasGantt            0
  @projectCategory            2
  @projectDifficulty          1
  @projectNotes               Null
  @projectCompletionDetails   Null
  @projectStatus              1
  @projectDateInProgress      Null
  @projectDateComplete        Null
  @projectDateCreated         1556103601
  @projectDateUpdated         1556103601
  @projectCanceledDate        Null
  @projectIsActive            1

answered Apr 24, 2019 at 18:05

donPablo

1,9691 gold badge13 silver badges20 bronze badges

2 Comments

EliSauder Over a year ago

projectID is based on another method which pulls values from a counter table that I have. It concatenates different values Prefix + Counter + Suffix and then returns that string. I can add that function to the question if you want to see it. CancelDate is set to either Now() or Null depending on values entered in the form. I have provided that logic in the question right under "Here is where I am calling the function:"

EliSauder Over a year ago

I do like that loop to view a QueryDef's params. Thank you for posting that.

Collectives™ on Stack Overflow

Access Not allowing INSERT or UPDATE of null values in SQL-Server column (Access Run-time Error 3162)

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related