Solved

update null value-ADO

Posted on 2001-07-16
34
287 Views
Last Modified: 2007-11-27
I have a field in my recordset that allows nulls. when i update the text box I have the following code running

sub text1_Validate
if text1="" then
   rs!field1=null
   rs.update
Else
   rs!field1=text1
   rs.update
end if
end sub

this returns a multi step error when the value is set to null. it works if there is a value in the text box. What am I doing wrong.
0
Comment
Question by:rvindust
  • 13
  • 6
  • 4
  • +5
34 Comments
 
LVL 3

Expert Comment

by:jrspano
ID: 6287343
change null to vbnullstring
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6287350
you could also jsut do

rs!field1 = text1.text

this would leave the entry blank in the db. or do you have to have the null? if so just don't write anything to the field.

if text1 <> "" then
  rs!fields = text1
endif
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6287354
of course the second method would only work on new adds
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6287388
Like so...

sub text1_Validate
if text1="" then
  rs!field1=null
  rs.update
Else
  rs!field1= "" & text1.text
  rs.update
end if
end sub
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6287394
Sorry that was confusing...

This should take care of either case

sub text1_Validate

 rs!field1= "" & text1.text
 rs.update

end sub

0
 

Author Comment

by:rvindust
ID: 6287403
Ok, the field is a number field so sending it vbnullstring will not work. the field has a value in it that I want to delete and send it null.
0
 

Author Comment

by:rvindust
ID: 6287424
Ok, the field is a number field so sending it vbnullstring will not work. the field has a value in it that I want to delete and send it null.
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6287436
Little confused here... but maybe something like..

rs!field1 = IIf(text1.text = "", 0, text1.text)

This will send a "0" if the textbox is empty
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6287449
What database are we talking about?  SQL Server?  If so, does it allow Nulls?

0
 

Author Comment

by:rvindust
ID: 6287453
Ok, the field is a number field so sending it vbnullstring will not work. the field has a value in it that I want to delete and send it null.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6287454
Also what are the multi step errors you are getting?
0
 

Author Comment

by:rvindust
ID: 6287463
As first stated the field allows nulls. I do not want to send zero, I want to send a null value back to the SQL Server table. when I use the recordset.update method I get and error sending the null back.
0
 

Author Comment

by:rvindust
ID: 6287470
sorry about all the comments, refresh must have done it.
0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 6287478
Why not do this...

if len(trim$(text1.text)) = 0 then
  rs!field1 = 0
else
  rs!field1 = text1.text
end if



I assume having a zero in the database won't be an issue
0
 

Author Comment

by:rvindust
ID: 6287483
runtime '-2147217887(80040e21)'
multiple-step operation generated errors. check each status value.
0
 

Author Comment

by:rvindust
ID: 6287486
again, zero is a problem, referential integrity.
0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 6287492
What happens if you only write to the field when text1.text contains data?


if len(trim$(text1.text)) > 0 then
 rs!field1 = text1.text
end if

0
 

Author Comment

by:rvindust
ID: 6287503
it works just fine. I have never update the recordset with a null value before, typically I use a sql string instead of the recordset update.
0
 
LVL 7

Expert Comment

by:John844
ID: 6287514
referential integrity?  Do you have another table with a foreign key pointing to this field?  If you do, then you will not be able to set it to NULL.
0
 
LVL 7

Expert Comment

by:John844
ID: 6287521
try changing the value in the database.  The database should give you a more informative error message about what is causing it.  
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6287525
I think John is on the right track...  I bet this field is a part of a key, be it primary or foreign...
0
 

Author Comment

by:rvindust
ID: 6287589
this item is on the many side of the RI, so nulls are allowed. I go into the linked access table and have no problem taking the value to null
0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 6287598
Did my response solve the problem?  I'm confused.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6287670
Sorry, I had not noticed that you had stated that the field allowed nulls.

I checked on a table I created, with no FK, and your code worked fine for me. Here is my code:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
With rs
   .Source = "Select NumField From Test"
   .ActiveConnection = "My SQL Server Connection string"
   .CursorType = adOpenDynamic
   .LockType = adLockOptimistic
   .Open Options:=adCmdText
   .MoveFirst
   !NumField = Null
   .Update
   .Close
End With
Set rs = Nothing

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6287696
Also you should be able to find the nature of the problem with error handling.  All you need is:

Dim cn as ADODB.Connection
Dim er As ADODB.Error

On Error GoTo ErrHandler

' Relevent code goes here

Exit Sub
ErrHandler:
For Each er In cn.Errors
   Debug.Print er.Description
Next

 
0
 

Expert Comment

by:razorzej
ID: 6288330
As acperkins pointed out, you usually will get more
information from the Errors collection of the Connection object, than with the VB Err object.

