Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Collect data from table and write to a file

Status
Not open for further replies.

uadm26

IS-IT--Management
Apr 24, 2008
5
PT
Hi,
I need to do a vbscript or a VB that connect to a database, execute a sql statement and send the output to a file. I have the done something so far, but I can't finish it. Can someone help me?

Dim cmd
Dim rs
Dim params
Dim param
Dim ArgObj, sunumber

Set ArgObj = WScript.Arguments
set cn=createobject("ADODB.Connection")
set cmd=createobject("ADODB.Command")
set rs=createobject("ADODB.Recordset")

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run("%comspec% /c <Console Command>")

cn.Provider = "SQLOLEDB"
cn.Open "Data Source = xpto", "User", "User"

Set cmd.ActiveConnection = cn
cmd.CommandText = "SELECT (*) FROM CtrlBalcao"


 
Hi, PHV
That's my problem, when I use "count(*)" and this code, I only get one line.
cmd.Prepared = True

Set rs = cmd.Execute
sunumber = rs(0)
wscript.echo sunumber
cn.Close

But I need to insert the code to get all rows information to a file. That must be something like using a loop.
 
That must be something like using a loop
Indeed.
Have a look at the EOF property and the MoveNext method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Has I can see it's not so easy, maybe I will use other way to get this information.
 
Help me to finish this code, please.

Dim Source
Dim Connect
Dim Rs1,intString

Source = "SELECT * FROM UnidadeOrganica"
Connect = "Provider=sqloledb;Data Source=SQL2PMACL02;" & _
"Initial Catalog=GrupoBanifMCS;Integrated Security=SSPI;"

Set Rs1 =CreateObject( "ADODB.Recordset" )
Rs1.Open Source, Connect, adOpenForwardOnly

Do while not Rs1.EOF
'What code I need to put here to get the information.
Rs1.movenext
loop
 
A starting point:
Code:
Rs1.Open Source, Connect, adOpenForwardOnly
Do While Not Rs1.EOF
  For i = 0 To Rs1.Fields.Count - 1
    WScript.Echo Rs1(i).Name & " = " & Rs1(i).Value
  Next
  Rs1.MoveNext
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thank's PHV. But I decide to use something to output like csv:
But thank's any way.
Set Rs1 =CreateObject( "ADODB.Recordset" )
Rs1.Open Source, Connect, adOpenForwardOnly

Do while not Rs1.EOF
WScript.Echo Rs1(0) & "," & Rs1(1)
Rs1.movenext
Loop



 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top