.vbs file script (DB Connectivity) 
Author Message
 .vbs file script (DB Connectivity)

OK,

Question two today,

Is it possible/how can I open a database object in a .vbs file.

I have a system DSN for the access 97 db and require read write access, so I
can run update SQL queries.

again, any help appreciated.

Darren



Mon, 02 Aug 2004 23:05:09 GMT  
 .vbs file script (DB Connectivity)
dim ConnStr = "UID=Holly;PWD=Huskador;" _
   & "DATABASE=MyDb;" _
   & "SERVER=MyServer;" _
   & "DRIVER={SQL SERVER};DSN='';"

Public Sub OpenX()

   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String
   Dim varDate As Variant

   ' Open connection.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn

   ' Open employee table.
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.Open "employee", cnn1, , , adCmdTable

   ' Assign the first employee record's hire date
   ' to a variable, then change the hire date.
   varDate = rstEmployees!hire_date
   Debug.Print "Original data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date
   rstEmployees!hire_date = #1/1/1900#
   rstEmployees.Update
   Debug.Print "Changed data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date

   ' Requery Recordset and reset the hire date.
   rstEmployees.Requery
   rstEmployees!hire_date = varDate
   rstEmployees.Update
   Debug.Print "Data after reset"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date

   rstEmployees.Close
   cnn1.Close

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 02 Aug 2004 23:17:02 GMT  
 .vbs file script (DB Connectivity)
Dont look at exemple with Public Sub OpenX()
********************************************
MSDN
Here is the same example written in VBScript to be used in an Active
Server Page (ASP). To view this fully functional example, you need to
create a system Data Source Name (DSN) called AdvWorks using the data
source AdvWorks.mdb installed with IIS and located at
C:\InetPub\ASPSamp\AdvWorks. This is a Microsoft Access database file.
Use Find to locate the file Adovbs.inc and place it in the directory you
plan to use. Cut and paste the following code to Notepad or another text
editor and save it as ADOOpen.asp. You can view the result in any client
browser.

<!-- #Include file="ADOVBS.INC" -->
<HTML><HEAD>
<TITLE>ADO Open Method</TITLE>
</HEAD><BODY>
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Open Method</H3>
<TABLE WIDTH=600 BORDER=0>
<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>
<!--- ADO Connection used to create 2 recordsets-->
<%
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
SQLQuery = "SELECT * FROM Customers"
'First Recordset RSCustomerList
Set RSCustomerList = OBJdbConnection.Execute(SQLQuery)
'Second Recordset RsProductist
Set RsProductList = Server.CreateObject("ADODB.Recordset")
RsProductList.CursorType = adOpenDynamic
RsProductList.LockType = adLockOptimistic
RsProductList.Open "Products", OBJdbConnection
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company
Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact
Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail
address</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff"
SIZE=1>State/Province</FONT></TD></TR>

<!--Display ADO Data from Customer Table-->
<% Do While Not RScustomerList.EOF %>
  <TR><TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RSCustomerList("CompanyName")%>
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RScustomerList("ContactLastName") & ", " %>
  <%= RScustomerList("ContactFirstName") %>
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RScustomerList("ContactLastName")%>
 </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RScustomerList("City")%>
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RScustomerList("StateOrProvince")%>
  </FONT></TD></TR>
<!-Next Row = Record Loop and add to html table-->
<%
RScustomerList.MoveNext
Loop
RScustomerList.Close
OBJdbConnection.Close
%>
</TABLE>
<HR>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Product List Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product
Type</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product
Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product
Description</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit
Price</FONT></TD></TR>
<!-- Display ADO Data Product List-->
<% Do While Not RsProductList.EOF %>
  <TR>  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RsProductList("ProductType")%>
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RsProductList("ProductName")%>
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
   <%= RsProductList("ProductDescription")%>
 </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RsProductList("UnitPrice")%>
  </FONT></TD>

<!--  Next Row = Record -->
<%
RsProductList.MoveNext
Loop
'Remove Objects from Memory Freeing  
Set RsProductList = Nothing
Set OBJdbConnection = Nothing
%>
</TABLE></FONT></Center></BODY></HTML>

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 02 Aug 2004 23:21:05 GMT  
 .vbs file script (DB Connectivity)
Sorry forgot to say, I dont have a IIS server to hand to run this, plus i
dont want any output from the script other than to do some updateing of the
DB, it will be automated to run at time intervals.

Darren

Quote:
> OK,

> Question two today,

> Is it possible/how can I open a database object in a .vbs file.

> I have a system DSN for the access 97 db and require read write access, so
I
> can run update SQL queries.

> again, any help appreciated.

> Darren



Mon, 02 Aug 2004 23:36:28 GMT  
 .vbs file script (DB Connectivity)
If you want to use Access in local PC, do like,

set c=createobject("adodb.connection")
str1="provider=microsoft.jet.oledb.4.0; data source=c:\db1.mdb"
'str1="provider=msdasql;dsn=ms access database; dbq=c:\db1.mdb"
c.open str1
q="update table1 set a='abc' where a='a'"
c.execute q

You use oledb or odbc connection, the first prefered. And dsn is another
budden to db, I recommand to kick out it.


Quote:
> Sorry forgot to say, I dont have a IIS server to hand to run this, plus i
> dont want any output from the script other than to do some updateing of
the
> DB, it will be automated to run at time intervals.

> Darren


> > OK,

> > Question two today,

> > Is it possible/how can I open a database object in a .vbs file.

> > I have a system DSN for the access 97 db and require read write access,
so
> I
> > can run update SQL queries.

> > again, any help appreciated.

> > Darren



Tue, 03 Aug 2004 03:48:16 GMT  
 .vbs file script (DB Connectivity)
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadocodeexampl...
cript.asp

Hope it helps.

Regards,
Robin

This posting is provided AS IS, with no warranties, and confers no rights.



Fri, 06 Aug 2004 14:22:48 GMT  
 .vbs file script (DB Connectivity)
ADO Code Examples in Microsoft Visual Basic Scripting Edition
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadocodeexampl...
cript.asp

Hope it helps.

Regards,
Robin

This posting is provided AS IS, with no warranties, and confers no rights.



Fri, 06 Aug 2004 14:23:22 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. DB Connectivity

2. Resolving IPconfig within VBS/ADODB connectivity

3. Include another VBS script file in a main VBS script

4. Running a file from a .VBS script

5. vbs Timer script to run other script

6. Launching 6 .vbs scripts simultaneously from a single script

7. Cannot Start debugging .vbs-Script in MS Script Debugger

8. DB connectivity: ODBC direct or Jet connected?

9. Need more DB connectivity ways

10. DB connectivity: ODBC direct or Jet connected?

11. MySql DB connectivity speed issue

12. Test terminal server / WLBS connectivity through Telnet/scripting?

 

 
Powered by phpBB® Forum Software