need help *RESOLVED*

I keep getting an error in my code. So I founf some code to handle the error an write it to log file. It errors out on my Select statement in the attahced code.

Error Log:

381,"Invalid property array index","SetDetails",#2003-06-25 14:13:21#
-2147217908,"Command text was not set for the command object.","SetDetails",#2003-06-25 14:13:23#
3704,"Operation is not allowed when the object is closed.","SetDetails",#2003-06-25 14:13:23#

VB Code:

Private Sub cmdSTP2Next_Click()
 
 On Error GoTo Err_handler
 
Screen.MousePointer = vbHourglass
    Call CreateConnection(objConn)
    Set objRs = New ADODB.Recordset
 
        objRs.ActiveConnection = objConn
        objRs.CursorLocation = adUseClient
        objRs.CursorType = adOpenDynamic
        objRs.LockType = adLockOptimistic
        objRs.Source = "select * from CAMPAIGNDETAILS where cd_id = '" & frmIntro.txtGlobalID.Text & "' and table_id_number = '" & lstBio2.ItemData(iCount) & "'"
        MsgBox objRs.Source
        objRs.Open
 
        'this checks to see if the records exist if it does don't add it
       With objRs
            If objRs.EOF Then
                For iCount = 0 To lstBio2.ListCount - 1
                    sSQL = "INSERT INTO CAMPAIGNDETAILS(cd_id, cd_type, table_id_number) Values ('" & frmIntro.txtGlobalID.Text & "',('BIO')," & lstBio2.ItemData(iCount) & ")"
                    objConn.Execute (sSQL)
                    'MsgBox lstBio2.ItemData(iCount)
                Next iCount
            End If
                'Debug.Print Error
       End With
 
        Set objRs = Nothing
    Call CloseConnection(objConn)
 
  Call closeAllFrames
        
'------------------ determin which frame to load based on chekkbox value ---------
    If frmIntro.txtTest.Text = 1 Then
        Call LoadTestimonials
    ElseIf frmIntro.txtAud.Text = 1 Then
        Call LoadAudience
    ElseIf frmIntro.txtPort.Text = 1 Then
        Call LoadPortfolios
    ElseIf frmIntro.txtPR.Text = 1 Then
        Call LoadPressReleases
    ElseIf frmIntro.txtNews.Text = 1 Then
        Call LoadNewsArticles
    Else
        frmIntro.fraContactInfo.Visible = True
    End If
    
Screen.MousePointer = vbDefault
 
Err_handler:
   If Err.Number <> 0 Then
      errLogger Err.Number, Err.Description, "SetDetails"
      Err.Clear
        
      Resume Next
     
    End If
End Sub

I am guessing you have table_id_num as a numeric datatype right? Well if so you need to get rid of the quotes around it. Also if cd_id is numeric then you will need to make that without quotes as well.

VB Code:

objRs.Source = "select * from CAMPAIGNDETAILS where cd_id = '" & frmIntro.txtGlobalID.Text & "' and table_id_number = " & lstBio2.ItemData(iCount)

Hey Maldrid,

Actually all the fields are set to "Text". Should they be numeric?

No you can leave them as text then. Just make sure you fix your INSERT statement then to include quotes around table_id_num.

VB Code:

sSQL = "INSERT INTO CAMPAIGNDETAILS(cd_id, cd_type, table_id_number) Values ('" & frmIntro.txtGlobalID.Text & "','BIO','" & lstBio2.ItemData(iCount) & "')"

What does this actually return lstBio2.ItemData(iCount)?
That is the only section I see that could return an invalid array index as your error log shows. Make sure lstBio2.ItemData(iCount) is correct because iCount might be out of range.

The lstBio2.itemData(iCount) is returning the id number of the item in the list box. Not the actual iCount. Ex. the iCount might be 2 indicating there are two items in the list, but the itemData(iCount)might be 12000 and 19800 which represent the ID of the item.

Just for grins.. If I strip away everthing and just run this code I don't have any problems, except if I hit my back button on my form and then hit the next button I'll get duplicate records in the database, hence the reasone for the rest of the code

VB Code:

