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
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
186 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

837 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