VBScript/SQL Formatting Issue

Question

Okay so the script that I have written is almost fully functional but I would like to add two things to it. What it currently does is you type in a CallID number that is associated with other data on a database in SQL Server. When you type in the number into the msgbox it retrieves all the other columns that are associated with that particular number and outputs it to the command prompt and also outputs it to a text file on the hard drive. This is good, but the formatting is horrible. How would I go about improving the formatting of the file so it is more reading. Currently it is just a line with space seperating each piece of data. Also what I would like to be able to do is also have the name of each Column under each piece of data that is associated with that column. Any help would be appreciated. Here is my code with sensitive information omitted:

    Dim strQuery
    strQuery = InputBox("Enter CallID Please:")
    Dim sServer
    Dim sLogin
    Dim sPwd
    Dim sDb
    Dim oCn 
    Dim oRs
    Dim strFullQuery
    Dim strfield
    Const ForReading  = 1

    sServer   = ""
    sLogin    = ""
    sPwd      = ""
    sDb       = ""


    Set oCn = CreateObject( "ADODB.Connection" ) ' set oCn to create an object called ADODB.Connection
    Set oRs = CreateObject( "ADODB.Recordset"  ) ' set oRs to create an object called ADODB.Recordset

    oCn.ConnectionString = "PROVIDER=SQLOLEDB" & _      
                           ";SERVER="   & sServer   & _
                           ";UID="      & sLogin  & _
                           ";PWD="      & sPwd    & _
                           ";DATABASE=" & sDb & " "
                           oCn.ConnectionTimeout=600
                           oCn.open 'Open the connection to the server

    strFullQuery = "select * from dbo.CallLog where CallID=" + strQuery 'this is the SQL statement that runs a query on the DB

    oRs.Open strFullQuery,oCn 'This opens the record set and has two parameters, strFullQuery and oCn

    If oRs.EOF Then 'If open record set is at the end of file then...
      wscript.echo "There are no records to retrieve; Check that you have the correct record number." 'echo that there is no records to retrieve.
    End if

    'if there are records then loop through the fields
    oRs.MoveFirst 'move to the first object in the record set and set it as the current record
    Do Until oRs.EOF ' Do while not open record set is not end of file
    Set objFileSystem    = WScript.CreateObject("Scripting.FileSystemObject") 'Set objFileSystem to create object Scripting.FileSystemObject
    Set objOutPutFile    = objFileSystem.CreateTextFile("c:\test.txt", True) 'Set objOutPutFile to create object objFileSystem.CreateTextFile
    objOutPutFile.WriteLine strColumnNames
    strfield = oRs.GetString

    if strfield <> "" then 'If strfield doesn't equal "" then


      WScript.echo strfield  
      objOutPutFile.WriteLine  strfield &"|"

      'objFileSystem.close
      objOutPutFile.close  
    end If

    'oRs.MoveNext 'Move to the next object in the record set
    Loop
    oCn.Close

Can you include an example of what your data looks like now and what you would like it to look like? — aphoria
– aphoria, Commented Jul 18, 2013 at 14:33
<Data> <More Data> It just keeps continuing on the first line then goes to the next line. It is just a bunch of text and numbers that are only seperated by whitespace. I want to display the column name under each piece of data and maybe add some dividers between each piece of data. — Colin Kramer
– Colin Kramer, Commented Jul 18, 2013 at 14:39
Can you add an example to your question of what your data looks like now and what you want it to look like? Use the code formatting option of the editor to control the layout. — aphoria
– aphoria, Commented Jul 18, 2013 at 19:46
how about commas instead of lines and then just open it in excel to read it nicely :-) — Papasmile
– Papasmile, Commented Jul 18, 2013 at 19:58

Papasmile · Accepted Answer · 2013-07-18 20:06:05Z

1

You can add space to make fixed-widths. Let's say you know that every field will be 20 characters or less:

objOutPutFile.WriteLine  strfield & String(20-Len(strfield)," ") & "|"

answered Jul 18, 2013 at 20:06

Papasmile

6341 gold badge4 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBScript/SQL Formatting Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related