By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

RecordsAffected reporting the wrong value

P: n/a

It appears that RecordsAffected reports the wrong value
when a Null integer or date value is inserted using empty quotes.
The insert works fine, but RecordsAffected reports 0 instead of 1.

Here is the code...

--------

Dim dbMyData As Database
Dim strSQL As String

Set dbMyData = CurrentDb

strSQL = "INSERT INTO tblExample (KeyField, TextField) VALUES ('test1', '');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test1 records affected = " & dbMyData.RecordsAffected) ' Reports 1

strSQL = "INSERT INTO tblExample (KeyField, Count) VALUES ('test2', '');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test2 records affected = " & dbMyData.RecordsAffected) ' Reports 0

strSQL = "INSERT INTO tblExample (KeyField, RecvDate) VALUES ('test3', '');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test3 records affected = " & dbMyData.RecordsAffected) 'Reports 0

--------

The database has 4 fields, and all except the KeyField allow empty records.

I just wasted a whole day chasing this down. My data has a lot of null
values that will need to be stored in a variety of fields. If I have to,
I can replace the '' with NULL, but it'll be a real pain because I'll have
to examine the value of every field on every insert.

Why is RecordsAffected lying to me?

Thanks,
Jack
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
That's an interesting issue, Jack.

The core of the problem is that the SQL strings are invalid: you cannot
assign a zero-length string to a Number type field, nor to a Date/Time
field. Consequently, the insert *should* fail. Instead, Access assigns what
it can from the insert anyway, and then reports that it failed. Clearly that
result is inconsistent: in my view, the error is that JET is allowing the
invalid string to partially execute, when the append should completely fail.

To avoid the problem, use the dbFailOnError switch, which blocks the invalid
append, and so the RecordsAffected is correctly returned as zero:
Dim dbMyData As Database
Dim strSQL As String

Set dbMyData = CurrentDb

strSQL = "INSERT INTO tblExample (KeyField, TextField) VALUES ('test1',
'');"
dbMyData.Execute strSQL, dbFailOnError 'Insert succeeds
Debug.Print "test1 records affected = " & dbMyData.RecordsAffected '
Reports 1

strSQL = "INSERT INTO tblExample (KeyField, Count) VALUES ('test2',
'');"
dbMyData.Execute strSQL, dbFailOnError 'Insert succeeds
Debug.Print "test2 records affected = " & dbMyData.RecordsAffected '
Reports 0

strSQL = "INSERT INTO tblExample (KeyField, RecvDate) VALUES ('test3',
'');"
dbMyData.Execute strSQL, dbFailOnError 'Insert succeeds
Debug.Print "test3 records affected = " & dbMyData.RecordsAffected
'Reports 0

To get that to run to completion, you will need to add:
On Error Resume Next

To correctly form the SQL strings, use Null instead of a zero-length string:
INSERT INTO tblExample (KeyField, TextField) VALUES ('test1', Null);
INSERT INTO tblExample (KeyField, RecvDate) VALUES ('test3', Null);

BTW, I would also encourage you to set the AllowZeroLength property of your
Text fields to No, so you do not have to constantly test for both a ZLS
(zero-length-string) and a Null, and so your users (who can't see the
difference) don't get confused. When you do, the first of your examples also
fails.

If you are not clear on the difference between null and a zls, see:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jack" <jn***@pcisysSPAM.net> wrote in message
news:pa****************************@pcisysSPAM.net ...

It appears that RecordsAffected reports the wrong value
when a Null integer or date value is inserted using empty quotes.
The insert works fine, but RecordsAffected reports 0 instead of 1.

Here is the code...

--------

Dim dbMyData As Database
Dim strSQL As String

Set dbMyData = CurrentDb

strSQL = "INSERT INTO tblExample (KeyField, TextField) VALUES ('test1',
'');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test1 records affected = " & dbMyData.RecordsAffected) ' Reports
1

strSQL = "INSERT INTO tblExample (KeyField, Count) VALUES ('test2', '');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test2 records affected = " & dbMyData.RecordsAffected) ' Reports
0

strSQL = "INSERT INTO tblExample (KeyField, RecvDate) VALUES ('test3',
'');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test3 records affected = " & dbMyData.RecordsAffected) 'Reports 0

--------

The database has 4 fields, and all except the KeyField allow empty
records.

I just wasted a whole day chasing this down. My data has a lot of null
values that will need to be stored in a variety of fields. If I have to,
I can replace the '' with NULL, but it'll be a real pain because I'll have
to examine the value of every field on every insert.

Why is RecordsAffected lying to me?

Thanks,
Jack

Nov 13 '05 #2

P: n/a

Thanks for your clear and informative reply. I had a feeling
that the inserts should've failed. Guess I'll have
to replace my empty strings with NULLs (and add dbFailOnError
per your suggestion).

Regards,
Jack

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.