Connecting To SQL Via VBS

[agent0047]

Thanks in advance, I am new to VBS and sp's so I am not really sure how to make this happen but here goes. I need a set of records that are grouped by 'Pending' to be updated every night via VBS, but I am not sure how to connect to the SQL Database with vbs. Any direction would be great?

set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=SQLNCLI;Server=SERVER\SQLEXPRESS;Database=Data;Trusted_Connection=yes;"
Conn.Open


sql = "SELECT * FROM [Data] WHERE [Data].[Status]="Pending"' " <--this should be grouped by 'Pending' so it chages all the pending records

rs.open sql, conn
'--

rs.Status="Processed"

rs.Update
rs.close
Conn.Close

 

[jbenson001]

Check this website:

http://www.connectionstrings.com

 

[agent0047]

There is nothing on there about vb only vb.net I don't need that but thanks for the reply

 

[AlexCuse]

Connection Strings are specific to the data provider type, not the programming language you're using.

Its' been a while since I did any VBScript but I think you can use either the standard OLEDB or ODBC strings

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[JoeAtWork]

There is nothing on there about vb only vb.net I don't need that but thanks for the reply

That's not correct, look under the section SQL Native Client OLE DB Provider. ADO is what you use in VB, and ADO is a wrapper for OLE DB.

In any case your connection string looks correct, but you are using trusted security, which means whatever account the VBScript is running under needs to be a login with sufficient rights in SQL Server.

Besides, your update won't work because you can't update a field in a grouped SELECT statement.

Why use a recordset in any case? Just execute an update directly:

Conn.Execute "UPDATE [DATA] SET Status = 'Processed' WHERE Status = 'Pending'"

 

[agent0047]

Ok I see now, but can you tell me if you guys think using the trusted rather than a password has security issues or not. The vbs is on the server but not in a root directory and runs locally. I read this could be a security issue but I don't see how as long as the server it’s on is secured from outside intrusion but I want to make sure I am not missing something. Thanks all for your help