how to write the search result in to text file or load it to the table on SUD or GUI

[Kandukuri_raghavendra]

Call GlobalDim("NEWT,CheckCalLimits,CalFileName,DefFileNm")
NEWT = CLng(NEWT)
dim myConn
dim myCommand
dim RS
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=FWATRGSQLVCL2;Initial Catalog=calsample;user id ='sampleUser';password='sample@1'"
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "SELECT [heid],[zvalue],[limit],[type],[xchannel],[xvalue],[ychannel],[yvalue],[class],[descrip] FROM [calsimple].[dbo].[Req_Simpledata] where hwid= "&NEWT&""
Set RS = myCommand.Execute

I have this code establishing connection to SQL and returning the result, is there a way, i can use this data to display it to the table on GUI or write this data to .txt file?

[tobriant]

If you have the data in successfully gathered from the SQL database, the vbscript for creating and writing to a .txt file should be something like:

Set objFSO=CreateObject("Scripting.FileSystemObject")

' How to write file
outFile="c:\test\autorun.inf"
Set objFile = objFSO.CreateTextFile(outFile,True)
objFile.Write "test string" & vbCrLf
objFile.Close

'How to read a file
strFile = "c:\test\file"
Set objFile = objFS.OpenTextFile(strFile)
Do Until objFile.AtEndOfStream
    strLine= objFile.ReadLine
    Wscript.Echo strLine
Loop
objFile.Close

[Kandukuri_raghavendra]

Hi Tyler,

 is vbCrLf, the object holding the result from SQL?

[Pesmith8]

Kandukuri,

No Vbcrlf is just the Carriage return,

I will send you some code on Saturday, that reads and SQL Db and returns the data as a Recordset.   The code will show how to access this recordset, into either Channels or arrays.

As far as how to put this data into a SUD.  Combobox works nice for this or an Xtable.  Xtable is very powerful, but takes more getting used to than a combobox.

Suggest that you look at both of these objects in the help system. They will be good friends of yours soon.

Paul

www.viviota.com 

ps. Some of your questions might be helped by looking at this website

http://www.savvydiademsolutions.com

[Pesmith8]

Hi Kandukuri,

Here is a script function that will execute a large sql query and return the results I wanted, in a Dictionary object using the Recordset.

Please review and see if helps some.

Paul

public sub GetDBPointDetails(byref oContext_,byval sDbConnection_, byref oDictParms)

dim Connection
dim Recordset
dim sSQL
dim Server
dim MajorDescription
dim PointCode
dim MajorCode
dim MinorDescription
dim MinorCode

