Solved

update null value-ADO

Posted on 2001-07-16
34
282 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
Comment Utility
change null to vbnullstring
0
 
LVL 3

Expert Comment

by:jrspano
Comment Utility
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
Comment Utility
of course the second method would only work on new adds
0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What database are we talking about?  SQL Server?  If so, does it allow Nulls?

0
 

Author Comment

by:rvindust
Comment Utility
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
Comment Utility
Also what are the multi step errors you are getting?
0
 

Author Comment

by:rvindust
Comment Utility
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
Comment Utility
sorry about all the comments, refresh must have done it.
0
 
LVL 2

Expert Comment

by:mmcmillen
Comment Utility
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
Comment Utility
runtime '-2147217887(80040e21)'
multiple-step operation generated errors. check each status value.
0
 

Author Comment

by:rvindust
Comment Utility
again, zero is a problem, referential integrity.
0
 
LVL 2

Expert Comment

by:mmcmillen
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rvindust
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Did my response solve the problem?  I'm confused.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks a million!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Share codes 68 114
z = x + y – 1 6 55
A macro to Count the number of rows across all worksheets 3 63
Windows 10 start screen issues 9 46
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now