need example of SQL Server connection with .vbs file 
Author Message
 need example of SQL Server connection with .vbs file

Your help would be greatly appreciated.
I have come across a file that doesn't want to work.  It is a .vbs file and
the error I am getting is...

(22,1)  VBScript runtime error: Type mismatch: 'Application'

Here is a portion of the code:

Set Mailer = WScript.CreateObject("ASPMail.ASPMailCtrl.1")

' Debug
Set objFS = WScript.CreateObject("Scripting.FileSystemObject")
if (objFS.FileExists("path goes here")) then
  objFS.CopyFile "path goes here", "path goes here", true
  objFS.DeleteFile "path goes here"
end if
Set objOutFile = objFS.OpenTextFile("path goes here", 8, true)


subject    = "Weekly delivery of Ad Requests"
mailserver = "mailserver"
message    = "This is an automated message generated from the Slumberland
Extranet. " _
  & "Attached are the ad requests for the week of " & Date() + 28 & "."
attach = "path goes here"

'###  ESTABLISH connection to the database new web.mdb file.  ###
set cnnLogin = WScript.CreateObject("ADODB.Connection")
cnnLogin.Open "Slumberland", Application("username"),
Application("password")                error is on this line
set rsLogin = Server.CreateObject("ADODB.Command")

--
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
Barbara Eichinger
Web Programmer

Slumberland Furniture
    http://www.*-*-*.com/

3060 Centerville Rd.
Little Canada, MN  55117

ph  651.490.6281
fax  651.482.0157
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~



Sun, 18 May 2003 03:00:00 GMT  
 need example of SQL Server connection with .vbs file
Application is an ASP built-in object.  You'll need another way to obtain username.  If this is
their NT domain username, use the WshNetwork object's UserName property...  if it isn't, you'll need
to prompt for it and you'll have to prompt for password anyway.  You may be better off writing this
as an HTA instead of a .vbs so you can mask the password entry...

--
Michael Harris
Microsoft.MVP.Scripting
--

Please do not email questions - post them to the newsgroup instead.
--


Quote:
> Your help would be greatly appreciated.
> I have come across a file that doesn't want to work.  It is a .vbs file and
> the error I am getting is...

> (22,1)  VBScript runtime error: Type mismatch: 'Application'

> Here is a portion of the code:

> Set Mailer = WScript.CreateObject("ASPMail.ASPMailCtrl.1")

> ' Debug
> Set objFS = WScript.CreateObject("Scripting.FileSystemObject")
> if (objFS.FileExists("path goes here")) then
>   objFS.CopyFile "path goes here", "path goes here", true
>   objFS.DeleteFile "path goes here"
> end if
> Set objOutFile = objFS.OpenTextFile("path goes here", 8, true)


> subject    = "Weekly delivery of Ad Requests"
> mailserver = "mailserver"
> message    = "This is an automated message generated from the Slumberland
> Extranet. " _
>   & "Attached are the ad requests for the week of " & Date() + 28 & "."
> attach = "path goes here"

> '###  ESTABLISH connection to the database new web.mdb file.  ###
> set cnnLogin = WScript.CreateObject("ADODB.Connection")
> cnnLogin.Open "Slumberland", Application("username"),
> Application("password")                error is on this line
> set rsLogin = Server.CreateObject("ADODB.Command")

> --
> *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
> Barbara Eichinger
> Web Programmer

> Slumberland Furniture
>    http://www.slumberland.com

> 3060 Centerville Rd.
> Little Canada, MN  55117

> ph  651.490.6281
> fax  651.482.0157
> *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~



Sun, 18 May 2003 03:00:00 GMT  
 need example of SQL Server connection with .vbs file
I thought you wanted SQL Server sample.. why is your database in an mdb?

Quote:
> '###  ESTABLISH connection to the database new web.mdb file.  ###

Not sure if this will work but try this first.

Quote:
> set cnnLogin = WScript.CreateObject("ADODB.Connection")
> cnnLogin.Open "Slumberland", """ &  Application("username") & """, """ &

Application("password") & """

Quote:
> set rsLogin = Server.CreateObject("ADODB.Command")

if all else fails use this.
'Access Jet 4.0 Driver
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src

'Create and Open Connection Object
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open  sConnStr
SQLQuery = "SELECT * FROM Customers"

