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
183 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
Comment Utility
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
Comment Utility
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
Comment Utility
doh!!! Quickdraw Shauli!
0
 
LVL 6

Expert Comment

by:PePi
Comment Utility
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
Comment Utility
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
Comment Utility
exactly which line produces the Automation Error?
0
 
LVL 6

Expert Comment

by:PePi
Comment Utility
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
Comment Utility
P.S. And thanks for correcting the missing parenthesis. Good eye! ;)
0
 

Author Comment

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

Expert Comment

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

S
0
 

Author Comment

by:angelahontau
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:angelahontau
Comment Utility
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
Comment Utility
What is the datatype of rsIB(0) ?

S
0
 

Author Comment

by:angelahontau
Comment Utility
No datatype just
Dim rsIB
0
 

Author Comment

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

Expert Comment

by:Shauli
Comment Utility
LOOKUP_ORDER, what datatype is it?

S
0
 

Author Comment

by:angelahontau
Comment Utility
numeric
number
integer

0
 

Author Comment

by:angelahontau
Comment Utility
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
Comment Utility
<<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
Comment Utility
YES
0
 

Author Comment

by:angelahontau
Comment Utility
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
Comment Utility
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
Comment Utility
sorry I wrote YES, I got confused
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now