-
Mar 21st, 2007, 08:58 AM
#1
Thread Starter
Member
[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:
Public Sub ConnectToServer(Database As String)
' On Error GoTo leave
Dim Kent As String
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
Cmd1.CommandText = "SELECT PORTNAME, PORTINFOID From portinfo " & _
"GROUP BY PORTNAME, PORTINFOID ORDER BY PORTNAME"
[COLOR="Red"]rs1.Open Cmd1, , adOpenStatic, adLockBatchOptimistic[/COLOR]
While Not rs1.EOF
Kent = rs1(0)
rs1.MoveNext
Wend
rs1.Close
End If
'!!!!TESTCODE
GoTo leave2
leave:
If Conn1.State = adStateOpen Then Set Conn1 = Nothing
If rs1.State = adStateOpen Then rs1.Close
leave2:
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
-
Mar 21st, 2007, 09:00 AM
#2
Re: Run-Time Error 3265
Then, either PORTNAME or PORTINFOID doesn't exist as a field. Check the spelling.
-
Mar 21st, 2007, 09:23 AM
#3
Thread Starter
Member
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.
-
Mar 21st, 2007, 09:31 AM
#4
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
-
Mar 21st, 2007, 09:33 AM
#5
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)?
-
Mar 21st, 2007, 09:42 AM
#6
Thread Starter
Member
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.
-
Mar 21st, 2007, 09:47 AM
#7
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.
-
Mar 21st, 2007, 10:16 AM
#8
Thread Starter
Member
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
-
Mar 21st, 2007, 10:25 AM
#9
Thread Starter
Member
Re: Run-Time Error 3265
Is there any speed to be gained with using AdLockOptimistic versus AdLockBatchOptimistic?
-
Mar 21st, 2007, 10:38 AM
#10
Re: Run-Time Error 3265
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?
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...
-
Mar 21st, 2007, 11:23 AM
#11
Thread Starter
Member
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.
-
Mar 21st, 2007, 11:29 AM
#12
Re: Run-Time Error 3265
Actually, it should be
vb Code:
Public Conn1 As ADODB.Connection
Public Cmd1 As ADODB.Command
Public rs1 As ADODB.Recordset
'then, when you need to use them
Public Sub OpenDB()
Set Conn1 = New ADODB.Connection
'connection code goes here
End Sub
Private Sub MakeQuery()
sSQL = "SELECT blah, blah, blah FROM blabla "
Set rs1 = New ADODB.Recordset
rs1.Open sSQL, Conn1
End Sub
-
Mar 21st, 2007, 01:13 PM
#13
Thread Starter
Member
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:
Private Sub form_activate()
Call ConnectToServer("B_CinFin_06_EDM")
rs1.Open "SELECT PORTNAME, PORTINFOID From portinfo", Conn1, adOpenStatic, adLockBatchOptimistic, adCmdText
rs1.Close
Conn1.Close
Set Conn1 = Nothing
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
Conn1.Close
Set Conn1 = Nothing
Call ConnectToServer("B_CinFin_06_EDM")
rs1.Open "SELECT PORTNAME, PORTINFOID From portinfo", Conn1, adOpenStatic, adLockBatchOptimistic, adCmdText
rs1.Close
Conn1.Close
Set Conn1 = Nothing
End Sub
Last edited by Hack; Mar 21st, 2007 at 01:17 PM.
Reason: Added VB Highlight Tags
-
Mar 21st, 2007, 01:20 PM
#14
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.
-
Mar 21st, 2007, 01:30 PM
#15
Thread Starter
Member
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!
-
Mar 21st, 2007, 01:37 PM
#16
Thread Starter
Member
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!
-
Mar 21st, 2007, 01:44 PM
#17
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|