hei, i want to ask, i have a database, i want to load the field in database to txtbox, but if the value of the field is NULL then error message appear, run time error 94: Invalid use of NULL
how can i load the data in the field that is null, i mean, i dont care if it  load nothing, but please dont be error.

CmbAlasan.Text = rs(9)

in rs(9) value =NULL

any one help me Please...

Well... you could write a routine that handled it (like if you're checking numerous fields all over the place)

Otherwise it's just:

If IsNull(recordset.field(0)) Then         variable/object = ""     Else         variable/object=recordset.field(0)     End If

But as you can tell... it adds a lof of lines to the program, since it would have to be done for every field where the field could be null.

' In a module Public function NullField(byref theField as ADODB.Field) as string ' Either     If IsNull(theField) Then         NullField=""     Else         Nullfield=theField     End If ' OR     Nullfield=""     If Not IsNull(theField) Then         Nullfield=theField     End If end function

____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)

Just an addition JLRodger's hint.
You can spare some rows by using the IIf function.

txtFamilyName.Text = IIf(Not IsNull(rs.Fields("FamilyName").Value), rs.Fields("FamilyName").Value, vbNullString)      txtName.Text = IIf(Not IsNull(rs.Fields("Name").Value), rs.Fields("Name").Value, vbNullString)      txtBirthDate.Text = IIf(Not IsNull(rs.Fields("BirthDate").Value), rs.Fields("BirthDate").Value, vbNullString)      txtBirthCity.Text = IIf(Not IsNull(rs.Fields("BirthCity").Value), rs.Fields("BirthCity").Value, vbNullString)      txtAddress.Text = IIf(Not IsNull(rs.Fields("Address").Value), rs.Fields("Address").Value, vbNullString)

...but in this way the rows are very long and hard.readable.

Another method I use is to detect and bypass that specific error

Private Sub LoadMyFields() On Error GoTo ErrRoutine      txtFamilyName.Text = rs.Fields("FamilyName").Value      txtName.Text = rs.Fields("Name").Value      txtBirthDate.Text = rs.Fields("BirthDate").Value      txtBirthCity.Text = rs.Fields("BirthCity").Value      txtAddress.Text = rs.Fields("Address").Value      Exit Sub ErrRoutine:     If Err.Number = 94 Then Resume Next End Sub

Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers

hei, thx for the reply! it really helps the problem.
but now i have another problem, ehheh,
i want to delete  a record but if the record contain a field or more that have the null value(not all field is null, just some)  the program will show error! the message is "run-time error '-2147467259(80004005)':
Query-based delete failed because the row to delete cannot be found"
that. help me again plis

When working with text fields, no need for cheking the null value, just add empty string to it

text1.text=rs(0) & ""

____________________________
If you find the answer helpful, please mark this topic as solved.

goran, r u reply to my last post? i dont get it.
resume my last post, if i delete null field  with this cmd
it always error, how can i solved this?

Private Sub CmdDelete_Click()        If rs.RecordCount = 0 Then        MsgBox ("Unable to delete Record!"), vbCritical        Else        MsgBox ("Are you sure want to delete this record?"), vbOKCancel             If vbOK Then             rs.Delete  'always got stuck here!             rs.Update             rs.Requery             rs.MoveFirst             ShowText             End If     End If End Sub

and other thing that confused me, record count suppose to count how many record in a table, (CMIIW) but why it always return -1 no matter if there is 2 records or something.  

[Edited by elgy on 08-08-2005 at 02:55 PM GMT]

[Edited by elgy on 08-08-2005 at 03:01 PM GMT]

If that's your actual code...

1) If there's more than 1 record, you can't delete message appears, if there's under 1 record (none), you allow them to delete

2) Regardless of what the user selects from the message box, it'll delete (or attempt to) (the if statement will be true always, unless vbOK = 0, if so, then it will never delete)



____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)

quote:

---

JLRodgers wrote:
If that's your actual code...

1) If there's more than 1 record, you can't delete message appears, if there's under 1 record (none), you allow them to delete

2) Regardless of what the user selects from the message box, it'll delete (or attempt to) (the if statement will be true always, unless vbOK = 0, if so, then it will never delete)

---

hei, i've found the answer!
in vb
rs.delete is can not delete the record if it contain a field or more that has null value, CMIIW
then to delete a record then
con.execute ("delete name from report where name="elgy")
i've tried and it works well.

Nope, I was reffering to yours:

CmbAlasan.Text = rs(9)

in rs(9) value =NULL


Lets clarify some things here... The reason why you are having -1 for recordcount is because you are using some cursor that doesnt support counting records.

Example, adOpenStatic and adOpenKeyset cursors support adBookmark property, therefore recordcount property will retrieve the exact count of records. On the other hand, adOpenForwardOnly and adOpenDynamic dont support it (if I remember well), so it will always return -1 as recordcount.

In your example, you can use

If rs.bof or rs.eof Then
    MsgBox ("Unable to delete Record!"), vbCritical
Else
    if MsgBox ("Are you sure want to delete this record?"), vbOKCancel)= vbok then
        rs.Delete  'always got stuck here!
        .......
    endif
