angelahontau
asked on
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
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
Set rsIBGroup = CMD.Execute
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
Set rsIBGroup = CMD.Execute
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
doh!!! Quickdraw Shauli!
even though Shauli beat me to it. I still have to make this correction
countIBGroup = iif(IsNull(rsIB(0), 1, rsIB(0) + 1)
countIBGroup = iif(IsNull(rsIB(0), 1, rsIB(0) + 1)
ASKER
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
Set rsIBGroup = CMD.Execute
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
Set rsIBGroup = CMD.Execute
exactly which line produces the Automation Error?
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
Where Conn is your Connection object
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
Where Conn is your Connection object
P.S. And thanks for correcting the missing parenthesis. Good eye! ;)
ASKER
The Automation error is coming right after...
countIBGroup = IIf(IsNull(rsIB(0)), 1, rsIB(0) + 1)
countIBGroup = IIf(IsNull(rsIB(0)), 1, rsIB(0) + 1)
"Right after" means on the line that begins with 'CMD.CommandText = "Insert Into LOOKU...', is that right?
S
S
ASKER
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
'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
Set rsIB = CMD.Execute
Call AddAudit("FormAdminIBGroup
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
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
'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
Set rsIB = CMD.Execute
Call AddAudit("FormAdminIBGroup
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
ASKER
No I mean when it hits this line it jumps to the error:
countIBGroup = IIf(IsNull(rsIB(0)), 1, rsIB(0) + 1)
countIBGroup = IIf(IsNull(rsIB(0)), 1, rsIB(0) + 1)
What is the datatype of rsIB(0) ?
S
S
ASKER
No datatype just
Dim rsIB
Dim rsIB
ASKER
It should be an array
How do I make it an array?
How do I make it an array?
LOOKUP_ORDER, what datatype is it?
S
S
ASKER
numeric
number
integer
number
integer
ASKER
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
I don't understand it
<<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
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
ASKER
YES
ASKER
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
So actually, no there's no data, becaues I'm supposed to add it
ASKER
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
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
ASKER
sorry I wrote YES, I got confused
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!!!