Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

update null value-ADO

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
rvindust
Asked:
rvindust
  • 13
  • 6
  • 4
  • +5
1 Solution
 
jrspanoCommented:
change null to vbnullstring
0
 
jrspanoCommented:
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
 
jrspanoCommented:
of course the second method would only work on new adds
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Dave_GreeneCommented:
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
 
Dave_GreeneCommented:
Sorry that was confusing...

This should take care of either case

sub text1_Validate

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

end sub

0
 
rvindustAuthor Commented:
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
 
rvindustAuthor Commented:
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
 
Dave_GreeneCommented:
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
 
Anthony PerkinsCommented:
What database are we talking about?  SQL Server?  If so, does it allow Nulls?

0
 
rvindustAuthor Commented:
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
 
Anthony PerkinsCommented:
Also what are the multi step errors you are getting?
0
 
rvindustAuthor Commented:
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
 
rvindustAuthor Commented:
sorry about all the comments, refresh must have done it.
0
 
mmcmillenCommented:
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
 
rvindustAuthor Commented:
runtime '-2147217887(80040e21)'
multiple-step operation generated errors. check each status value.
0
 
rvindustAuthor Commented:
again, zero is a problem, referential integrity.
0
 
mmcmillenCommented:
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
 
rvindustAuthor Commented:
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
 
John844Commented:
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
 
John844Commented:
try changing the value in the database.  The database should give you a more informative error message about what is causing it.  
0
 
Dave_GreeneCommented:
I think John is on the right track...  I bet this field is a part of a key, be it primary or foreign...
0
 
rvindustAuthor Commented:
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
 
mmcmillenCommented:
Did my response solve the problem?  I'm confused.
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
razorzejCommented:
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
 
nigelroweCommented:
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
 
rvindustAuthor Commented:
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
 
nigelroweCommented:
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
 
rvindustAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
rvindustAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
rvindustAuthor Commented:
Thanks a million!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 13
  • 6
  • 4
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now