Results 1 to 17 of 17

Thread: [RESOLVED] Run-Time Error 3265

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Resolved [RESOLVED] Run-Time Error 3265

    Hello,

    Getting the below error (happens at red text) when running a sql command from vb. From other posts it appears to be due to invalid field names. But when I run the same script from Query Analyzer it returns the expected results. Is there a server setting that might cause this issue? I have no issues retreiving data from other types of databases, just this kind.

    Run-time error '3265': Item cannot be found in the collection to the requested name ordinal.
    vb Code:
    1. Public Sub ConnectToServer(Database As String)
    2.  
    3. '    On Error GoTo leave
    4.     Dim Kent As String
    5.    
    6.     If Conn1.State = adStateOpen Then Set Conn1 = Nothing
    7.     Conn1.Open "Driver={SQL Server};Server=LDMRMS01;Trusted_Connection=yes;"
    8.     Set Cmd1.ActiveConnection = Conn1
    9.     Cmd1.CommandTimeout = 600
    10.     If Len(Database) > 0 Then
    11.              Cmd1.CommandText = "Use " & Trim(Database)
    12.              Cmd1.Execute
    13.     End If
    14.     rs1.CursorLocation = adUseClient
    15.    
    16.     '!!!!TESTCODE
    17.     If Database = Trim(Exposure.ExposureDBs.Text) And Database > "" Then
    18.              Cmd1.CommandText = "SELECT PORTNAME, PORTINFOID From portinfo " & _
    19.             "GROUP BY PORTNAME, PORTINFOID ORDER BY PORTNAME"
    20.         [COLOR="Red"]rs1.Open Cmd1, , adOpenStatic, adLockBatchOptimistic[/COLOR]        
    21.       While Not rs1.EOF
    22.                  Kent = rs1(0)
    23.                  rs1.MoveNext
    24.         Wend
    25.         rs1.Close
    26.     End If
    27.     '!!!!TESTCODE
    28.    
    29.     GoTo leave2
    30. leave:
    31.     If Conn1.State = adStateOpen Then Set Conn1 = Nothing
    32.     If rs1.State = adStateOpen Then rs1.Close
    33. leave2:
    34.  
    35. End Sub
    Thanks for any help that might be provided!
    Last edited by Hack; Mar 21st, 2007 at 08:58 AM. Reason: Added VB Highlight Tags

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Run-Time Error 3265

    Then, either PORTNAME or PORTINFOID doesn't exist as a field. Check the spelling.

  3. #3

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Re: Run-Time Error 3265

    I think its got to be a simple and stupid mistake too but I can copy the exact command into Query Analyzer and it returns the expected results. I actually shortened the command to just "SELECT PORTNAME, PORTINFOID From portinfo". Works fine in Query Analyzer, same error produced in VB.

  4. #4
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Run-Time Error 3265

    Make this change and try it
    Code:
    Dim sSQL As String
    sSQL = "SELECT PORTNAME, PORTINFOID From portinfo " _ &  
    "GROUP BY PORTNAME, PORTINFOID ORDER BY PORTNAME"
    rs1.Open sSQL,Conn1, adOpenStatic, adLockBatchOptimistic

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

    Re: Run-Time Error 3265

    Does it happen when you run the query directly via the recordset?
    eg:
    Code:
    rs1.Open "SELECT PORTNAME, PORTINFOID From portinfo", Conn1, adOpenStatic, adLockBatchOptimistic
    Oh, and is there a reason for using adLockBatchOptimistic (rather than adLockOptimistic)?

  6. #6

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Re: Run-Time Error 3265

    Same error with your suggested code...I'm completely baffled by this! Though I do not know a lot about server user priveleges if it could be something along those lines...

    And yes to Si as well, same error. As far as adlockbatchoptimistic, I just took that out of a book I was looking at years ago and has worked well for me ever since. I'll have to look into the difference between the two.
    Last edited by GotTroubles; Mar 21st, 2007 at 09:47 AM.

  7. #7
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Run-Time Error 3265

    If it were a privilege issue, the error would be different, and you would have a problem running it from Query Analyser as well.

    Lets try a different approach. It looks like you are connecting to your database and attempting to run this query all in the same sub.

    Remove your SELECT query from this sub, and put it in a button click event.

    Run your program and make sure you do not receive any errors when it opens and connects.

    If not, click the button and see if the query will run.

  8. #8

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Re: Run-Time Error 3265

    No issues connecting...changed my code so that at the bottom of form_activate I switch to the database I want to query on. Then when I hit next on my form I almost immediately try to load rs1 with the query that is causing the problems (and still does). In the previous code I commented out my test code to not do the query in the 'ConnectToServer' routine.


    Private Sub form_activate()

    Call ConnectToServer("")
    Cmd1.CommandText = "sp_databases"
    rs1.Open Cmd1, , adOpenStatic, adLockBatchOptimistic
    rs1.Sort = rs1.Fields(0).Name
    ExposureDBs.Clear
    Do While Not rs1.EOF
    If InStr(rs1(0), "EDM") Then ExposureDBs.AddItem (rs1(0))
    rs1.MoveNext
    Loop
    rs1.Close

    '!!!!TESTCODE
    Cmd1.CommandText = "Use B_CinFin_06_EDM"
    Cmd1.Execute

    ' Conn1.Close
    ' Set Conn1 = Nothing

    '!!!!TESTCODE

    End Sub

    Private Sub Next_Click()

    Dim PerilType As String
    Dim TotalValue As Double, TotalLimit As Double
    Dim Kent As String

    Exposure.MousePointer = vbHourglass

    '!!!!TESTCODE
    rs1.Open "SELECT PORTNAME, PORTINFOID From portinfo", Conn1, adOpenStatic, adLockBatchOptimistic
    '!!!!TESTCODE

  9. #9

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Re: Run-Time Error 3265

    Is there any speed to be gained with using AdLockOptimistic versus AdLockBatchOptimistic?

  10. #10
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Run-Time Error 3265

    Quote Originally Posted by GotTroubles
    Code:
    Public Sub ConnectToServer(Database As String)
    
    '    On Error GoTo leave
        Dim Kent As String
        Dim strSql as string 'added this
        
        If Conn1.State = adStateOpen Then Set Conn1 = Nothing
    
        Conn1.Open "Driver={SQL Server};Server=LDMRMS01;Trusted_Connection=yes;"
    
        Set Cmd1.ActiveConnection = Conn1
    
        Cmd1.CommandTimeout = 600
        If Len(Database) > 0 Then
                 Cmd1.CommandText = "Use " & Trim(Database)
                 Cmd1.Execute
        End If
    
        rs1.CursorLocation = adUseClient
        
        '!!!!TESTCODE
        If Database = Trim(Exposure.ExposureDBs.Text) And Database > "" Then
                 strSql="SELECT PORTNAME, PORTINFOID From portinfo " & _
                "GROUP BY PORTNAME, PORTINFOID ORDER BY PORTNAME"
            rs1.Open strSql,conn1 , adOpenStatic, adLockBatchOptimistic,adCmdText 'note the adcmdtext! ADO likes it
          While Not rs1.EOF
                     Kent = rs1(0)
                     rs1.MoveNext
            Wend
            rs1.Close
        End If
        '!!!!TESTCODE
        
        exit sub
    leave:
        If Conn1.State = adStateOpen Then Set Conn1 = Nothing
        If rs1.State = adStateOpen Then rs1.Close
    
    End Sub
    Also note that I put the sql statement first and the connection object second
    Your first post had the connection object in the wrong place. Infact it should have been conn1.
    Where are the declarations for these? In the module?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  11. #11

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Re: Run-Time Error 3265

    The way I was initially doing it I was using a Cmd object; I believe the order is correct with that use. Here are my declarations that are defined publicly in a module.

    Public Conn1 As New ADODB.Connection
    Public Cmd1 As New ADODB.Command
    Public rs1 As New ADODB.Recordset

    Thanks for the help.

  12. #12
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Run-Time Error 3265

    Actually, it should be
    vb Code:
    1. Public Conn1 As ADODB.Connection
    2. Public Cmd1 As ADODB.Command
    3. Public rs1 As ADODB.Recordset
    4.  
    5. 'then, when you need to use them
    6. Public Sub OpenDB()
    7. Set Conn1 = New ADODB.Connection
    8. 'connection code goes here
    9. End Sub
    10.  
    11. Private Sub MakeQuery()
    12. sSQL = "SELECT blah, blah, blah FROM blabla "
    13. Set rs1 = New ADODB.Recordset
    14. rs1.Open sSQL, Conn1
    15. End Sub

  13. #13

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Re: Run-Time Error 3265

    Ok...I'm getting closer. The top 5 instructions is repeated at the bottom of the subroutine. The top 5 work...the bottom 5 give the same error....any ideas why that would be?
    vb Code:
    1. Private Sub form_activate()
    2.        
    3.     Call ConnectToServer("B_CinFin_06_EDM")
    4.     rs1.Open "SELECT PORTNAME, PORTINFOID From portinfo", Conn1, adOpenStatic, adLockBatchOptimistic, adCmdText
    5.     rs1.Close
    6.     Conn1.Close
    7.     Set Conn1 = Nothing
    8.    
    9.     Call ConnectToServer("")
    10.     Cmd1.CommandText = "sp_databases"
    11.     rs1.Open Cmd1, , adOpenStatic, adLockBatchOptimistic
    12.     rs1.Sort = rs1.Fields(0).Name
    13.     ExposureDBs.Clear
    14.     Do While Not rs1.EOF
    15.         If InStr(rs1(0), "EDM") Then ExposureDBs.AddItem (rs1(0))
    16.         rs1.MoveNext
    17.     Loop
    18.     rs1.Close
    19.     Conn1.Close
    20.     Set Conn1 = Nothing
    21.  
    22.     Call ConnectToServer("B_CinFin_06_EDM")
    23.     rs1.Open "SELECT PORTNAME, PORTINFOID From portinfo", Conn1, adOpenStatic, adLockBatchOptimistic, adCmdText
    24.     rs1.Close
    25.     Conn1.Close
    26.     Set Conn1 = Nothing
    27.    
    28. End Sub
    Last edited by Hack; Mar 21st, 2007 at 01:17 PM. Reason: Added VB Highlight Tags

  14. #14
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Run-Time Error 3265

    My first question would be why are you running the same query twice?

    Next, you are using the same recordset object for both queries, so that will generate an error. The second time around the recordset object is already open, and now you are trying to open it for a second time.

    Also, it helps in reading posted code if you use either [highlight=vb]your code goes in here[/highlight] or [code][/code] tags.

  15. #15

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Re: Run-Time Error 3265

    I'm just going out of my mind trying to figure out why it is not working. Somehow I got to the point where it will work in one instance and not the other...I think because I moved some code where I was doing many queries that work and found it worked there as well. But my coding structures would have it coded in the form being used. Am I not closing the recordset properly between queries?

    Will definetely apply your suggestion in the future about the highlight!

  16. #16

    Thread Starter
    Member
    Join Date
    Jan 2007
    Posts
    33

    Re: Run-Time Error 3265

    Ok...resolved (kind of). I've added code to set rs1=nothing in my connecttoserver routine and that seems to cleared it up. Thank you for all your help and patience!

  17. #17
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: [RESOLVED] Run-Time Error 3265

    You should always close and set to nothing your recordset object each and everytime you use it.

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