When I get errors from SQL Server via ADO though, the Descriptions of the ADO Errors are usually still vague.

Try also using the NativeError property, which will give you an internal SQL Server error number (like 515).

e.g. Try adding this in the example:
    Debug.Print er.NativeError

Write down the numbers that appear in the debug window.

If you have access to SQL Query Analyser, you can run this SQL to find out a bit more about the native error:

e.g.

select description
from master..sysmessages
where error = 515

(remember to change the error = xxx part to be the value of the native error)

You will get a generic message, which should help.

This won't solve your problem, but may help to debug it :)

Razorzej
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6288788
Yes, I think the only way to do this is with "UPDATE table1 SET field1 = Null WHERE...."

The VB Null is not the same as an SQL Null so you are on a sticky wicket using Rs.Update
0
 

Author Comment

by:rvindust
ID: 6289531
I there aren no ado errors returned in the error coding. I also am not getting any native error. I guess that nigelrowe has the same conclusion that I am coming to. The ado update does not seem to update this null value. I have no idea why! I am in a "sticky wicket"
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6289615
It's just an expression, I think it means that if you want to insert a Null, you will have to do it with SQL.
0
 

Author Comment

by:rvindust
ID: 6289721
One last shot at this. It is a foreign Key, and that is why it is not allowing update to null, I am still not sure why. I have tried some other fields in the same record which are not foreign keys and they can be set to null through the rs.update. I have updated this field to null both in access(DAO) and an sql execute string(update table set field=null where ...), but when I use the ado update it returns errors which can not be tracked down. Is there a way to set options in ado that would allow me to update this foreign key field?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 25 total points
ID: 6289997
I added a new table Test2 and created a relationship with the old table (Test).  I then retested the same code I posted previously, with the addition of some error checking.  Here is the code:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim er As ADODB.Error

On Error GoTo ErrHandler
Set cn = New ADODB.Connection
With cn
   .ConnectionString = "My SQL Server Connection string"
   .Open
End With

Set rs = New ADODB.Recordset
With rs
   .Source = "Select NumField From Test"
   .ActiveConnection = cn
   .CursorType = adOpenDynamic
   .LockType = adLockOptimistic
   .Open Options:=adCmdText
   .MoveFirst
   !NumField = Null
   .Update
   .Close
End With
Set rs = Nothing

Exit Sub
ErrHandler:
Debug.Print Err.Description
For Each er In cn.Errors
   Debug.Print er.Description
Next

It worked without any problems.  If I changed the value to some value that did not exist than I got the following 3 errors:
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Test_Test2'. The conflict occurred in database 'TestDB', table 'Test2', column 'NumField2'.
The statement has been terminated.
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Note: I am not recommending you use this method for updating, a SQL Insert would be more efficient and Stored Procedure would be even better.  I am just pointing out that the outcome should be the same and that good error handling practices will point out any mistakes.

Anthony
0
 

Author Comment

by:rvindust
ID: 6290204
I have finally located the problem. It is in the text box. There is something squirrely going on with setting the datasource and datafield of the text box then trying to update the null value in the text box. I had no problem running acperkins code. I then tried doing the same thing except that I was running on the text box validate event to run the update, it gave me an error that was not trappable. I then removed setting the datasource to the rs and field property to numfield and just set the text property to the numfield value. once I did this and then set rs!numfield=text1.text and then rs.update it update the null values, but refused to if the datasource was set to the rs.

Works!
Private Sub Form_Load()
ConStr = ""
Set cn = New ADODB.Connection
With cn
  .ConnectionString = ConStr
  .CursorLocation = adUseClient
  .Open
End With

Set rs = New ADODB.Recordset
rs.Open "Select numfield from table1", cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Text1.Text = rs!numfield
End Sub


Private Sub Text1_Validate(Cancel As Boolean)
If Text1.Text = "" Then rs!Reqid = Null Else rs!Reqid = Text1.Text
rs.Update
End Sub

Does Not Work
Dim ConStr As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
ConStr = ""
Set cn = New ADODB.Connection
With cn
  .ConnectionString = ConStr
  .CursorLocation = adUseClient
  .Open
End With

Set rs = New ADODB.Recordset
rs.Open "Select numfield from table1", cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Set Text1.DataSource = rs
Text1.DataField = "NumField"
End Sub


Private Sub Text1_Validate(Cancel As Boolean)
If Text1.Text = "" Then rs!Reqid = Null Else rs!Reqid = Text1.Text
rs.Update
End Sub
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6290362
The lesson to be learned from this is to stay away of bound controls, as much as possible.  Sooner or later you will come to this conclusion, but in the meantime you will spend your time hunting down ellusive problems like this one.
0
 

Author Comment

by:rvindust
ID: 6291000
Thanks a million!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question