endif

no reason why it wont work, if you have created recordset with adequate CursorType and LockType.

At last, the method you have found can have unexpected results, if name field is not unique. For example, if you have 2 records that have name="elgy", then you will delete two records, not just the current one.

____________________________
If you find the answer helpful, please mark this topic as solved.

The RecordCount property of a forward-only recordset (ADO default), and of some server-side cursors, always returns -1.

To get the correct record count you can assign it to an array by the GetRows method, and then count the array items by the UBound function. Sometimes, when the records are too many, this approach could cause a memory leak. And anyway, we often need the number before processing the recordset.

A trick is to send an SQL instruction before the real recordset retrieving, as following

Dim rs As ADODB.Recordset     Dim sql As String, rsCount As Long     Set rs = New ADODB.Recordset     ' set an instruction to get the record count     sql = "SELECT COUNT(*) FROM MYTABLE"     rs.CursorLocation = adUseServer     rs.Open sql, cn, adOpenForwardOnly, , adCmdText     ' the retrieved recordset contains one single record with one single field     rsCount = rs(0)     rs.Close     sql = "SELECT * FROM MYTABLE ORDER BY ID"     ' get the real recordset     rs.Open sql, cn, adOpenForwardOnly, , adCmdText     ' ..... other code .....

When working with db engines which support multiple SQL intructions into a single query, such as MS SQL Server, you can group the two instructions, and pass them together to the engine, as following

Dim rs As ADODB.Recordset     Dim sql As String, rsCount As Long     Set rs = New ADODB.Recordset     ' set the two instructions together     ' (notice the semicolon at the end of the first instruction)     sql = "SELECT COUNT(*) FROM MYTABLE; " _      & "SELECT * FROM MYTABLE ORDER BY ID"     rs.CursorLocation = adUseServer     rs.Open sql, cn, adOpenForwardOnly, , adCmdText     ' the first recordset contains the record count     rsCount = rs(0)     ' the second recordset effectively contain the records     Set rs = rs.NextRecordset

This approach is a bit faster, as it requires only one single access to the server.

Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers

Hei thanks for the reply and explanation!
but i think im not good enough yet, so hard to understand what you  wrote. do you mind tell me bout what is cursor type, diferrences between 4 opt, and lock type as well. hehe.sorry for bother you.

Well, it's not a simple matter...

"A cursor is a set of values that represents a query's result" (F. Balena - Programming Visual Basic 6 - Microsoft Press)
A cursor is the recordset itself, but we speak about "cursors" to specify where the the query has been executed to retrieve the records.

Consider a simple query: "SELECT * FROM MYTABLE ORDER BY DESCRIPTION". This instruction returns all the fields of all the records of the table MYTABLE, and then sort the record by the Description field contents.

You can execute it on the server (say, pass the instruction to the database, asking it "Please give me back only the matching records"). In this case, it's the database engine that filters the database, and provides only the matching records. If the server is located on a network computer, only the matching records will move thru the network cable.

Or else, you can execute the query on the client (the pc on which your VB app is currently running). In this case you will retrieve the whole database - a lot of data thru the network cable - and then you filter the database to search only the matching records.
The advantage of this method (client-side cursor) is that your query will probably be the only one executed on the whole database, while in the other case the server has to provide records to many clients together. And client-side cursors often expose some other properties, such as RecordCount.
The server-side method (server-side cursor) advantage is that often, in a network, the server is a more performant machine, so query execution is faster. And more, we have a lower traffic of data thru the cable. This is the meaning of the CursorLocation property.

The cursor type depends on the provider (the db engine) you use. The four avaliable types of ADO, from the fastest to the slowest, are Forward-Only, Static, Dynamic and Keyset. The slower ones offer more features, but we pay them by performance leak.

The lock is the way you block a record while you edit it, in order to avoid two users can edit the same record at the same time. There are four options, but in plain words we can say that from the optimistic to the pessimistic to the read-only, the lock is always less permissive. You can fnd more on the online help.

However I suggest youa good book about ADO or VB6, where all these things are better explained.

____________________________
Real Programmer can count up to 1024 on his fingers

Oooh thx for the reply, i appreciate it, hehe, even its long and complicated you still answered my question well, ok, i'll find something good to explain bout this thing. thx very much

i have problem when want to delete record from listview. the error is "either bof or eof is true, or the current record has been deleted. requested operation requires a current recod"..

my coding is:
Dim DB As Database
    Dim rs As Recordset
    Dim mntDel As Integer
    Set DB = OpenDatabase(App.Path + "\iwanie.mdb")
    ' Open recordset (table: staff) in database DB
    Set rs = New ADODB.Recordset
        rs.Open "Select * From staff WHERE staff_id=" & lngStaffID, cn, adOpenKeyset, adLockOptimistic
        mntDel = MsgBox("Are you sure to delete the entry for " & lvwAllStaff.ListItems.Item(ItemIndex).Text & "?", vbQuestion + vbYesNo, "Delete?")
    If mntDel = vbYes Then
        rs.Delete
        rs.Update
        frmAllStaffRecord.Show
    End If

