Run time error '3709'
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12

Thread: Run time error '3709'

  1. #1
    Join Date
    Feb 2002
    Posts
    295

    Run time error '3709'

    Here's My Set of codes, but i've got a problem in my record "rs.open SQL, SETCONNECT, adOpenKeyset, adLockOptimistic"
    But still the same problem. I ve got this error.

    Run-time error '3709'

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


    Please help me reconstruct my codes.

    -----------------
    CODES
    -----------------
    Module1
    ------------------
    Option Explicit
    Public rs As ADODB.Recordset
    Private cn As ADODB.Connection

    Sub Main()
    Dim cmd As String

    cmd = "ODBC;DATABASE=ems_ho1;UID="";PWD="";DSN=ems_ho1"

    Set cn = New ADODB.Connection
    With cn
    .ConnectionString = cmd
    .open
    End With
    End Sub

    Public Sub SETCONNECT()
    Set Form1.CURRENTCONNECT = cn
    End Sub

    ------------------------
    FORM1
    ------------------------

    Option Explicit
    Public SETCONNECT As ADODB.Connection
    Dim rs As ADODB.Recordset
    Public Property Set CURRENTCONNECT(Setting As ADODB.Connection)
    Set SETCONNECT = Setting
    End Property

    Private Sub loadinfo()
    Dim SQL As String

    Set rs = New ADODB.Recordset

    SQL = "SELECT master.CARD_CODE, master.FIRSTNAME, master.LASTNAME, master.TITLE, master.DEPARTMENT, master.MI, master.Course, master.Photograph_File_Name, verification.Dept, verification.Acctg, verification.Reg, verification.DO, verification.LRC, verification.Guid, verification.Foreign, verification.IClab, verification.ITC, verification.OTHERS, verification.Status, verification.Remarks FROM master LEFT JOIN verification ON master.CARD_CODE = verification.ID_num WHERE (((master.CARD_CODE)='" + txt_StudNo.Text + "'));"

    rs.open SQL, SETCONNECT, adOpenKeyset, adLockOptimistic

    If Not rs.RecordCount Then

    If rs("status") = "1" Then
    Label6 = " Accounting " & rs("Acctg")
    ELSE
    MSGBOX "Clear"
    end if
    end if
    rs.close
    cn.close
    end sub

    Codes Something like that, correct me if im wrong.

    Please help me.

    Thanks in advance.

    Jay



  2. #2
    Join Date
    Sep 2001
    Location
    IL, USA
    Posts
    1,090

    Re: Run time error '3709'

    'Add to the Form a CommandButton (Command1). Keep the label (Label6) and the TextBox (txt_StudNo)
    'Delete the extra code from The Form and let us test the connection. Make sure The DSN was configered Correctly.
    private Sub loadinfo()
    Dim SQL as string
    Dim cn as new ADODB.Connection, rs as ADODB.Recordset
    'When you configured the DSN ems_ho1, I assume you chose the mdb file.
    cn.Open "DSN=ems_ho1;UID=;PWD=;"

    set rs = new ADODB.Recordset

    SQL = "SELECT master.CARD_CODE, master.FIRSTNAME, master.LASTNAME, master.TITLE, master.DEPARTMENT, master.MI, master.Course, master.Photograph_File_Name, verification.Dept, verification.Acctg, verification.Reg, verification.DO, verification.LRC, verification.Guid, verification.Foreign, verification.IClab, verification.ITC, verification.OTHERS, verification.Status, verification.Remarks FROM master LEFT JOIN verification on master.CARD_CODE = verification.ID_num WHERE (((master.CARD_CODE)='" + txt_StudNo.Text + "'));"

    rs.Open SQL, cn, adOpenKeyset, adLockOptimistic

    If Not rs.RecordCount then

    If rs("status") = "1" then
    Label6 = " Accounting " & rs("Acctg")
    else
    MsgBox "Clear"
    End If
    End If
    rs.Close
    cn.Close
    set rs=nothing
    set cn=nothing
    End Sub

    private Sub Command1_Click()
    loadinfo
    End Sub

    Help us improve our answers by rating them.


  3. #3
    Join Date
    Feb 2002
    Posts
    295

    Re: Run time error '3709'

    I've got this error in if then statement.

    If Not rs.RecordCount Then
    If rs("status") = "1" Then
    Label6 = " Accounting " & rs("Acctg")
    Else
    MsgBox "Clear"
    End If
    End If

    run time error '3021':

    either BOF OR EOF IS TRUE, OR the current record has been deleted.

    requested operation requires a current record.

    anyway i have to configure more my if then statement.
    My question is, what if i put my connection in a module, how do i call my conneection? Let say in form1, i need a connection what will be my statement in order to call that said connection?

    Please Help.

    Thanks

    jay


  4. #4
    Join Date
    Sep 2001
    Location
    IL, USA
    Posts
    1,090

    Re: Run time error '3709'

    Let's try to fix the error:If rs.RecordCount>0 then
    If rs("status") = "1" then
    Label6 = " Accounting " & rs("Acctg")
    else
    MsgBox "Clear"
    End If
    End If

    Help us improve our answers by rating them.


  5. #5
    Join Date
    Feb 2002
    Posts
    295

    Re: Run time error '3709'

    Thanks it works

    How about my second question?

    I appreciate your answer a lot.

    again thank you.
    jay


  6. #6
    Join Date
    Sep 2001
    Location
    IL, USA
    Posts
    1,090

    Re: Run time error '3709'

    'Add to the Form a CommandButton (Command1). Keep the label (Label6) and the TextBox (txt_StudNo)
    'Delete the extra code from The Form and let us test the connection. Make sure The DSN was configered Correctly.
    private Sub loadinfo()
    Dim SQL as string, rs as ADODB.Recordset

    set rs = new ADODB.Recordset

    SQL = "SELECT master.CARD_CODE, master.FIRSTNAME, master.LASTNAME, master.TITLE, master.DEPARTMENT, master.MI, master.Course, master.Photograph_File_Name, verification.Dept, verification.Acctg, verification.Reg, verification.DO, verification.LRC, verification.Guid, verification.Foreign, verification.IClab, verification.ITC, verification.OTHERS, verification.Status, verification.Remarks FROM master LEFT JOIN verification on master.CARD_CODE = verification.ID_num WHERE (((master.CARD_CODE)='" + txt_StudNo.Text + "'));"
    'Notice the change here
    rs.Open SQL, SetDSNConnection, adOpenKeyset, adLockOptimistic

    If Not rs.RecordCount then

    If rs("status") = "1" then
    Label6 = " Accounting " & rs("Acctg")
    else
    MsgBox "Clear"
    End If
    End If
    rs.Close
    SetDSNConnection.Close
    set rs = nothing
    End Sub

    private Sub Command1_Click()
    loadinfo
    End Sub
    ‘In a module add the following:
    public Function SetDSNConnection() as ADODB.Connection
    Dim cn as new ADODB.Connection, rs as ADODB.Recordset
    'When you configured the DSN ems_ho1, I assume you chose the mdb file.
    cn.Open "DSN=ems_ho1;UID=;PWD=;"
    set SetDSNConnection = cn
    End Function


    Help us improve our answers by rating them.



  7. #7
    Join Date
    Feb 2002
    Posts
    295

    Re: Run time error '3709'

    Yes Great!
    That was a very very very good answer.

    Thanks a lot.

    Jay



  8. #8
    Join Date
    Mar 2006
    Posts
    1

    Unhappy Re: Run time error '3709'

    hi, im new here, can anyone please help me debug my stuff, (desperate)
    im getting the same error 3709.
    ive checked my codes,(even compared to working ones)

    i really dont have any clue whats causing the error, the only thing i know that it has something to do with my adodb's and some search functions.
    i think its best if you take a look at the whole program itself
    its not very big, its just a small one, i think the pros here wouldnt really get lost inside.

    please please please check this.. :sad:
    http://leerz25.sitesled.com/EDTnick.rar

    its in RAR. hope pros and everybody else could and would help, thank you very much for reading, and thank you for the help in advance
    Regards


    Leerz

  9. #9
    Join Date
    Apr 2012
    Posts
    3

    Re: Run time error '3709'

    JPnyc

  10. #10
    Join Date
    Apr 2012
    Posts
    3

    Re: Run time error '3709'

    Here's My Set of codes, but i've got a problem in my record "rs.open SQL, SETCONNECT, adOpenKeyset, adLockOptimistic"
    But still the same problem. I ve got this error.

    Run-time error '3709'

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

    code
    -----------------------------------------------------

    Private Sub cmdcanacel_Click()
    End
    End Sub

    Private Sub cmdok_Click()
    Dim strsql As String
    Dim rs As ADODB.Recordset
    'OpenConnection
    If txtusername.Text = "" Then
    MsgBox "enter the user name", vbInformation
    txtusername.SetFocus
    Exit Sub

    End If
    If txtpassword.Text = "" Then
    MsgBox "enter the user password", vbInformation
    'txtpassword .SetFocus
    Exit Sub
    End If
    Set rs = New ADODB.Recordset
    strsql = "select loginname,loginpassword from login where loginname='" & txtusername.Text & "'and loginpassword='" & txtpassword.Text & "'"
    rs.Open strsql, g_con, adOpenDynamic, adLockOptimistic
    If rs.EOF = False Then
    welcome.Show
    'MDI.Show
    Else
    MsgBox "Please! enter the username and password correctly", vbCritical
    End If
    rs.Close


    End Sub





    Private Sub Form_Load()
    txtusername.Text = ""
    frmconnection.Hide
    End Sub

    Private Sub txtpassword_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
    cmdok_Click
    End If
    End Sub

    Private Sub txtusername_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
    cmdok_Click
    End If
    End Sub

  11. #11
    Join Date
    Apr 2012
    Posts
    3

    Re: Run time error '3709'

    rs.Open strsql, g_con, adOpenDynamic, adLockOptimistic

    is d error line can anyone makeout y???
    plz help me as soon as possible..

  12. #12
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,724

    Re: Run time error '3709'

    You should trace back where your connection g_con is opened.
    You don't show that code here, but your error message definitely is stating:

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

    So you ought to inspect the code where you open this connection.

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center