访问不允许在SQL Server列中插入或更新空值（访问运行时错误3162） [英] Access Not allowing INSERT or UPDATE of null values in SQL-Server column (Access Run-time Error 3162)
问题描述
您好，stackoverflow的世界很广，
Hello the wide world of stackoverflow,
问题：
我目前正在开发使用访问表单的sql-server数据库获取和插入数据。
在昨天之前，一切正常。现在的问题是，尝试运行我的插入查询时出现以下错误。
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.
访问运行时错误'3162'：您尝试执行以下操作：将Null值分配给不是Variant数据类型的变量。
问题详细信息：
是Access不允许在允许空值的SQL表列中插入或更新空值的结果。
如果在Microsoft SQL Server Management Studio中运行相同的查询，则可以进一步支持此功能，并且能够插入和更新Null值。
您可以在OLD ISSUE下看到我当前正在使用的INSERT查询。细节。
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.
解决方案19/4/24 PST：
似乎访问表链接未使用最新版本的SQL表。刷新链接后，一切正常运行
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
旧问题详细信息：
更新：4 / 24/19，上午10:33 PST：意识到我忘记提供错误发生的位置。该错误发生在 QDF.Execute
上的InsertProject函数中，据我所知我没有这样做。所有可以为null的表列都是这样设置的，从我所看到的来看，vba内部的变量似乎已正确设置。
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
在这里我称之为函数：
'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
表结构是：
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
我为代码块墙道歉。
如果有人知道为什么或可以找出发生此错误的原因，我将非常感激。
If anyone knows why or can figure out why this error is happening I would be very grateful.
更新19/4/24，太平洋标准时间（PST）：基于HansUp的建议，我使用RST.AddNew方法创建了一个备用插入方法更多详细信息可以在这里找到。完成此操作后，我发现导致悲伤的变量是InsertProject函数中的projectCanceledDate。唯一的问题是我不知道为什么，该变量定义为Variant。
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 19/4/24 AM PST：经过更多测试。我发现我可以在Microsoft SQL Server Management Studio中为project_cancleddate插入和更新NULL值。
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.
推荐答案
之后做更多的实验，我发现了问题。似乎SQL Server的更改未反映在访问数据库中（即使 project_canceleddate 接受空值）。刷新 ci_project 表上的链接后，一切正常。
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.
这篇关于访问不允许在SQL Server列中插入或更新空值（访问运行时错误3162）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
