Results 1 to 7 of 7

Thread: Item cannot be found in the collection corresponding to the requested name or ordinal

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    8

    Item cannot be found in the collection corresponding to the requested name or ordinal

    I know this error has been discussed before but none of the solutions have been working for me.

    I have a VB6 application that imports data into a SQL Server 2005 database via a stored procedure that uses bulk insert with data from a text file. My application builds the query string and uses an ADODB.Connection object's Execute function to send the query to the SQL database. When I do that, I get the aforementioned error. However, when I run the application in debug mode, copy and paste the query string into SQL Management Studio, the query works just fine.

    Here's what really has me confused: Everything worked just fine last Friday. The code in the application hasn't changed. The stored procedure hasn't changed. I'm connecting to the same SQL Server database with the same user. The database is on my local machine. The only thing that has changed is the database but it was created with the same SQL script that I used to create the one I was using on Friday.

    I've checked and rechecked table and database names. I've restarted my computer. I've rebuilt the database. I tried Set NOCOUNT ON. I made sure all of my connections are correct. The only thing that I haven't done is re-install SQL Server.

    I'm out of ideas. Any help would be greatly appreciated.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Item cannot be found in the collection corresponding to the requested name or ord

    Welcome to VBForums

    Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

    It is rather unusual to get that error when just executing a statement, can you show us the code you are using, and tell us which line the error occurs on?

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    8

    Re: Item cannot be found in the collection corresponding to the requested name or ord

    Here is the code in the VB6 function:
    Code:
    10    sMsg = getCountFromDatabase(lDataCountBeforeInsert, lEventCountBeforeInsert, lLogCountBeforeInsert)
        If sMsg <> "" Then
            Debug.Print sMsg
            Call createLog(g_LogFile, sMsg, ForAppending)
        End If
        
        On Error GoTo errDatabase
        Call m_dbconn.BeginTrans
        sSql = "sp_BulkInsert " & "'data', '" & sImportFileDest & "data.txt'"
    20    m_dbconn.Execute (sSql)
    It errors on line 20. sImportFileDest is the absolute path to the directory data.txt is in. m_dbconn is an ADODB.Connection object. Everything prior in this function is declarations and everything after is a slight variation on the same call but with different files. getCountFromDatabase just returns the number of records currently in the tables that will be written to.

    Here is the sp_BulkInsert stored procedure (in case you're wondering):
    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    ALTER   procedure [dbo].[sp_BulkInsert](@TableName as varchar(50), @FileName as varchar(500))
    as
    begin
    	declare @sSql as varchar(1000)
    
    	if @TableName = 'data'
    	begin
    		truncate table DataToCopy
    		set @sSql = 'BULK INSERT '  + 'DataToCopy' + ' FROM ''' +  @FileName + ''' WITH (FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\n'')'
    		print @sSql
    		exec (@sSql)
    
    		delete DataToCopy
    			from DataToCopy a join data b on (a.Track = b.Track and a.Datatime = b.Datatime)
    
    		insert into data 
    		select a.*
    			from DataToCopy a 
    
    		truncate table DataToCopy
                   end
    end
    If it looks incomplete, that's becuase it is. I cut out the rest of the stored procedure because it is the same thing with different tables. I figure if we can figure out why the Data table can't be written to, we can figure out why the Event and Log tables can't either. It copies the data into a temporary table (DataToCopy) and then moves it into a permanent one (Data).

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Item cannot be found in the collection corresponding to the requested name or ord

    The first thing I noticed is that not all of your lines have line numbers - which means that the error could be occurring on a later line of code (if you are relying on an error handler with Erl to tell you).

    The next thing is that you are not using a hard-coded string, which means that there might be an issue with the string you have built. It would be very useful for us to see what the string contains (use Debug.Print sSql and then read it [and copy+paste it] from the Immediate window).


    A minor (and technically irrelevant) issue is that you shouldn't have brackets on line 20, because you are not using the Call keyword or using the result that .Execute returns. This wont cause problems because you can put brackets around any single value, but it would be more correct to remove them.

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    8

    Re: Item cannot be found in the collection corresponding to the requested name or ord

    I stepped through the code and that's the line that caused the jump to the error handler but I will look into that possibility.

    This is the string in sSql when m_dbconn.Execute is run:
    sp_BulkInsert 'data', 'C:\Importer\Pending\data.txt'

    I tried the application on another computer with SQL Server 2005 and a database built with the same scripts and it worked just fine. I'm starting to think that something may be corrupted on my computer. I'm not a VB6 or SQL expert but I can't seem to find any other explanation.

    Thanks for your help!.
    Last edited by dspinner; Nov 4th, 2010 at 10:34 AM. Reason: Added information.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Item cannot be found in the collection corresponding to the requested name or ord

    In that case it is that line causing the error.

    The value of sSql looks fine, but make sure that the location and file name are spelt correctly, and that the permissions of the file/folder allow SQL Server to access it.


    I recommend specifying the optional parameter to tell ADO what functionality you want from .Execute, as that may remove the issue (even if it doesn't, it will run slightly faster):
    Code:
    20    m_dbconn.Execute sSql, , adCmdText + adExecuteNoRecords
    If those two things don't help, some kind of corruption somewhere is a possibility.

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    8

    Re: Item cannot be found in the collection corresponding to the requested name or ord

    I'm still getting the same error. Nobody else is having this problem that I know of so I'm going to call it a computer problem and move on. Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width