ADO - problem with Null fields

[emmzou]

I have just finished (almost) my first ADO database (connected to an Access database). It's a kind of scientific reference manager. Everything looks OK EXCEPT that :

When I try to add a new record and if some fields remain empty then either
1. I get an error during the Recordset.update process (saying "Invalid use of Null" )
or
2.When I try to review a record with empty fields the text boxes that are assigned to present the Fields data (for example : txtTitle.text=rs.Fields("Title").Value) do not accept the Null values (reasonable!).

Please Help. It may be a stupid problem but I am new in VB and databases.

Thanks

Emmanuel

[Jimbob]

text boxes and the like won't take null values.

the best thing to do is test each value before you commit it to your database

use isnull to test the value, if it returns true, then set the value to an empty string instead eg

if isnull(ValueToBeTested) then
ValueToBeTested = ""
endif

[DrewDog_21]

Looks like you are using ADO recordsets and not the ADO data control. Instead of assigning the values to the textboxes with code, bind the textboxes to the recordset like this:


Private myCN As ADODB.Connection
Private myRS As New ADODB.Recordset

Private Sub Form_Load()

'set new connection
Set myCN = New ADODB.Connection
With myCN
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDataBase.mdb;Persist Security Info=False"
.CursorLocation = adUseClient
.Open
End With

'close an open recordset
If Not (myRS Is Nothing) Then
Set myRS = Nothing
End If

myRS.Open "Select * From MyTable Order By MyField", myCN, adOpenKeyset, adLockOptimistic

Set Text1.DataSource = myRS
Text1.DataField = "FieldName"

End Sub


Or if you are using the ADO control, do this:


Private Sub Form_Load()

'establish connection string for ADO control
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDataBase.mdb;Persist Security Info=False"
'set SQL paramaters for ADO control
Adodc1.RecordSource = "Select * From MyTable Order By MyField"

Set Text1.DataSource = Adodc1
Text1.DataField = "MyField"

End Sub


That way, you do not have to worry about txtTitle.text=rs.Fields("Title").Value and the like, because the textboxes will automatically be filled with the corresponding values as you move through the recordset.
When the recordset moves to a record that has an empty field, the textboxes will be blank. Also, you will only get an error during the .Update procedure if the field's Required value is set to True.

Welcome to the world of ADO, by the way.! ;)

[bosse2]

Hi

You may want to fix the problem already in the database

when selecting a value use:

isnull(value,replacevalue)

This way you may save some coding

/Bo

[Franky]

I am impressed, it works now fine, i had the same problem as emmzou.

To answer the second not asked question from emmzou: with this system you don't need even to do anything for a update, except, just write rs.update

I love ADO ...


Every problem is now gone.

Only some fine tuning question:

When i leave, have i to destroy the Sets?
Set Text1.DataSource = nothing

Have i really to make a Set statement for every field?
I fear that my stacks get overloaded ....