How to translate ado into ado.net?

  • Thread starter Thread starter Siegfried Heintze
  • Start date Start date
S

Siegfried Heintze

I want to translate the following script into VB.NET. Does ADO.NET have that
feature of ADO where it can read Excel? What would the connection string?
What classes would I use: the System.data.oleDB or system.data.ODBC?

Thanks,
Siegfried

'
'
' Begin commands to execute this file using windows scripting host with bash
' cscript query.vbs 080604FastPropAdams.xlsx
' End commands to execute this file using windows scripting host with bash

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
for kk = 0 to WScript.Arguments.Count-1
Wscript.Echo "begin " & kk & " = " & Wscript.Arguments.Item(kk)

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
WScript.Arguments.item(kk) & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

objRecordset.Open "Select * FROM [Sheet1$]", objConnection, adOpenStatic,
adLockOptimistic, adCmdText

call DumpFieldNames(objRecordset)
call DumpRecordSet(objRecordset)

next
Wscript.Echo "end query "
Wscript.Quit


Sub DumpFieldNames(objRecordset)
dim sFieldNames
sFieldNames = ""
sFieldTypes = ""
For ii = 0 to objRecordset.Fields.Count -1
If sFieldNames = "" Then
Else
sFieldNames = sFieldNames & ", "
sFieldTypes = sFieldTypes & ", "
End If
sFieldNames = sFieldNames & objRecordset.Fields.Item(ii).Name & "(" &
objRecordset.Fields.Item(ii).Type & "/" &
objRecordset.Fields.Item(ii).DefinedSize & "/" &
objRecordset.Fields.Item(ii).ActualSize & ")"
sFieldTypes = sFieldTypes & objRecordset.Fields.Item(ii).Type
Next

WScript.Echo sFieldNames
WScript.Echo sFieldTypes
End Sub

Sub DumpRecordSet(objRecordSet)

jj = 1
Do Until objRecordset.EOF

' Wscript.Echo objRecordset.Fields.Item("Name"),
objRecordset.Fields.Item("Number")

dim sFieldValues
sFieldValues = ""
For ii = 0 to objRecordset.Fields.Count -1
If sFieldValues = "" Then
Else
sFieldValues = sFieldValues & ", "
End If
sFieldValues = sFieldValues & objRecordset.Fields.Item(ii)
Next
WScript.Echo " <div><a href='#'
onclick='AddPushpin("""&sFieldValues&""");'>Add pushpin
"&sFieldValues&"</a></div>"
WScript.Echo " <div><a href='#'
onclick='Find("""&sFieldValues&""");'>go to "&sFieldValues&"</a></div>"

objRecordset.MoveNext

jj = jj + 1
if jj > 8 then
exit do
end if
Loop
End Sub
 
Back
Top