Set RsCustomerList = OBJdbConn.Execute(SQLQuery)


Quote:
> Your help would be greatly appreciated.
> I have come across a file that doesn't want to work.  It is a .vbs file
and
> the error I am getting is...

> (22,1)  VBScript runtime error: Type mismatch: 'Application'

> Here is a portion of the code:

> Set Mailer = WScript.CreateObject("ASPMail.ASPMailCtrl.1")

> ' Debug
> Set objFS = WScript.CreateObject("Scripting.FileSystemObject")
> if (objFS.FileExists("path goes here")) then
>   objFS.CopyFile "path goes here", "path goes here", true
>   objFS.DeleteFile "path goes here"
> end if
> Set objOutFile = objFS.OpenTextFile("path goes here", 8, true)


> subject    = "Weekly delivery of Ad Requests"
> mailserver = "mailserver"
> message    = "This is an automated message generated from the Slumberland
> Extranet. " _
>   & "Attached are the ad requests for the week of " & Date() + 28 & "."
> attach = "path goes here"

> '###  ESTABLISH connection to the database new web.mdb file.  ###
> set cnnLogin = WScript.CreateObject("ADODB.Connection")
> cnnLogin.Open "Slumberland", Application("username"),
> Application("password")                error is on this line
> set rsLogin = Server.CreateObject("ADODB.Command")

> --
> *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
> Barbara Eichinger
> Web Programmer

> Slumberland Furniture
>    http://www.slumberland.com

> 3060 Centerville Rd.
> Little Canada, MN  55117

> ph  651.490.6281
> fax  651.482.0157
> *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~



Sun, 18 May 2003 03:00:00 GMT  
 need example of SQL Server connection with .vbs file
Those comments just weren't updated.  Sorry about that.  This was something
that was transferred from Access to SQL Server.

--
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
Barbara Eichinger
Web Programmer

Slumberland Furniture
   http://www.slumberland.com

3060 Centerville Rd.
Little Canada, MN  55117

ph  651.490.6281
fax  651.482.0157
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~

Quote:
> I thought you wanted SQL Server sample.. why is your database in an mdb?
> > '###  ESTABLISH connection to the database new web.mdb file.  ###

> Not sure if this will work but try this first.

> > set cnnLogin = WScript.CreateObject("ADODB.Connection")
> > cnnLogin.Open "Slumberland", """ &  Application("username") & """, """ &
> Application("password") & """
> > set rsLogin = Server.CreateObject("ADODB.Command")

> if all else fails use this.
> 'Access Jet 4.0 Driver
> sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src

> 'Create and Open Connection Object
> Set OBJdbConn = Server.CreateObject("ADODB.Connection")
> OBJdbConn.Open  sConnStr
> SQLQuery = "SELECT * FROM Customers"

> Set RsCustomerList = OBJdbConn.Execute(SQLQuery)



> > Your help would be greatly appreciated.
> > I have come across a file that doesn't want to work.  It is a .vbs file
> and
> > the error I am getting is...

> > (22,1)  VBScript runtime error: Type mismatch: 'Application'

> > Here is a portion of the code:

> > Set Mailer = WScript.CreateObject("ASPMail.ASPMailCtrl.1")

> > ' Debug
> > Set objFS = WScript.CreateObject("Scripting.FileSystemObject")
> > if (objFS.FileExists("path goes here")) then
> >   objFS.CopyFile "path goes here", "path goes here", true
> >   objFS.DeleteFile "path goes here"
> > end if
> > Set objOutFile = objFS.OpenTextFile("path goes here", 8, true)


> > subject    = "Weekly delivery of Ad Requests"
> > mailserver = "mailserver"
> > message    = "This is an automated message generated from the
Slumberland
> > Extranet. " _
> >   & "Attached are the ad requests for the week of " & Date() + 28 & "."
> > attach = "path goes here"

> > '###  ESTABLISH connection to the database new web.mdb file.  ###
> > set cnnLogin = WScript.CreateObject("ADODB.Connection")
> > cnnLogin.Open "Slumberland", Application("username"),
> > Application("password")                error is on this line
> > set rsLogin = Server.CreateObject("ADODB.Command")

> > --
> > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
> > Barbara Eichinger
> > Web Programmer

> > Slumberland Furniture
> >    http://www.slumberland.com