the error is highlighted at rs.Delete

even i also have the same error when trying to update record..my coding:
Set rs = New ADODB.Recordset
    rs.Open "Select * From staff WHERE staff_id=" & lngStaffID, cn, adOpenKeyset, adLockOptimistic
    With rs
        .Fields("name") = txtStaffName
        .Fields("ic") = txtStaffIC
        .Fields("gender") = cboStaffGender
        .Fields("address") = txtStaffAdd1
        .Fields("state") = txtStaffState
        .Fields("contact_no") = txtStaffContact
        .Fields("mode") = cboStaffMode
        .Fields("status") = cboStaffStatus
        .Fields("password") = txtStaffPassword
        .Update
        .Close
    End With
    
    Set rs = Nothing
    
    'sanitation
    txtStaffName = ""
    txtStaffIC = ""
    cboStaffGender.ListIndex = -1
    txtStaffAdd1 = -1
    txtStaffState = -1
    txtStaffContact = ""
    cboStaffMode.ListIndex = -1
    cboStaffStatus.ListIndex = -1
    txtStaffPassword = ""
    txtStaffName.Text = ""
    txtStaffName.SetFocus
  
    strStaffName = ""
    lngStaffIC = 0
    strStaffGender = ""
    strStaffAddress = ""
    strStaffState = ""
    strStaffContact = ""
    strStaffMode = ""
    strStaffStatus = ""
    strStaffPassword = ""
  
    'updation is complete, now time to close the form
    Unload Me
    frmAllStaffRecord.Show

the error is always highlighted at .Fields("name") = txtStaffName

Hello. It seems to me that the error is on the value of the lngStaffID variable, and you didn't post the code you use to set it.

You open a single record recordset, and this single record should be the current record, but it isn't. So the recordset has to be empty (you can test it for not being empty by an instruction ... If Not (rs.BOF And rs.EOF) Then.... before executing the deletion), as no other record can be current.
The only reason for retrieving an empty recordset is that the value compared in the WHERE clause is not and existing value (aka, the number in lngStaffID is not present in the staff_id field).
Check the code in which you assign a value to lngStaffID, maybe by some breakpoints during the run.

Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers

Hi,
Without detriment to anything in yronium's reply, you do seem to be a bit confused with your data retrieval methods. You Dim DB As Database - and then :
Set DB = OpenDatabase(App.Path + "\iwanie.mdb")
    ' Open recordset (table: staff) in database DB

but you don't - you
    Set rs = New ADODB.Recordset
and open it with :-
  rs.Open "Select * From staff WHERE staff_id=" & lngStaffID, cn, adOpenKeyset, adLockOptimistic

DB and the OpenDatabase commands are DAO
BUT
rs and the Open command using "cn" (connection) are ADO.

You don't need the DB bit if you are using ADO.




____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hi everyone
Just thought I'd throw my tuppence worth in too regarding null records.
I hate having to deal with null records in my code so I tend to create my databases with empty strings as default rather than nulls for all text fields.
Whilst it takes a tad longer to design each table, the time saved in coding traps for nulls makes up for it.
Does anyone know why Microsoft doesn't set empty string as the automatic default?
Dave

____________________________
While Breath.Count>0
       Live(gbRelax)
Wend

"" implies that the user entered information, but it was blank

null implies that the user didn't enter any information (or was never prompted to enter the field)

Like if you had a field of "sendnewsletter" and it was null, you'd know that they never requested nor denied a newsletter, but if it was "" they had the chance of requesting, but entered nothing.

____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)

An empty field contains only the field terminator, one byte; a blank space contains a Null char (it'd be Chr(255), I seem to remember...) and the field terminator, so two bytes total.
When you execute a query to manage Null values, the blank fields are skipped, as they're not empty to the db engine.

____________________________
Real Programmer can count up to 1024 on his fingers

Thanks, guessed there had to be a reason, well a couple of reasons the way the two of you have explained it.
By the way I think Null is Chr(0) isn't it? My book says 255 is a y with an omlaut (how do you spell that?)  
Dave

____________________________
While Breath.Count>0
       Live(gbRelax)
Wend

thank you for your help..thank you so much..

A trick is to send an SQL instruction before the real recordset retrieving, as following

Dim rs As ADODB.Recordset     Dim sql As String, rsCount As Long     Set rs = New ADODB.Recordset     ' set an instruction to get the record count     sql = "SELECT COUNT(*) FROM MYTABLE"     rs.CursorLocation = adUseServer     rs.Open sql, cn, adOpenForwardOnly, , adCmdText     ' the retrieved recordset contains one single record with one single field     rsCount = rs(0)     rs.Close     sql = "SELECT * FROM MYTABLE ORDER BY ID"     ' get the real recordset     rs.Open sql, cn, adOpenForwardOnly, , adCmdText     ' ..... other code .....

once again,yronium never fails to amaze me.thanks a lot, i was browsing through old posts and found this.very helpfull.       

____________________________
united we stand,divided we stand alone...

    

____________________________
Real Programmer can count up to 1024 on his fingers