Run time error '3709'

[pre_wreck]

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

[MKSa]

'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.

[pre_wreck]

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

[MKSa]

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.

[pre_wreck]

Thanks it works

How about my second question?

I appreciate your answer a lot.

again thank you.
jay

[MKSa]

'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.

[pre_wreck]

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

Thanks a lot.

Jay

[Leerz]

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

[Praveen A Lakshmipat]

JPnyc

[Praveen A Lakshmipat]

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

[Praveen A Lakshmipat]

rs.Open strsql, g_con, adOpenDynamic, adLockOptimistic

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

[WoF]

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.