For iCount = 0 To lstBio2.ListCount - 1
                    sSQL = "INSERT INTO CAMPAIGNDETAILS(cd_id, cd_type, table_id_number) Values ('" & frmIntro.txtGlobalID.Text & "',('BIO')," & lstBio2.ItemData(iCount) & ")"
                    objConn.Execute (sSQL)
                    'MsgBox lstBio2.ItemData(iCount)
                Next iCount

Of course that is going to work. You are resetting iCount to 0 and it can only go up to the listcount - 1 when you use your for loop. I mean what is iCount when you go to the Select statement to open up your recordset. It seems as if iCount is out of range and that is why you are getting your "Invalid property array index" error. I also still don't see how you have table_id_num in quotes on your select statement but not on your Insert statement.

Sorry:eek: Thats an error on my part. When I run the app and it crashes it does so on the SELECT line and if I mouse over the lstBio2.ItemData(iCount), while it's highlited, the iCount = 4

Ok iCount is 4, now what is ItemData(iCount) when you select it? Or is iCount out of range?

When I run the app and it bombs, wether I am in the Select statement or the Insert statement the listbio2.ItemData(iCount) the iCount=4.

Yea I know that iCount is 4 now. What is lstbio.ItemData(iCount) when iCount = 4? Basically what does lstbio.ItemData(4) return?

I'm sorry. I don't understand. How can I get the answer you looking for? :confused:

Put a breakpoint on the Select statement line. When your application stops at that line. Move your mouse pointer to lstbio and keep it there until a tooltip box comes up and it shows you what the value is. If the value shows that your index is out of range then your iCount is too high.

Ok, I understand now. I did that, and the toolTip for the listbio2.ItemData(iCount) says iCount=4


Sorry, If I hold it directly over the lstBio it says this:

listbio2.ItemData(iCount) =<invalid property array index>

Did you move your mouse over iCount or over lstbio2? It has to be over lstbio2 and not over iCount in that statement. If you still don't understand do this for me. At the very beginning of your procedure set iCount = 0.

Like this

VB Code:

Private Sub cmdSTP2Next_Click()
 
 On Error GoTo Err_handler
 
Screen.MousePointer = vbHourglass
    Call CreateConnection(objConn)
    Set objRs = New ADODB.Recordset
        
        [COLOR=red]iCount = 0[/COLOR]
    
        objRs.ActiveConnection = objConn
        objRs.CursorLocation = adUseClient
        objRs.CursorType = adOpenDynamic
        objRs.LockType = adLockOptimistic
        objRs.Source = "select * from CAMPAIGNDETAILS where cd_id = '" & frmIntro.txtGlobalID.Text & "' and table_id_number = '" & lstBio2.ItemData(iCount) & "'"
        MsgBox objRs.Source
        objRs.Open

Now does your program still crash on select and if so what is the error it gives?

Ok, I did that and it doesn't crash anymore.:D

Now explain to me why we added the icount = 0, other than it just sets everything to zero:)

Ok when you store things in a listbox under itemdata it stores it in a array right. To access each element in the array you use an index as in your example your index was iCount.

ItemData(0) This accesses the first element in the array
ItemData(1) This accesses the second element in the array

Now in your example you had iCount = 4 everytime you got into the subroutine so basically it was

ItemData(4) This accesses the fifth element in the array.

Basically your listbox did not have a fifth element in the array so it was giving you an index error. Now I set it to zero because I knew you atleast had one item in your array.

I am not sure exactly what you want the iCount to equal but it can not be greater than how many elements you have in the array. What I am guessing is you are trying to check to see if the items you have in your listbox is in the database. If it isn't then you want to put in the database right? I am not sure how you have your listbox set up and in what order you have your records coming so I can't help you with it, but I will tell you that your code will need to be changed or you will have duplicate records. Unless of course you have primary keys set up based on those fields, but then you will get error messages about trying to insert a duplicate record.

Thanks Maldrid:)

I was indeed having trouble with duplicate records being added to the database. Thats what got me started on this whole section of code. I did the Select statement to see if there were any records that matched what I am going to insert. If there are records already present then don't add them else add them.

Thats what this part of the code is doing. I think.

VB Code:

