Solved

When getting a value from a field in the database, how can I code it so I don't get errors if this field is contains no values

Posted on 2005-05-12
23
187 Views
Last Modified: 2010-05-01
  Here is a copy of my code:
I'm doing a SELECT from the database, I'm trying to get the VALUE of a field called LOOKUP_ORDERS
However if this field is blank, I get an error, my entire code after gets messed up. Specifically the error comes when I do the following code:
countIBGroup = rsIB(0) + 1  
This is because there is no value for rsIB(0) due to the fact the that database contains no values for the LOOKUP_ORDERS field.
What can I do to overcome this problem.  How can I do an if else statment similar to the one below?

PSEUDOCODE:
if (no values in the database) then
countIBGroup =1
else
countIBGroup = rsIB(0) + 1  
end if

 Dim rsIB
    Dim countIBGroup As Integer
    countIBGroup = 1
   
    CMD.CommandText = "SELECT TOP 1 LOOKUP_ORDER FROM LOOKUP_VALUES WHERE LOOKUP_NAME like 'IB_GROUP%' ORDER BY LOOKUP_ORDER DESC"
    CMD.CommandType = adCmdText
   
    'DMG (AH) -- Increment the highest LOOKUP_CODE value by one, so the new IB_GROUP records added to LOOKUP_VALUES are UNIQUE
    Set rsIB = CMD.Execute
   

     countIBGroup = rsIB(0) + 1

   
    CMD.CommandText = "Insert Into LOOKUP_VALUES (LOOKUP_NAME, LOOKUP_CODE, LOOKUP_DESC, LOOKUP_ORDER) Values('IB_GROUP', '" & CStr(countIBGroup) & "', '" & Replace(txtAddIBGroup.Text, "'", "''") & "',  '" & CStr(countIBGroup) & "')"
   
    Set rsIBGroup = CMD.Execute
0
Comment
Question by:angelahontau
  • 12
  • 6
  • 5
23 Comments
 
LVL 19

Accepted Solution

by:
Shauli earned 500 total points
ID: 13990741
If Not IsNull(rsIB(0)) Then
    countIBGroup = rsIB(0) + 1
Else
    countIBGroup = 1  '<<< or any other thing you may want to do here
End If

S
0
 
LVL 6

Expert Comment

