!AddNew doesn't. ...cannot insert NULL 'rowguid'

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a Project connected to (local) Anonymous Merge Subscription, cannot Add a
New Record programmatically. Works fine from Table View and Standalone Form
created with Wizard. CommandButton Event Procedure is a Cut-and-Paste Code
that works several other places in the ADP. In this Form it Errors out at
"rst2.Update"

Run-time error '-2147217873...':
Cannot insert the value NULL into column 'rowguid', table


Dim cmd2 As ADODB.Command
Set cmd2 = New ADODB.Command
cmd2.ActiveConnection = CurrentProject.Connection
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM tblTeamContact WHERE 1=0"
Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst2.CursorLocation = adUseClient
rst2.Open cmd2, , adOpenStatic, adLockOptimistic

Dim lngNID As Long
Randomize
lngNID = Int((Rnd * 2 ^ 29) - 2 ^ 28) * 10 + Val(Right$(pstrMachineName,
2))
rst2.AddNew
rst2![SchedID] = rst1!SchedID
rst2![PID] = rst1!PID
rst2![MDID] = rst1!PhysID
rst2![NFID] = rst1!NursingFacility
rst2![SysDate] = Now()
rst2![NoteDate] = Now()
rst2![ProvID] = pstrLogonID
rst2![NID] = lngNID
rst2.Update
rst2.Close
Set rst2 = Nothing
 
Hello,

You may want to use SQL profiler to check the query when the issue occurs.
Also, please use the query directly in QA to test if the issue occurs.

Since rowguid is default to "newid()" in table design, this issue can occur
if ADO tries to use "NULL" as uniqueidentifier when inserting the record.
You may want to manually create a guid and explicitly insert them into the
table.

How To Use GUIDs w/ Access, SQL 6.5 and SQL 7
http://support.microsoft.com/kb/q197916/

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
Thread-Topic: !AddNew doesn't. ...cannot insert NULL 'rowguid'
thread-index: AcYnZrJT2dhHxF8/RPOqgvVcdtfQ3w==
X-WBNR-Posting-Host: 68.79.183.184
From: =?Utf-8?B?QXVicmV5?= <[email protected]>
Subject: !AddNew doesn't. ...cannot insert NULL 'rowguid'
Date: Wed, 1 Feb 2006 11:35:45 -0800
Lines: 39
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.adp.sqlserver
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.access.adp.sqlserver:23507
X-Tomcat-NG: microsoft.public.access.adp.sqlserver

In a Project connected to (local) Anonymous Merge Subscription, cannot Add a
New Record programmatically. Works fine from Table View and Standalone Form
created with Wizard. CommandButton Event Procedure is a Cut-and-Paste Code
that works several other places in the ADP. In this Form it Errors out at
"rst2.Update"

Run-time error '-2147217873...':
Cannot insert the value NULL into column 'rowguid', table


Dim cmd2 As ADODB.Command
Set cmd2 = New ADODB.Command
cmd2.ActiveConnection = CurrentProject.Connection
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM tblTeamContact WHERE 1=0"
Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst2.CursorLocation = adUseClient
rst2.Open cmd2, , adOpenStatic, adLockOptimistic

Dim lngNID As Long
Randomize
lngNID = Int((Rnd * 2 ^ 29) - 2 ^ 28) * 10 + Val(Right$(pstrMachineName,
2))
rst2.AddNew
rst2![SchedID] = rst1!SchedID
rst2![PID] = rst1!PID
rst2![MDID] = rst1!PhysID
rst2![NFID] = rst1!NursingFacility
rst2![SysDate] = Now()
rst2![NoteDate] = Now()
rst2![ProvID] = pstrLogonID
rst2![NID] = lngNID
rst2.Update
rst2.Close
Set rst2 = Nothing
Click to expand...
 
Thanks, Peter.

While not an answer, per se, it did encourage me to look at explicitly
setting the <rowguid>. From another thread I found this code snippet to
create the GUID:

Dim x As Object
Dim strNewGUID As String
Set x = CreateObject("Scriptlet.TypeLib")
strNewGUID = Left(x.Guid, 38)

Application now works! . . . at least, got past this hurdle.

--
Aubrey Kelley


Peter Yang said:
Hello,

You may want to use SQL profiler to check the query when the issue occurs.
Also, please use the query directly in QA to test if the issue occurs.

Since rowguid is default to "newid()" in table design, this issue can occur
if ADO tries to use "NULL" as uniqueidentifier when inserting the record.
You may want to manually create a guid and explicitly insert them into the
table.

How To Use GUIDs w/ Access, SQL 6.5 and SQL 7
http://support.microsoft.com/kb/q197916/

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
Thread-Topic: !AddNew doesn't. ...cannot insert NULL 'rowguid'
thread-index: AcYnZrJT2dhHxF8/RPOqgvVcdtfQ3w==
X-WBNR-Posting-Host: 68.79.183.184
From: =?Utf-8?B?QXVicmV5?= <[email protected]>
Subject: !AddNew doesn't. ...cannot insert NULL 'rowguid'
Date: Wed, 1 Feb 2006 11:35:45 -0800
Lines: 39
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.adp.sqlserver
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.access.adp.sqlserver:23507
X-Tomcat-NG: microsoft.public.access.adp.sqlserver

