same code different problem 1

[DaiDreamer]

I'm having a database problem now. I get a error message everytime my program opens up the database. Can anyone help?

here's the error message:
Line: 37 Char: 3
Error: Data source name not found and no default driver specified.



here's the code:

Option Explicit

' Database Variables
Dim objConn, ObjRS, strQuery
Dim strConnection
Dim strAddress, StrSubject

' Mail Variables
Dim cdoSession, inbox, messages, message, msgID, i, objMessage, objMessages, objFolder, objSession, objMessageFilter

Const adUseServer = 2
Const adOpenKeyset = 1
Const adCmdTable = 2
Const adLockOptimistic = 3

Set objSession = CreateObject("MAPI.Session&quot
objSession.logon
Set objFolder = objSession.Inbox
Set objMessages = objFolder.Messages
Set objMessageFilter= objMessages.Filter
'objMessageFilter.Or= True
'objMessageFilter.Subject="Remove"
'objMessageFilter.Subject="Registration"
Set objMessage=objMessages.GetFirst

If objMessage Is Nothing Then
MsgBox "Folder is Empty"
else
'loop through other messages
Do While Not objMessage Is Nothing

strAddress= objMessage.Sender
strSubject = objMessage.Subject

Set objConn = CreateObject("ADODB.Connection&quot
strConnection= "DSN=POCKETMOSAIC"
objConn.Open strConnection 'line 37
strQuery= "SELECT PagerAddress FROM Users "
strQuery= strQuery & "ORDER BY PagerAddress"

Set objRS = objConn.Execute ( strQuery )

'if the pager address needs to be removed from the db
if strSubject = "Remove" then
MsgBox "remove"
While not(objRS.EOF)
if objRS("PagerNumber&quot = strAddress Then
objRS.Delete
MsgBox strAddress
end if
wend

'if the pager address need to be added to the db
ElseIf StrSubject = "Registration" then
MsgBox "Add"
objRS.AddNew
objRS("PagerNumber&quot=strAddress

objRS.Update
MsgBox strAddress

End if
Set objMessage= objMessages.GetNext
Loop
End if
objRS.close
objConn.close

Set objMessage = nothing
Set objMessages = nothing
Set objFolder= nothing
Set objSession = nothing
Set objRS = nothing
set objConn= nothing
MsgBox "Done!"

cdoSession.Logoff

 

[JohnYingling]

It appears that you did not use ODBC Data Sources in the Control Panel to define DSN=POCKETMOSAIC on the machine on which the code is running.

http://WWW.VBCompare.Com

 

[DaiDreamer]

Thanks but now it doesn't open the record set.

Here's the error:
line 57
error: wrong number of arguements or invalid property assignment: "open"

Here's my updated code:

Option Explicit

' Database Variables
Dim objConn, ObjRS, strQuery
Dim strConnection
Dim strAddress, StrSubject

' Mail Variables
Dim cdoSession, inbox, messages, message, msgID, i, objMessage, objMessages, objFolder, objSession, objMessageFilter

Const adUseServer = 2
Const adOpenKeyset = 1
Const adCmdTable = 2
Const adLockOptimistic = 3

Set objSession = CreateObject("MAPI.Session&quot
objSession.logon
Set objFolder = objSession.Inbox
Set objMessages = objFolder.Messages
Set objMessageFilter= objMessages.Filter
'objMessageFilter.Or= True
'objMessageFilter.Subject="Remove"
'objMessageFilter.Subject="Registration"
Set objMessage=objMessages.GetFirst

If objMessage Is Nothing Then
MsgBox "Folder is Empty"
else
'loop through other messages
Do While Not objMessage Is Nothing

strAddress= objMessage.Sender
strSubject = objMessage.Subject

Set objConn = CreateObject("ADODB.Connection&quot
strConnection= "DSN=POCKETMOSAIC"
objConn.Open strConnection
strQuery= "SELECT PagerAddress FROM Users "
strQuery= strQuery & "ORDER BY PagerAddress"

'if the pager address needs to be removed from the db
if strSubject = "Remove" then
MsgBox "remove"
Set objRS = objConn.Execute ( strQuery )
While not(objRS.EOF)
if objRS("PagerNumber&quot = strAddress Then
objRS.Delete
MsgBox strAddress
end if
wend

'if the pager address need to be added to the db
ElseIf StrSubject = "Registration" then
MsgBox "Add"
set objRS = createobject("ADODB.Connection&quot
objRS.CursorLocation = adUseServer
objRS.Open "Users", objConn, adOpenKeyset, adLockOptimistic, adCmdTable

objRS.AddNew
objRS("PagerNumber&quot=strAddress

objRS.Update
MsgBox strAddress

End if
Set objMessage= objMessages.GetNext
Loop
End if
objRS.close
objConn.close

Set objMessage = nothing
Set objMessages = nothing
Set objFolder= nothing
Set objSession = nothing
Set objRS = nothing
set objConn= nothing
MsgBox "Done!"

cdoSession.Logoff


I hate always asking for help, but I'm new to the database stuff...

 

[link9]

objRS.Open "Users", objConn, adOpenKeyset, adLockOptimistic, adCmdTable

I assume is line 57???

"Users" is not a valid SQL Statement --

If you wish to retrieve all records from your Users table, then ask:

"SELECT * FROM Users"

or as you put before,

"SELECT PagerAddress FROM Users"

 

[JohnYingling]

Do some more research. You never CLOSED objRS before reusing it and from what I see in MSDN, objRS.Open takes 0 parameters.

open Method

Opens a Recordset object.

Syntax

object.open()

Parameters

object

A script object.

Remarks

Use the setRecordSource method to set the connection properties before opening a Recordset object. Use the isOpen method to determine if a Recordset object is open.

Example

This shows how to change the where clause of a query, by using a value from a Textbox. 

function btnQuery_onclick()
{   
   if ( RS1.isOpen() )
       RS1.close();   // must close the recordset before changing the SQLText
   RS1.setSQLText('Select * from TABLE1 where (ID =' + txtQuery.value + ')');
   RS1.open();
}

http://WWW.VBCompare.Com

 

[link9]

actually, the .open method can take up to 5 parameters -- and even more if the fifth has several options OR'd together...

ie...

rs.open sqlStatement, conObject, cursorType, lockType, options


--

 

[DaiDreamer]

I found my problem...

i put this:

set objRS = createobject("ADODB.Connection&quot

instead of this

set objRS = createobject("ADODB.recordset&quot

Thanks for all the help