Solved

update null value-ADO

Posted on 2001-07-16
34
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month6 days, 21 hours left to enroll

623 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