'this checks to see if the records exist if it does don't add it
       With objRs
            If objRs.EOF Then
                For iCount = 0 To lstBio2.ListCount - 1
                    sSQL = "INSERT INTO CAMPAIGNDETAILS(cd_id, cd_type, table_id_number) Values ('" & frmIntro.txtGlobalID.Text & "',('BIO')," & lstBio2.ItemData(iCount) & ")"
                    objConn.Execute (sSQL)
                    'MsgBox lstBio2.ItemData(iCount)
                Next iCount
            End If
                'Debug.Print Error
       End With

This

Well the logic could and could not be doing what you say. It depends on how your list box is set up. Do all items in the list box have the same id that is returned by the lstbio2.itemdata(iCount)? Because that is what you select statement is looking for, now if they aren't all the same you are only checking if the first item in the listbox is also in the database. If that item isn't in the list box then you are adding all the items from the listbox into the database. Also if that item is in the database you are adding none, which could be wrong since maybe new items were put in the listbox. If I were you I would do more of a generic select statement and pull up all records that should be in both the listbox and database and then check record by record and if it isn't in the database add it. You can do that in a lot of ways but if you need help let me know. I will need to know what the database field sare in the table that you are working on and also what determines a duplicate record.

Maldrid,

I was doing some testing today and I was able to generate a dubplicate record. So I was wondering if your offer to help was still good and if you just want to post stuff from the vb forums?

Sorry to jump in here, but I don't see any code that checks to see if there is already a record in the database matching the one you are trying to insert.

This snippet from your code:

VB Code:

'this checks to see if the records exist if it does don't add it
       With objRs
            If objRs.EOF Then
                For iCount = 0 To lstBio2.ListCount - 1
                    sSQL = "INSERT INTO CAMPAIGNDETAILS(cd_id, cd_type, table_id_number) Values ('" & frmIntro.txtGlobalID.Text & "',('BIO')," & lstBio2.ItemData(iCount) & ")"
                    objConn.Execute (sSQL)
                    'MsgBox lstBio2.ItemData(iCount)
                Next iCount
            End If
                'Debug.Print Error
       End With

is commented to say that it is checking the database, but the ONLY thing it checks is whether or not objRs is at EOF. It then says, if objRs is at EOF, then insert a record.

Where is the checking to see if that record is already in the database?

doofusboy,

I think I am checking the database with this select statment. But I'm laerning as I go.

snippet from first post

VB Code:

Call CreateConnection(objConn)
    Set objRs = New ADODB.Recordset
 
        objRs.ActiveConnection = objConn
        objRs.CursorLocation = adUseClient
        objRs.CursorType = adOpenDynamic
        objRs.LockType = adLockOptimistic
        objRs.Source = "select * from CAMPAIGNDETAILS where cd_id = '" & frmIntro.txtGlobalID.Text & "' and table_id_number = '" & lstBio2.ItemData(iCount) & "'"
        MsgBox objRs.Source
        objRs.Open

The following SQL statement will return a record that is equal to the one you are trying to add:

SELECT cd_id, cd_type, table_id_number
FROM CAMPAIGNDETAILS
WHERE cd_id = 'frmIntro.txtGlobalID.Text'
and cd_type = 'BIO'
and table_id_number = lstBio2.ItemData(iCount)

If no records are returned by that SQL statement, you can safely add the new one.

The checking needs to be done inside the IF statement where you have your INSERT statement.

How can I run the Select statement from inside the IF part?:confused:

Hey Navarone-

Tell me all the fields that are in the database table CAMPAIGNDETAILS and also the fields that are in the list box. Then tell me what makes a duplicate record and I will help you write you logic.

Ok, in the Campaign Details tabel I have the following fields:
cd_ident(autonumber)
cd_id(generated id)
cd_type(Campaign, Bio, Testimonial, Audience, Portfolio, Press Release, News Article, ContactInfo)
tabel_id_number(id of Bio, Testimonial, etc..)

For each cd_id there is a tabel_id_number. You can't have a duplicate tabel_id_number for the same cd_id (see attached)

Each cd_type has it's own table with specific information.