> > 3060 Centerville Rd.
> > Little Canada, MN  55117

> > ph  651.490.6281
> > fax  651.482.0157
> > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~



Mon, 19 May 2003 03:00:00 GMT  
 need example of SQL Server connection with .vbs file
Do you have any other suggestions?  The goal is to get e-mail addresses out
of SQL Server.  But be able to run through the MS dos prompt so it can be
done automatically every week.

--
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
Barbara Eichinger
Web Programmer

Slumberland Furniture
   http://www.slumberland.com

3060 Centerville Rd.
Little Canada, MN  55117

ph  651.490.6281
fax  651.482.0157
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~

Quote:
> Application is an ASP built-in object.  You'll need another way to obtain

username.  If this is
Quote:
> their NT domain username, use the WshNetwork object's UserName property...

if it isn't, you'll need
Quote:
> to prompt for it and you'll have to prompt for password anyway.  You may

be better off writing this
Quote:
> as an HTA instead of a .vbs so you can mask the password entry...

> --
> Michael Harris
> Microsoft.MVP.Scripting
> --

> Please do not email questions - post them to the newsgroup instead.
> --



> > Your help would be greatly appreciated.
> > I have come across a file that doesn't want to work.  It is a .vbs file
and
> > the error I am getting is...

> > (22,1)  VBScript runtime error: Type mismatch: 'Application'

> > Here is a portion of the code:

> > Set Mailer = WScript.CreateObject("ASPMail.ASPMailCtrl.1")

> > ' Debug
> > Set objFS = WScript.CreateObject("Scripting.FileSystemObject")
> > if (objFS.FileExists("path goes here")) then
> >   objFS.CopyFile "path goes here", "path goes here", true
> >   objFS.DeleteFile "path goes here"
> > end if
> > Set objOutFile = objFS.OpenTextFile("path goes here", 8, true)


> > subject    = "Weekly delivery of Ad Requests"
> > mailserver = "mailserver"
> > message    = "This is an automated message generated from the
Slumberland
> > Extranet. " _
> >   & "Attached are the ad requests for the week of " & Date() + 28 & "."
> > attach = "path goes here"

> > '###  ESTABLISH connection to the database new web.mdb file.  ###
> > set cnnLogin = WScript.CreateObject("ADODB.Connection")
> > cnnLogin.Open "Slumberland", Application("username"),
> > Application("password")                error is on this line
> > set rsLogin = Server.CreateObject("ADODB.Command")

> > --
> > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
> > Barbara Eichinger
> > Web Programmer

> > Slumberland Furniture
> >    http://www.slumberland.com

> > 3060 Centerville Rd.
> > Little Canada, MN  55117

> > ph  651.490.6281
> > fax  651.482.0157
> > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~



Mon, 19 May 2003 03:00:00 GMT  
 need example of SQL Server connection with .vbs file

I've been working on a similar thing myself. This is what I've got going.
I use the command line mailer BLAT
ftp://ftp.digital.com/pub/micro/pc/simtelnet/win95/email/blat184.zip
 Hope it helps!.

