Querying a SQL Server DB and returning more than one column

[scmadden]

Instead of recording a script, I am attempting to query a given database table myself. I am able, but I am only able to return records for one column. Is there a way I can append to what I have here in the while loop to add columns and view like a table in my log? My sample code:

function RunSimpleQuery()
{
var AConnection, RecSet;
//Create ADO connection
AConnection = ADO.CreateConnection();
AConnection.ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=*******;Initial Catalog=*******;Data Source=*******";
AConnection.Open();
RecSet = AConnection.Execute("SELECT * FROM someDB.SomeTable WHERE statuscode != 4");
Log.AppendFolder("This is a test");
RecSet.MoveFirst();
while(! RecSet.EOF )
{
Log.Message(RecSet.Fields.Item("statuscode").Value);
RecSet.MoveNext();
}
AConnection.Close();
}

Like I said, I have more than one column to return in results for log. Any help would be appreciated.

Stephen Madden

[tristaanogre]

You're not going to be able to write out a table in your log.  However, you can write out the values to a file, e.g. a CSV file, and then attach the file to your log.

[Wamboo · Accepted solution]

Hello scmadden

"Is there a way I can append to what I have here in the while loop to add columns and view as a table in my log?"

Yes, there is a way to achieve this and see results in Your log results.

1) Store data in an object, using a while loop or another simple solution in Your code.

2) Create a html string using this object:

let html = "<table style=\"width:100%\">";
html += "<tr>";
html += "<th>Your data</th>";
for (let x = 1; x <= length; x++) {
  html += "<th>Value" + x + "</th>";
}
html += "</tr>";
html += "<tr>";

3) use these values in Your log report using

var attr = Log.CreateNewAttributes();
attr.ExtendedMessageAsPlainText = false;
  
Log.Message("Check table Values", html, 300, attr);

Check values in log and see the table in the "deails" tab.

[TanyaYatskovska]

Thanks for the help,  Tristaanogre, Wamboo!

@scmadden, do the suggestions help you resolve the issue? Please share your solution with us.