When my form loads there are two list boxes. Example Bio's,
I have lstBio1.list and lstBio2.list. I have "add" and "remove" buttons to add items from lstBio1 to lstBio2. (In this way the user can create there own list of bio's from those in the database)

I also have back and next buttons to move between the forms.

When the bios load into the first list box I load the b_name and b_id from the Bio's table. When I add them to the second listbox I need the same information.

When I hit the next button, I insert into the CampaignDetails table the cd_type (Bio) and the table_id_number(b_id) and the cd_id(Campaign ID).

Of course if I remove something from the list I also need to make sure it gets deleted from the CampaignDetail table. If I go Back one form then go forward using the Next button, I need to make sure the items in the second list box don't get duplicated in the CapaignDetail table.

Here is something else that might help you understand.

Ok I think I understand it, but I have a few questions. The cd_id, you say you store that in the database when you do the insert right? Now is cd_id stored in the list box under lstbio2.Itemdata(iCount)? If so how do you generate cd_id in order to store it in ItemData?. Is cd_id pulled from a different table depending on cd_type? The cd_type value based on each screen you have? Because right now you only have cd_type "Bio" right? Each screen has a different cd_type right?

I think your best bet is not to have records inserted into the database on your next and back command buttons. I think you should have your records inserted and deleted on your add and remove buttons. When you add and remove stuff from the lstbio2 you should also have it delete or insert into the database as well. Now when you add a record from lstbio1 to lstbio2. Does it remove the record from lstbio1? Or does it just add a record to lstbio2 and still keeps the record in lstbio1? Actually better yet just give me your code on your add and remove command buttons.

This code loads the information into the lstBio1 list box.

VB Code:

Function LoadBios()
 
'clear list box if back button used then next button
frmIntro.lstBio1.Clear
 
 Call CreateConnection(objConn)
        Set objRs = New ADODB.Recordset
    
            objRs.ActiveConnection = objConn
            objRs.CursorLocation = adUseClient
            objRs.CursorType = adOpenDynamic
            objRs.LockType = adLockOptimistic
            objRs.Source = "select * from Bios "
            'MsgBox objRs.Source
            objRs.Open
        
        Do Until objRs.EOF() 'adds record then loops thru and gets next record till all records found
            
        '/////// ADDING RECORDS TO LISTBOX
            frmIntro.lstBio1.AddItem objRs!b_name '& objRs!b_id
            'MsgBox objRs!b_id
            frmIntro.lstBio1.ItemData(frmIntro.lstBio1.NewIndex) = objRs!b_id
            
            objRs.MoveNext
        Loop
        
        Set objRs = Nothing
        Call CloseConnection(objConn)
        frmIntro.fraStep2.Visible = True 'load step2 bios
End Function

Here is the code that I use to select from lstBio1 and add to the lstBio2 and the code I use to remove.

VB Code:

'--------------------------- add bios to list box 2
Private Sub cmdAddBio_Click()
 
    Found_It = False
 
    Screen.MousePointer = vbHourglass
    For iCount = 0 To lstBio1.ListCount - 1
    'MsgBox iCount
        If lstBio1.Selected(iCount) = True Then
            lstBio2.Text = lstBio1.List(iCount)
 
            If lstBio1.Text = lstBio2.Text Then
               'MsgBox lstBio2.Text
                Found_It = True
                MsgBox lstBio2.Text & "has already been added"
                Exit For
            End If
 
            If Found_It = False Then
                lstBio2.AddItem lstBio1.Text
                lstBio2.ItemData(lstBio2.NewIndex) = lstBio1.ItemData(iCount)
                lstBio2.SetFocus
            End If
        End If
    Next iCount
    Screen.MousePointer = vbDefault
 
End Sub

And this is the code I use to remove.

VB Code:

Private Sub cmdRemoveBio_Click()
    Screen.MousePointer = vbHourglass
    
    iCount = 0
    
    If lstBio2.ListCount = 0 Then
        MsgBox "There are no items to remove"
    ElseIf lstBio2.ListIndex = -1 Then
        MsgBox "You must first select and item to remove it"
    Else
        'remove item from list box
       lstBio2.RemoveItem (lstBio2.ListIndex)
        'delete item selected in list box from CampaignDetails table
        Call CreateConnection(objConn)
        objConn.Execute ("Delete from CampaignDetails where cd_id = '" & frmIntro.txtGlobalID.Text & "' and table_id_number = '" & lstBio2.ItemData(iCount) & "'")
        Call CloseConnection(objConn)
    End If
    Screen.MousePointer = vbDefault
End Sub

When I create a campaign, I generate a campaign id, c_id. I store this in the Campaign table, I then insert the c_id into the CampaignDetails table as cd_id.

Hope that helps:)

