dcsimg
Results 1 to 10 of 10

Thread: Find Records error

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2008
    Posts
    4

    Post Find Records error

    helo im a student in VB and i have problem with the code in finding some records in my database, im using ADO and using MDI form 1 parents and 3 child form, the search form and form where the data will be add

    this is are the code


    'in my module

    Public Const DBNAME = "medata.mdb"
    Public cn As New ADODB.Connection


    Public Sub OpenDB()
    Dim DBPATH As String
    DBPATH = App.Path & "\" & DBNAME
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH

    End Sub


    ' in my form this are the code

    Option Explicit
    Dim rs As ADODB.Recordset

    Private Sub Form_Load()
    OpenDB
    End Sub


    Private Sub Form_Unload(Cancel As Integer)
    Set cn = Nothing
    End Sub

    Private Sub kuwaitfnd_Click()
    Dim myname As String
    myname = InputBox("Please Type The Name Here")
    Set cn = New Connection
    Set rs = New Recordset

    rs.Open "Select * from biodata where lname like '" & myname & "%'", cn
    searchlist.Clear
    Do While Not rs.EOF
    searchlist.AddItem (rs("lname") & "" & "," & rs("fname"))
    rs.MoveNext

    Loop
    rs.Close

    Set rs = Nothing
    End Sub


    and i got the following error
    runtime error 3709
    The connection cannot be used to perform this operation. It is either closed or invalid in this context

    in my module i declare the connection as PUBLIC so why i stil got error

    thanks

  2. #2
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Find Records error

    In Form Load the Connection is opened by calling the OpenDB procedure but in the Kuwaitfnd_Click procedure this line

    Set cn = New Connection

    causes the opened connection to be closed and a new connection object to be created. However the new connection is never opened.

    Delete the Set cn line from the click procedure.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2008
    Posts
    4

    Re: Find Records error

    I already try that and its still the same problem

    i realy dont know which and why part of the code have a problem

  4. #4
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Find Records error

    Code:
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH
    I think you need a semi-colon at the end of the conection string. eg
    Code:
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH & ";"

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2008
    Posts
    4

    Re: Find Records error

    the same thing
    i wonder why i click debug it goes to this line of my code


    rs.Open "Select * from biodata where lname like '" & myname & "%'", cn


    and the error

    The connection cannot be used to perform this operation. It is either closed or invalid in this context

    if closed that could be possible because i already declare the cn PUBLIC

  6. #6
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Find Records error

    Well, you're stretching my knowledge of ADO to its limits. The only other thing I observe is that you're using some default properties of the Connection and RecordSet Objects which may cause conflict (or may not)
    I wonder whether making some changes will help. eg
    Code:
    Public Sub OpenDB()
    Dim DBPATH As String
    DBPATH = App.Path & "\" & DBNAME
    Set cn = New ADODB.Connection
    cn.CursorLocation = adUseClient
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH & ";"
    End Sub
    and
    Code:
    rs.Open "Select * from biodata where lname like '" & myname & "%'", cn, adOpenStatic, adLockOptimistic

  7. #7
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Find Records error

    Change this: Public cn As New ADODB.Connection
    To this: Public cn As ADODB.Connection
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  8. #8

    Thread Starter
    New Member
    Join Date
    Jan 2008
    Posts
    4

    Re: Find Records error

    Same Error and if i click debug still goes to this one


    rs.Open "Select * from biodata where lname like '" & myname & "%'", cn

    or suggestion a query code to search some records in my database by declaring it to the module

    suggestion will be accepted

    thanks

  9. #9
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Find Records error

    Could you please post the code as it is now.

    The only way I can reproduce this error is by defining cn in the kuwaitfnd_Click() routine which, as was pointed out in Post #2, should not be there.
    ie it should look like this:
    Code:
    Private Sub kuwaitfnd_Click()
    Dim myname As String
    myname = InputBox("Please Type The Name Here")
    Set rs = New Recordset
    rs.Open "Select * from biodata where lname like '" & myname & "%'", cn
    searchlist.Clear
    Do While Not rs.EOF
        searchlist.AddItem (rs("lname") & "" & "," & rs("fname"))
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    End Sub
    Last edited by Doogle; Jan 24th, 2008 at 02:24 AM.

  10. #10
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,692

    Re: Find Records error

    Try these changes:
    Quote Originally Posted by noknok
    'in my module Why in a module? This could be on the form.

    Public Const DBNAME = "medata.mdb"
    'Public cn As New ADODB.Connection
    Public cn As ADODB.Connection
    'If you declare it as New you can't ever reclaim the memory.


    Public Sub OpenDB()
    Dim DBPATH As String
    DBPATH = App.Path & "\" & DBNAME
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH

    End Sub


    ' in my form this are the code

    Option Explicit
    Dim rs As ADODB.Recordset

    Private Sub Form_Load()
    OpenDB
    End Sub


    Private Sub Form_Unload(Cancel As Integer)
    cn.Close
    Set cn = Nothing
    End Sub

    Private Sub kuwaitfnd_Click()
    Dim myname As String
    Set rs = New ADODB.Recordset

    myname = InputBox("Please Type The Name Here")
    'Set cn = New Connection - This one never gets opened. That causes your error.
    'Set rs = New Recordset


    rs.Open "Select * from biodata where lname like '" & myname & "%'", cn
    searchlist.Clear
    Do While Not rs.EOF
    searchlist.AddItem (rs("lname") & "" & "," & rs("fname"))
    rs.MoveNext

    Loop
    rs.Close

    Set rs = Nothing
    End Sub


    and i got the following error
    runtime error 3709
    The connection cannot be used to perform this operation. It is either closed or invalid in this context

    in my module i declare the connection as PUBLIC so why i stil got error
    Declaring it doesn't open it.
    The most difficult part of developing a program is understanding the problem.
    The second most difficult part is deciding how you're going to solve the problem.
    Actually writing the program (translating your solution into some computer language) is the easiest part.

    Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read.

    Please Help Us To Save Ana

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width