sSQL = sSQL & " SELECT dbo.Point2Code.Point2CodeID, dbo.Point2Code.PointCode, dbo.Point2Code.MajorCode, dbo.Point2Code.MinorCode, dbo.Point2Code.XYLocationCode, dbo.Point2Code.OtherLocationCode, dbo.Point2Code.DistanceCode, "
sSQL = sSQL & " dbo.Point2Code.ActivePassiveCode, dbo.Point2Code.SuffixCode, dbo.Point2Code.TransducerTypeCode, dbo.Point2Code.CategoryCode, dbo.Point2Code.GeneralLocationCode, dbo.Point2Code.Suspension02Code, "
sSQL = sSQL & " dbo.Point2Code.SeatCode, dbo.Major.MajorDescriptionText, dbo.Minor.MinorDescriptionText, dbo.Minor.MinorDescriptionText02, dbo.XYLocation.XYLocationDescriptionText, dbo.OtherLocation.OtherLocationDescriptionText, "
sSQL = sSQL & " dbo.OtherLocation.OtherLocationID, dbo.Distance.DistanceDescriptionText, dbo.Distance.DistanceUnit, dbo.ActivePassive.ActivePassiveDescriptionText, dbo.Suffix.SuffixDescriptionText, dbo.seat.SeatDescriptionText, "
sSQL = sSQL & " dbo.TransducerType.TransducerTypeDescriptionText, dbo.Category.CategoryDescriptionText, dbo.Suspension02.Suspension02DescriptionText, dbo.GeneralLocaton.GeneralLocationDescriptionText,dbo.Point2CodeDescription.PointIDdescription "
sSQL = sSQL & " FROM dbo.Point2Code LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Suffix ON dbo.Point2Code.SuffixCode = dbo.Suffix.SuffixCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.XYLocation ON dbo.Point2Code.XYLocationCode COLLATE Latin1_General_CS_AS = dbo.XYLocation.XYLocationCode COLLATE Latin1_General_CS_AS LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Distance ON dbo.Point2Code.DistanceCode = dbo.Distance.DistanceCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Minor ON dbo.Point2Code.MinorCode = dbo.Minor.MinorCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.ActivePassive ON dbo.Point2Code.ActivePassiveCode = dbo.ActivePassive.ActivePassiveCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Category ON dbo.Point2Code.CategoryCode = dbo.Category.CategoryCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Major ON dbo.Point2Code.MajorCode = dbo.Major.MajorCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.GeneralLocaton ON dbo.Point2Code.GeneralLocationCode = dbo.GeneralLocaton.GeneralLocationCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Suspension02 ON dbo.Point2Code.Suspension02Code = dbo.Suspension02.Suspension02Code LEFT OUTER JOIN "
sSQL = sSQL & " dbo.seat ON dbo.Point2Code.SeatCode = dbo.seat.SeatCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.TransducerType ON dbo.Point2Code.TransducerTypeCode = dbo.TransducerType.TransducerTypeCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.OtherLocation ON dbo.Point2Code.OtherLocationCode COLLATE Latin1_General_CS_AS = dbo.OtherLocation.OtherLocationCode COLLATE Latin1_General_CS_AS LEFT OUTER JOIN"
sSQL = sSQL & " dbo.Point2CodeDescription ON dbo.Point2Code.PointCode COLLATE SQL_Latin1_General_CP1_CS_AS = dbo.Point2CodeDescription.PointCode COLLATE SQL_Latin1_General_CP1_CS_AS "

'SQL = replace(SQL,"SEARCH_TEXT",sPointCode)
'logfilewrite sSQL
'create an instance of the ADO connection and recordset objects
'
Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")

''open the connection to the database
call post_result(oContext_,"sDbConnection = " & sDbConnection_)
Connection.ConnectionString = sDbConnection_
Connection.Open

''Open the recordset object executing the SQL statement and return records 
'recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Recordset.Open sSQL,Connection

dim iRecCtr: iRecCtr= 0
''first of all determine whether there are any records 
If Recordset.EOF Then
'logfilewrite "There are no records to retrieve; Check that you have the correct job number."
Else

''if there are records then loop through the fields 
Do While NOT Recordset.Eof
dim oPointDetails: set oPointDetails = new cPointCodedetails
iRecCtr = iRecCtr +1
oPointDetails.majordescription = Recordset("MajorDescriptionText")
oPointDetails.pointid = Recordset("PointCode")
oPointDetails.majorcode = Recordset("MajorCode")
oPointDetails.minordescription = Recordset("MinorDescriptionText")
oPointDetails.minorcode = Recordset("MinorCode")
oPointDetails.pointdescription = Recordset("PointIDdescription")
oPointDetails.xylocationcode = Recordset("XYLocationCode")

if oPointDetails.MajorDescription <> "" then
'logfilewrite "[POINTCODE]: "&oPointDetails.pointid &" [MajorCode]:"&oPointDetails.majorcode & " [MajorText]:" & oPointDetails.majordescription
end if

' put line away in dictionary
' key is pointID and value is object with all the fields of interest.
If oDictParms.Exists(oPointDetails.pointid) Then
Call MsgBox("Key "&oPointDetails.pointid &" already exists")
Else
Call oDictParms.Add (oPointDetails.pointid, oPointDetails)
End If

Recordset.MoveNext
Loop
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset = nothing
Connection.Close
Set Connection = nothing
end sub

' Object to pass the parameters back to calling routine
class cPointCodedetails
dim pointid
dim majorcode
dim majordescription
dim minorcode
dim minordescription
dim minordescription2
dim xylocationcode
dim transducerdescription
dim seatdescription
dim generallocationDescription
dim distancedescription
dim suspensiondescription
dim suspension02description
dim distanceunit
dim pointdescription
end class