Can more than one item be selected at a time from lstBio1? Or only one item can be selected and added or removed at a time?

hmm... Ideally the user should be able to select one or all items. But I was happy with getting just one to work:) I'm happy with one item for now, and if selecting more than one at a time becomes an issue I can always add that. (maybe in the next revision.)

Also does lstBio2 come in blank everytime you start up the form? Because you load up listBio1 with what is in the database but I don't see where you load lstBio2. What if the user added records to lstBio2 and saved them in the database right. Then if the user went back in the screen wouldn't lstBio2 have the records that they previously added?

When the user creates a campaign, lstbio2 loads initially blank. They can only add records from lstBio1. The user cannot add more then one of whatever items are in lstBio1. (so no duplicates in the lstBio2) They also cannot type in a item and save it.

All the items in the lstBio1 have been provided by a adminstrator. If more items need to be added to the lstBio1 then the admin has to do that. (The admin section of my program is already complete)

If the user selects the back button going to a previous form then on that previous form hit the next button going back to Bio's, or whereever, then the users selections would still be in lstbio2, as long as the user remains within the campaign. If they wish to exit the campaign, then they can save there "stuff" and return to it later.

No you don't understand. Ok lets say the user goes in to the Bio form and adds items b_id = 12786 and b_id = 17017 right. Those items are added into lstBio2 and utimately added into the database table CampaignDetails right? Well now the user exits the whole program and then another user goes into the program and goes to Bio Form. They will now see lstBio1 with all the records that are in the database, but lstBio2 will be blank. They will never know that the previous user added those two items in to CampaignDetails. Now if they go and add the two records won't that cause a problem? Or do you just want logic to check if those items were previously entered and then just not enter them. I just think everytime you go into the screen you load lstBio1 using table Bios and lstBio2 using table CampaignDetails so that the user can see if anyone added records previously to CampaignDetails. Wouldn't that make more sense?

When a user creates a campaign, a unique ID is created just for that campaign. Everything that the user adds to lstbio2 is assigned to that campaign id. If another user comes along they have to create a brand new campaign. The application is for single users, but on the off chance that some one else wanted to use the same application, they would have to create a new campaign. :)

Ok so I am guessing those screens are only for adding compelety new Campaign records. If a user wants to view what is in a particular Campaign they will have to go to a different part of the program right? Also why do you keep putting frmIntro in front of all your controls? Such as frmIntro.lstBio1. You don't need to reference a control by using the form name if the code is within the form. Only if the code is outside the form do you need to reference a control like that. Otherwise you can just put lstBio1.

You are correct, if the user wants to view or edit a campaign theres another part of the program they can go to. This part is just for building the campaign.

I reference the list boxes with frmIntro, because I was using frames but if I don't have to, I wont. I learned something?:)

Nay you shouldn't have to regardless if you are using frames as long as the frames are on the same form it don't matter. Ok I went through your code and this is what I think it should be.

VB Code:

Function LoadBios()
    Dim i As Integer
'clear list box if back button used then next button
lstBio1.Clear
 
 Call CreateConnection(objConn)
        Set objRs = New ADODB.Recordset
    
            objRs.ActiveConnection = objConn
            objRs.CursorLocation = adUseClient
            objRs.CursorType = adOpenDynamic
            objRs.LockType = adLockOptimistic
            objRs.Source = "select * from Bios "
            'MsgBox objRs.Source
            objRs.Open
        
        For i = 1 To objRs.RecordCount 'adds record then loops thru and gets next record till all records found
            
        '/////// ADDING RECORDS TO LISTBOX
            lstBio1.AddItem objRs!b_name '& objRs!b_id
            'MsgBox objRs!b_id
            lstBio1.ItemData(lstBio1.NewIndex) = objRs!b_id
            
            objRs.MoveNext
        Next
                
        objRs.Close
        Set objRs = Nothing
        Call CloseConnection(objConn)
        'frmIntro.fraStep2.Visible = True 'load step2 bios