Set WshShell = WScript.CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
set cnHeat = CreateObject("ADODB.Connection")
SET rsUnAcknowledged = CreateObject("ADODB.RecordSet")
SET rsTeam = CreateObject("ADODB.RecordSet")
cnHeat.Open("Provider=SQLOLEDB; DATA SOURCE=wlndb2; UID=hdeskadm;
PWD=hdesk135; DATABASE=HELPDESK")

Yesterday = DateAdd("d",-1,date)
TargetDate= year(Yesterday) &"-"& month(Yesterday) &"-"& day(Yesterday)

SQLText = "Select " & _
 "CALLLOG.CallID, CALLLOG.MFATCallDesc, CALLLOG.CallDesc, " & _
 "ASGNMNT.GroupName, ASGNMNT.Assignee, ASGNMNT.DateAssign " & _
"FROM " & _
 "CALLLOG CALLLOG LEFT OUTER JOIN PROFILE PROFILE ON " & _
 "CALLLOG.CustID = PROFILE.CustID AND " & _
 "CALLLOG.CustType = PROFILE.CustType " & _
 "LEFT OUTER JOIN ASGNMNT ASGNMNT ON " & _
 "CALLLOG.CallID = ASGNMNT.CallID " & _
"WHERE " & _
 "CALLLOG.CallStatus <> 'Closed' AND " & _
 "CALLLOG.CallStatus <> 'Resolved' AND " & _
 "ASGNMNT.DateAssign < '" & TargetDate & "' AND " & _
 "ASGNMNT.DateAcknow = '' " & _
"ORDER BY " & _
 "CALLLOG.CallID ASC"

rsUnAcknowledged.Open SQLText, cnHeat, 0,1 'Cursor Type=0 (Forward Only)
Lock Method=1 (Read Only)
do until rsUnAcknowledged.EOF
 CallID = cInt(rsUnAcknowledged("CallID"))
 MFATCallDesc = rsUnAcknowledged("MFATCallDesc")
 CallDesc = rsUnAcknowledged("CallDesc")

 DateAssign = rsUnAcknowledged("DateAssign")
 DaysOld = DateDiff("d",DateAssign,Date)
 wscript.echo vbCrLf & cInt(rsUnAcknowledged("CallID")) &" "&
rsUnAcknowledged("GroupName") &" "& rsUnAcknowledged("Assignee") &" "&
DaysOld & " days old."

 if rsUnAcknowledged("Assignee") <> "" then
  ToAddress = GetAssigneeEmailAddress(rsUnAcknowledged("Assignee"))
  Return = WshShell.Run("blat c:\temp\Message.txt -t " &ToAddress& " -s
"&chr(34)&CallID&" "&MFATCallDesc&chr(34)& " -f

 else
  ToAddress = GetTeamMembersEmailAddresses(rsUnAcknowledged("GroupName"))
  Return = WshShell.Run("blat c:\temp\GrpMessage.txt -t " &ToAddress& " -s
"&chr(34)&CallID&" "&MFATCallDesc&chr(34)& " -f

 end if
 rsUnAcknowledged.MoveNext
loop

Function GetTeamMembersEmailAddresses (TargetTeam)
if TargetTeam = "Technical Sup" then
 TargetTeam = "Technical Support"
end if
sTQuery = "Select " & _
 "TeamMems.LoginID, TeamMems.Team, Profile.Firstname, Profile.LastName,
Profile.PhoneExt " & _
"FROM " & _
 "TeamMems TeamMems LEFT OUTER JOIN PROFILE PROFILE ON " & _
 "TeamMems.LoginID = PROFILE.CustID " & _
"WHERE " & _
 "TeamMems.Team = '" & TargetTeam & "'"
rsTeam.Open sTQuery, cnHeat, 0,1 'Cursor Type=0 (Forward Only) Lock Method=1
(Read Only)
email = ""
'===========================================================================
====
Set f = fso.CreateTextFile("c:\temp\GrpMessage.txt", True)
f.Writeline "Dear " &TargetTeam& " member,"
f.Writeline ""
f.Writeline " The Heat call " &CallID& " has been assigned to the team "
&TargetTeam&", but has remained unaccepted for " &DaysOld& " days now."
f.Writeline "This call MUST be accepted. If YOU do not want to accept it
please find a member of the "&TargetTeam& " team who will accept it."
f.Writeline ""
f.Writeline TargetTeam& " members are:"
'===========================================================================
====
do until rsTeam.EOF
 email = email & rsTeam("FirstName") &"."& rsTeam("LastName")

 f.Writeline rsTeam("PhoneExt") &vbTab&rsTeam("FirstName")&"
"&rsTeam("LastName")
 rsTeam.MoveNext
loop
email = left(email, len(email)-1)
if TargetTeam = "Help Desk" then
 email = "...(removed)..."
end if
'===========================================================================
====
f.Writeline ""
f.Writeline "Alternatively contact the Help Desk on 8057 to arrange
appropriate assignment for this call."
f.Writeline ""
f.Writeline ""
f.Writeline CallID& " details are:"
f.Writeline ""
f.Writeline MFATCalldesc
f.Writeline ""
f.Writeline CallDesc
f.Close
'===========================================================================
====
rsTeam.Close
GetTeamMembersEmailAddresses = email
end function

Function GetAssigneeEmailAddress(TargetAssignee)
sTQuery = "Select " & _
 "Profile.Firstname, Profile.LastName " & _
"FROM " & _
 "PROFILE " & _
"WHERE " & _
 "Profile.CustID = '" & TargetAssignee & "'"
rsTeam.Open sTQuery, cnHeat, 0,1 'Cursor Type=0 (Forward Only) Lock Method=1
(Read Only)
email = ""
do until rsTeam.EOF
 email = email & rsTeam("FirstName") &"."& rsTeam("LastName")

 FirstName = rsTeam("FirstName")
 rsTeam.MoveNext
loop
'===========================================================================
====
Set f = fso.CreateTextFile("c:\temp\Message.txt", True)
f.Writeline "Dear " &FirstName& ","
f.Writeline ""
f.Writeline " The Heat call " &CallID& " has been assigned to you, but has
remained unaccepted for " &DaysOld& " days now."
f.Writeline "This call MUST be accepted. If YOU do not want to accept it
please contact the Help Desk on 8057 to arrange appropriate assignment for
this call."
f.Writeline ""
f.Writeline ""
f.Writeline CallID& " details are:"
f.Writeline ""
f.Writeline MFATCalldesc
f.Writeline ""
f.Writeline CallDesc
f.Close
'===========================================================================
====
rsTeam.Close
GetAssigneeEmailAddress = email
end function


Quote:
> Do you have any other suggestions?  The goal is to get e-mail addresses
out
> of SQL Server.  But be able to run through the MS dos prompt so it can be
> done automatically every week.

> --
> *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
> Barbara Eichinger
> Web Programmer

> Slumberland Furniture
>    http://www.slumberland.com

> 3060 Centerville Rd.
> Little Canada, MN  55117

> ph  651.490.6281
> fax  651.482.0157
> *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~


> > Application is an ASP built-in object.  You'll need another way to
obtain
> username.  If this is
> > their NT domain username, use the WshNetwork object's UserName
property...
> if it isn't, you'll need
> > to prompt for it and you'll have to prompt for password anyway.  You may
> be better off writing this
> > as an HTA instead of a .vbs so you can mask the password entry...

> > --
> > Michael Harris
> > Microsoft.MVP.Scripting
> > --

> > Please do not email questions - post them to the newsgroup instead.
> > --



> > > Your help would be greatly appreciated.
> > > I have come across a file that doesn't want to work.  It is a .vbs
file
> and
> > > the error I am getting is...

> > > (22,1)  VBScript runtime error: Type mismatch: 'Application'

> > > Here is a portion of the code:

> > > Set Mailer = WScript.CreateObject("ASPMail.ASPMailCtrl.1")

> > > ' Debug
> > > Set objFS = WScript.CreateObject("Scripting.FileSystemObject")
> > > if (objFS.FileExists("path goes here")) then
> > >   objFS.CopyFile "path goes here", "path goes here", true
> > >   objFS.DeleteFile "path goes here"
> > > end if
> > > Set objOutFile = objFS.OpenTextFile("path goes here", 8, true)


> > > subject    = "Weekly delivery of Ad Requests"
> > > mailserver = "mailserver"
> > > message    = "This is an automated message generated from the
> Slumberland
> > > Extranet. " _
> > >   & "Attached are the ad requests for the week of " & Date() + 28 &
"."
> > > attach = "path goes here"

> > > '###  ESTABLISH connection to the database new web.mdb file.  ###
> > > set cnnLogin = WScript.CreateObject("ADODB.Connection")
> > > cnnLogin.Open "Slumberland", Application("username"),
> > > Application("password")                error is on this line
> > > set rsLogin = Server.CreateObject("ADODB.Command")

> > > --
> > > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
> > > Barbara Eichinger
> > > Web Programmer

> > > Slumberland Furniture
> > >    http://www.slumberland.com

> > > 3060 Centerville Rd.
> > > Little Canada, MN  55117

> > > ph  651.490.6281
> > > fax  651.482.0157
> > > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~



Tue, 20 May 2003 04:13:42 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Need Help in building a connection to SQL server

2. Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

3. Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

4. need odbc connection - no sql server

5. Need Help in building a connection to SQL server

6. Need Help in building a connection to SQL server

7. SQL Server connections not closing when Access connection also open

8. SQL Server db connections

9. How Do I force a SQL server to drop a connection

10. HELP: ASP / SQL Server connection hangs when user aborts processing

11. adding a user DSN connection to MS SQL server 2000 using AD GPO logon scripts

12. Closing Connections SQL Server

 

 
Powered by phpBB® Forum Software