In a Project connected to (local) Anonymous Merge Subscription, cannot Add a
New Record programmatically. Works fine from Table View and Standalone Form
created with Wizard. CommandButton Event Procedure is a Cut-and-Paste Code
that works several other places in the ADP. In this Form it Errors out at
"rst2.Update"

Run-time error '-2147217873...':
Cannot insert the value NULL into column 'rowguid', table


Dim cmd2 As ADODB.Command
Set cmd2 = New ADODB.Command
cmd2.ActiveConnection = CurrentProject.Connection
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM tblTeamContact WHERE 1=0"
Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst2.CursorLocation = adUseClient
rst2.Open cmd2, , adOpenStatic, adLockOptimistic

Dim lngNID As Long
Randomize
lngNID = Int((Rnd * 2 ^ 29) - 2 ^ 28) * 10 + Val(Right$(pstrMachineName,
2))
rst2.AddNew
rst2![SchedID] = rst1!SchedID
rst2![PID] = rst1!PID
rst2![MDID] = rst1!PhysID
rst2![NFID] = rst1!NursingFacility
rst2![SysDate] = Now()
rst2![NoteDate] = Now()
rst2![ProvID] = pstrLogonID
rst2![NID] = lngNID
rst2.Update
rst2.Close
Set rst2 = Nothing
Click to expand...
Click to expand...
 
Hello Aubrey,

Great to hear you resolved the issue.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
Thread-Topic: !AddNew doesn't. ...cannot insert NULL 'rowguid'
thread-index: AcYoy71CfhlOdtsaTYiI+PrtFN7mZQ==
X-WBNR-Posting-Host: 68.79.183.184
From: =?Utf-8?B?QXVicmV5?= <[email protected]>
References: <[email protected]>
Click to expand...
Subject: RE: !AddNew doesn't. ...cannot insert NULL 'rowguid'
Date: Fri, 3 Feb 2006 06:11:34 -0800
Lines: 116
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.adp.sqlserver
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.access.adp.sqlserver:23543
X-Tomcat-NG: microsoft.public.access.adp.sqlserver

Thanks, Peter.

While not an answer, per se, it did encourage me to look at explicitly
setting the <rowguid>. From another thread I found this code snippet to
create the GUID:

Dim x As Object
Dim strNewGUID As String
Set x = CreateObject("Scriptlet.TypeLib")
strNewGUID = Left(x.Guid, 38)

Application now works! . . . at least, got past this hurdle.

--
Aubrey Kelley


Peter Yang said:
Hello,

You may want to use SQL profiler to check the query when the issue occurs.
Also, please use the query directly in QA to test if the issue occurs.

Since rowguid is default to "newid()" in table design, this issue can occur
if ADO tries to use "NULL" as uniqueidentifier when inserting the record.
You may want to manually create a guid and explicitly insert them into the
table.

How To Use GUIDs w/ Access, SQL 6.5 and SQL 7
http://support.microsoft.com/kb/q197916/

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
Thread-Topic: !AddNew doesn't. ...cannot insert NULL 'rowguid'
thread-index: AcYnZrJT2dhHxF8/RPOqgvVcdtfQ3w==
X-WBNR-Posting-Host: 68.79.183.184
From: =?Utf-8?B?QXVicmV5?= <[email protected]>
Subject: !AddNew doesn't. ...cannot insert NULL 'rowguid'
Date: Wed, 1 Feb 2006 11:35:45 -0800
Lines: 39
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.adp.sqlserver
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.access.adp.sqlserver:23507
X-Tomcat-NG: microsoft.public.access.adp.sqlserver

In a Project connected to (local) Anonymous Merge Subscription, cannot
Click to expand...
Add
a
New Record programmatically. Works fine from Table View and Standalone Form
created with Wizard. CommandButton Event Procedure is a Cut-and-Paste Code
that works several other places in the ADP. In this Form it Errors out at
"rst2.Update"

Run-time error '-2147217873...':
Cannot insert the value NULL into column 'rowguid', table


Dim cmd2 As ADODB.Command
Set cmd2 = New ADODB.Command
cmd2.ActiveConnection = CurrentProject.Connection
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM tblTeamContact WHERE 1=0"
Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst2.CursorLocation = adUseClient
rst2.Open cmd2, , adOpenStatic, adLockOptimistic

Dim lngNID As Long
Randomize
lngNID = Int((Rnd * 2 ^ 29) - 2 ^ 28) * 10 + Val(Right$(pstrMachineName,
2))
rst2.AddNew
rst2![SchedID] = rst1!SchedID
rst2![PID] = rst1!PID
rst2![MDID] = rst1!PhysID
rst2![NFID] = rst1!NursingFacility
rst2![SysDate] = Now()
rst2![NoteDate] = Now()
rst2![ProvID] = pstrLogonID
rst2![NID] = lngNID
rst2.Update
rst2.Close
Set rst2 = Nothing
Click to expand...
Click to expand...
Click to expand...
 
Back
Top