evilmousse
Programmer
I'm attempting to create my own customized outtoexcel funtion, to be run client-side by users of an intranet asp.net application.
Right now I'm trying to get an accurate recordcount from my ado recordset object, but it always fails due to my inability to get a non-forwardonly recordset.
I'll also need to be considering how to manage the sqlserver authentication securely, since this would
involve passing the client a connection string to draw it's own data. I could use advice to this end.
Moving on to the problem at hand, can someone tell me why I continue to get a forwardonly recordset no matter how many times i explicitly ask for a static one?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<BODY>
<INPUT id="button1" name="button1" type="button" value="Button">
<SCRIPT LANGUAGE="VBScript">
sub button1_onclick()
'prepare excel
dim app
set app = createobject("Excel.Application"
app.Visible = true
dim wb
set wb = app.workbooks.add
'prepare ADO connection
Dim objConn, objRS
Set objConn = CreateObject("ADODB.Connection"
Set objRS = CreateObject("ADODB.Recordset"
With objConn
.Provider = "sqloledb"
.ConnectionString = "CENSORED" - right now a loalmachine with integrated sec
.Open
End With
'fill an array with a query
dim arr()
objrs.CursorType = adOpenStatic
objRS.Open "Select top 20 * from loan_info", objConn, adOpenStatic
dim iii
iii = 999
'If objRS.Supports(adBookmark) = True Then
' iii = objrs.RecordCount
'end if
dim i, ii, j, jj
ii = 19
jj = objRS.Fields.count
i=0
redim arr(20,jj-1)
'fill title row
j = -1
for each x in objRS.fields
if j < jj then
j=j+1
arr(0,j) = x.name
end if
next
'fill data rows
do until objRS.EOF or i > ii
j = -1
i = i+1
for each x in objRS.fields
if j < jj then
j=j+1
arr(i,j) = objRS.CursorType
'arr(i,j) = x.value
end if
next
objRS.moveNext
loop
objRS.close
objConn.close
Set objRS = Nothing
Set objConn = Nothing
'assign the array to the excel sheet
dim rng
set rng = wb.Activesheet.Range("A1"
.Resize(21,jj)rng.value = arr
'set freezepane, autoresize columns, & set title bgcolor to yellow
wb.Activesheet.Range("B2"
.Selectapp.ActiveWindow.FreezePanes = True
wb.Activesheet.Cells.Columns.AutoFit
wb.sheets("sheet1"
.Rows("1:1".Interior.ColorIndex = 6 ' Give the user control of Excel
app.UserControl = true
end sub
</SCRIPT>
</BODY>
</HTML>
