VBScript - Using Excel As A Web Service Client
Here is the ASP server side code which queries the database and returns the results:
<%@ Language = "VBScript"%>
<%
Option Explicit
Response.Buffer = True
Const adOpenDynamic=2
Const adLockPessimistic=2
Const adCmdTable=2
Const adOpenForwardOnly=0
Const adSchemaTables=20
Const adOpenStatic=3
Const adLockReadOnly=1
Const adCmdText=1
Dim sql,queryId,tmp,field,connectString,dbConn,rs
queryId = Request.Form("queryId")
' A simple example of some queries
' The client will use HTTP Post to send a value for queryId and this
' piece of code will choose which SQL query to run based on what the
' client as asked for.
Select Case queryId
Case 1
sql = "SELECT * FROM T_Mchn"
Case 2
sql = "SELECT * FROM T_MLText"
End Select
' The connect string should be changed to fit the db in use
' here I am using TCP tp connect to SQL Server 2005
connectString="Provider=SQLNCLI;Server=127.0.0.1;Database=dv;Uid=*******;Pwd=********;"
Set dBConn = CreateObject("ADODB.Connection")
dBConn.Open connectString
' This is just a simple way of getting a record set from and SQL Query
Set rs=CreateObject("ADODB.RecordSet")
rs.Open _
sql, _
dbConn, _
adOpenStatic, _
adLockReadOnly, _
adCmdText
rs.MoveFirst
' Write column names
tmp=""
' This loop concatenates the field names (escaped) with pipe symbols
For Each field In rs.Fields
tmp = tmp & Escape(field.Name) & "|"
Next
' There is always 1 too many pipe symbols, this chops off the last one
tmp=Left(tmp,Len(tmp)-1) & VBCRLF
' Send the column headers to the client
Response.Write tmp
' Write data
While Not rs.EOF
tmp=""
' Here I use the same technique but this time for the data not headers
For Each field In rs.Fields
tmp = tmp & Escape("" & field.Value) & "|"
Next
tmp=Left(tmp,Len(tmp)-1) & VBCRLF
Response.Write tmp
rs.MoveNext
Wend
rs.close
%>
[Via]