End Function
'--------------------------------------------------------------------------------
 
 
 
'Here is the code that I use to select from lstBio1 and add to the lstBio2 and the code I use to remove.
 
 
 
'visual basic code:--------------------------------------------------------------------------------
'--------------------------- add bios to list box 2
Private Sub cmdAddBio_Click()
    Dim sAdd As String
    
    Found_It = False
    
    If lstBio1.ListIndex <> -1 Then
        sAdd = lstBio1
 
        Screen.MousePointer = vbHourglass
        For iCount = 0 To lstBio2.ListCount - 1
            
            lstBio2.ListIndex = iCount
            
            If sAdd = lstBio2 Then
                Found_It = True
                Exit For
            End If
        Next
        
        If Found_It Then
            MsgBox "This item has already been selected"
            lstBio2.ListIndex = -1
        Else
            lstBio2.AddItem lstBio1.Text
            iCount = lstBio2.NewIndex
            lstBio2.ItemData(lstBio2.NewIndex) = lstBio1.ItemData(lstBio1.ListIndex)
            
            Call CreateConnection(objConn)
            
            sSQL = "INSERT INTO CAMPAIGNDETAILS(cd_id, cd_type, table_id_number) Values ('" & txtGlobalID.Text & "',('BIO')," & lstBio2.ItemData(iCount) & ")"
            objConn.Execute (sSQL)
            
            lstBio2.ListIndex = -1
        End If
 
    End If
    
    Screen.MousePointer = vbDefault
 
End Sub
'--------------------------------------------------------------------------------
 
 
'And this is the code I use to remove.
 
'visual basic code:--------------------------------------------------------------------------------
Private Sub cmdRemoveBio_Click()
    Screen.MousePointer = vbHourglass
    
    iCount = 0
    
    If lstBio2.ListCount = 0 Then
        MsgBox "There are no items to remove"
    ElseIf lstBio2.ListIndex = -1 Then
        MsgBox "You must first select and item to remove it"
    Else
        'delete item selected in list box from CampaignDetails table
        Call CreateConnection(objConn)
        objConn.Execute ("Delete from CampaignDetails where cd_id = '" & txtGlobalID.Text & "' and table_id_number = '" & lstBio2.ItemData(lstBio2.ListIndex) & "'")
        Call CloseConnection(objConn)
        'remove item from list box
        lstBio2.RemoveItem (lstBio2.ListIndex)
    End If
    Screen.MousePointer = vbDefault
End Sub

Also fix your next and back buttons to not include the code that adds records to the database such as this. Since your add and remove buttons do that already.

VB Code:

Private Sub cmdSTP2Next_Click()
 
 On Error GoTo Err_handler
 
Screen.MousePointer = vbHourglass
 
  Call closeAllFrames
        
'------------------ determin which frame to load based on chekkbox value ---------
    If frmIntro.txtTest.Text = 1 Then
        Call LoadTestimonials
    ElseIf frmIntro.txtAud.Text = 1 Then
        Call LoadAudience
    ElseIf frmIntro.txtPort.Text = 1 Then
        Call LoadPortfolios
    ElseIf frmIntro.txtPR.Text = 1 Then
        Call LoadPressReleases
    ElseIf frmIntro.txtNews.Text = 1 Then
        Call LoadNewsArticles
    Else
        frmIntro.fraContactInfo.Visible = True
    End If
    
Screen.MousePointer = vbDefault
 
Err_handler:
   If Err.Number <> 0 Then
      errLogger Err.Number, Err.Description, "SetDetails"
      Err.Clear
        
      Resume Next
     
    End If
End Sub

Ok let me put in my vb app and do some testing. :)

Thanks for you help. I'll yelp! if something goes amiss;)

Ok, I tried the code and everything works ok. I checked the db and watched if it added duplicates or not and it didn't add them.:D

I really appreciate the time and effort you took. I hope I can return the help someday:)

No problem, just glad it works. Make sure to go to your first post and edit the title to say Resolved.