cancel
Showing results for 
Search instead for 
Did you mean: 

Querying a SQL Server DB and returning more than one column

Occasional Visitor

Querying a SQL Server DB and returning more than one column

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

3 REPLIES 3
Community Hero

Re: Querying a SQL Server DB and returning more than one column

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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Community Leader

Re: Querying a SQL Server DB and returning more than one column

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.

details-table.png

 

Community Manager

Re: Querying a SQL Server DB and returning more than one column

Thanks for the help,  Tristaanogre, Wamboo!

 

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

 

 

---------
Tanya Gorbunova
SmartBear Community Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
New Here?
Join us and watch the welcome video:
Watch the new Interview
Top Kudoed Authors