by:PePi
ID: 13990756
countIBGroup = iif(IsNull(rsIB(0), 0, rsIB(0))

this means that if the field has Null value, it will default to 0. If you want it to default to 1 then change it to this:

countIBGroup = iif(IsNull(rsIB(0), 1, rsIB(0))



HTH!!!
0
 
LVL 6

Expert Comment

by:PePi
ID: 13990765
doh!!! Quickdraw Shauli!
0
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!

 
LVL 6

Expert Comment

by:PePi
ID: 13990774
even though Shauli beat me to it. I still have to make this correction

countIBGroup = iif(IsNull(rsIB(0), 1, rsIB(0) + 1)
0
 

Author Comment

by:angelahontau
ID: 13991311
Actually so far, none of them are working...  :(
Here is my code again, with the change...  There was a synthax error, so I added an extra paranthesis after IsNull(rsIB(0))
However it's still not working, it's giving me an "Automation error"
Can you guys help me figure out what I'm doing wrong?


    Dim rsIB
    Dim countIBGroup As Integer
    countIBGroup = 1
   
    CMD.CommandText = "SELECT TOP 1 LOOKUP_ORDER FROM LOOKUP_VALUES WHERE LOOKUP_NAME like 'IB_GROUP%' ORDER BY LOOKUP_ORDER DESC"
    CMD.CommandType = adCmdText
   
    'DMG (AH) -- Increment the highest LOOKUP_CODE value by one, so the new IB_GROUP records added to LOOKUP_VALUES are UNIQUE
    Set rsIB = CMD.Execute
         
    countIBGroup = IIf(IsNull(rsIB(0)), 1, rsIB(0) + 1)

    ' countIBGroup = rsIB(0) + 1

   
    CMD.CommandText = "Insert Into LOOKUP_VALUES (LOOKUP_NAME, LOOKUP_CODE, LOOKUP_DESC, LOOKUP_ORDER) Values('IB_GROUP', '" & CStr(countIBGroup) & "', '" & Replace(txtAddIBGroup.Text, "'", "''") & "',  '" & CStr(countIBGroup) & "')"
   
    Set rsIBGroup = CMD.Execute
0
 
LVL 6

Expert Comment

by:PePi
ID: 13991539
exactly which line produces the Automation Error?
0
 
LVL 6

Expert Comment

by:PePi
ID: 13991567
Have you declared and properly instantiated the Command Object? Also when doing your insert, you don't have to set it to a recordset since there should be no recordset to be returned anyways. Just do a CMD.Execute.

Also, in lieu of using the Command object when doing inserts you can use the Connection object. Probably somewhere in your code you are setting the .ActiveConnection of the Command object. use that same Connection object to execute the INSERT. Example:

Conn.Execute = "Insert Into LOOKUP_VALUES (LOOKUP_NAME, LOOKUP_CODE, LOOKUP_DESC, LOOKUP_ORDER) Values('IB_GROUP', '" & CStr(countIBGroup) & "', '" & Replace(txtAddIBGroup.Text, "'", "''") & "',  '" & CStr(countIBGroup) & "')"

Where Conn is your Connection object

0
 
LVL 6

Expert Comment

by:PePi
ID: 13991578
P.S. And thanks for correcting the missing parenthesis. Good eye! ;)
0
 

Author Comment

by:angelahontau
ID: 13992168
The Automation error is coming right after...
    countIBGroup = IIf(IsNull(rsIB(0)), 1, rsIB(0) + 1)
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13992190
"Right after" means on the line that begins with 'CMD.CommandText = "Insert Into LOOKU...', is that right?

S
0
 

Author Comment

by:angelahontau
ID: 13992245
Now, I'm getting a Type Mismatch error from rsIB(0)
Does that help?

This is the code I have now, I've included the entire SUB

Private Sub btnAddIBGroup_Click()
   
On Error GoTo ERR_EXIT

    Dim rsIBGroup, strMatchName
   
    Set CMD.ActiveConnection = Conn
     
    CMD.CommandText = "SELECT * FROM LOOKUP_VALUES WHERE LOOKUP_DESC like '" & Replace(txtAddIBGroup.Text, "'", "''") & "%' "
                               
    CMD.CommandType = adCmdText
           
    Set rsIBGroup = CMD.Execute
   
    Call AddAudit("FormAdminIBGroup", CMD.CommandText)
   
    'DMG (AH) -- This code looks for duplicates.  If recordCount > 0, the text box entry already exists in the database
    If (rsIBGroup.RecordCount > 0) Then
               
        strMatchName = "The following IBGroup Profile(s) already exist in the IB Activity Management System: " & vbCrLf
           
        Do While Not rsIBGroup.EOF
            strMatchName = strMatchName & vbCrLf & "- " & rsIBGroup("LOOKUP_DESC")
            rsIBGroup.MoveNext
        Loop
               
        strMatchName = strMatchName & vbCrLf & vbCrLf & _
                "Would you like to proceed with creating the IBGroup Profile: " & vbCrLf & txtAddIBGroup.Text
               
        If MsgBox(strMatchName, vbYesNo) = vbNo Then

            Exit Sub
        End If
       
    End If
   
    Dim rsIB
    Dim countIBGroup As Integer
    countIBGroup = 1
       
    Set CMD.ActiveConnection = Conn
    CMD.CommandText = "SELECT TOP 1 LOOKUP_ORDER FROM LOOKUP_VALUES WHERE LOOKUP_NAME like 'IB_GROUP%' ORDER BY LOOKUP_ORDER DESC"
    CMD.CommandType = adCmdText
   
    'DMG (AH) -- Increment the highest LOOKUP_CODE value by one, so the new IB_GROUP records added to LOOKUP_VALUES are UNIQUE
    'Set rsIB = CMD.Execute
         
    countIBGroup = IIf(IsNull(rsIB(0)), 1, rsIB(0) + 1)

    ' countIBGroup = rsIB(0) + 1

    CMD.CommandText = "Insert Into LOOKUP_VALUES (LOOKUP_NAME, LOOKUP_CODE, LOOKUP_DESC, LOOKUP_ORDER) Values('IB_GROUP', '" & CStr(countIBGroup) & "', '" & Replace(txtAddIBGroup.Text, "'", "''") & "',  '" & CStr(countIBGroup) & "')"
       
    Set rsIB = CMD.Execute

    Call AddAudit("FormAdminIBGroup", CMD.CommandText)
   
   
    loadIBGroup 'call this function to load the values from the database into the list box
   
    txtAddIBGroup.Text = ""
   
NORMAL_EXIT:
    Set CMD = Nothing
   
    Exit Sub
   
ERR_EXIT:
    Set CMD = Nothing
   
    MsgBox "Error: " & CStr(Err.Number) & vbCrLf & "Description: " & Err.Description, vbCritical
   
End Sub
0
 

Author Comment

by:angelahontau
ID: 13992252
No I mean when it hits this line it jumps to the error:
countIBGroup = IIf(IsNull(rsIB(0)), 1, rsIB(0) + 1)
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13992267
What is the datatype of rsIB(0) ?

S
0
 

Author Comment

by:angelahontau
ID: 13992333
No datatype just
Dim rsIB
0
 

Author Comment

by:angelahontau
ID: 13992346
It should be an array
How do I make it an array?
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13992433
LOOKUP_ORDER, what datatype is it?

S
0
 

Author Comment

by:angelahontau
ID: 13992503
numeric
number
integer

0
 

Author Comment

by:angelahontau
ID: 13992507
but if I change rsIB to integer instead of array, I no longer get any errors, but rsIB is empty, it doesn't get the value either...
I don't understand it
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13992572
<<due to the fact the that database contains no values for the LOOKUP_ORDERS field>>

Good. Now you dont get an error because datatypes are in place. Now all we have to make sure is that there is data in the table. Is there?

S
0
 

Author Comment

by:angelahontau
ID: 13992689
YES
0
 

Author Comment

by:angelahontau
ID: 13992704
Actually there's two errors one caused when there's no data in the Table, because I'm supposed to add the data to the table which is why I'm writing this code

So actually, no there's no data, becaues I'm supposed to add it
0
 

Author Comment

by:angelahontau
ID: 13992711
rsIB(0)  works great when there's data but when there's no data it gives an AUTOMATION ERROR
but there should be no data because the whole purpose of this code it to add data where there isn't any of this type
0
 

Author Comment

by:angelahontau
ID: 13992712
sorry I wrote YES, I got confused
0

Featured Post

Independent Software Vendors: 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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

685 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