Query MS-SQL database through TC

The following line will not output anything

Log.Message aRecSet

aRecSet is a recordset data type where you need to grab the data from fields and display it.  If there is more than one row, you then need to interate through the recordset using a MoveNext command to move to the next row.  Simply logging the aRecSet object to the log won't do anything.

Hi Robert,



Thanks for the info...



As you suggested, I modified the code as below... I can see all the records displaying, but when I modified the SQL select query but it is not giving the results as per the SQL query... could you please help in this regard...!?!?



Sub TestSQL_ADO2

'Creates ADO connection

  Set aCon = ADO.CreateConnection



'Sets up the connection parameters

  aCon.ConnectionString = "Driver={SQL Server};" & _ 

                          "Server=WIN2K;" & _

                          "Database=INR;"



'Opens the connection

  aCon.Open

 

'Creates a command and specifies its parameters

  Set aCmd = ADO.CreateCommand

  Set aCmd.ActiveConnection = aCon ' Connection

  aCmd.CommandType = adCmdTable ' Command type

  aCmd.CommandText = "controlinfo" ' Table name



  ' Opens a recordset

  Set aRecSet = aCmd.Execute("select * from controlinfo where Module like 'rental' and submodule = 'leads'") 'I'm not getting the results as per this SQL query, it is displaying all the records

  aRecSet.MoveFirst



  ' Obtains field names

  s = ""

  For i = 0 To aRecSet.Fields.Count - 1

    s = s & aRecSet.Fields.Item(i).Name & vbTab

  Next

  s = s & vbCrLf



  ' Scans recordset

  Do While Not aRecSet.EOF

    For i = 0 To aRecSet.Fields.Count - 1

      s = s & aRecSet.Fields.Item(i).Value & vbTab

    Next

    s = s & vbCrLf

    aRecSet.MoveNext

  Loop



  ' Outputs results

  Log.Message "controlinfo", s



  ' Closes the recordset and connection

  aRecSet.Close

  aCon.Close



End Sub

I can't be sure what the specific problem is since I don't have your database to try out the query.  My guess, though, is that your where clause needs some grouping symbols.



try the following on that line:

Set aRecSet = aCmd.Execute("select * from controlinfo where (Module like 'rental') and (submodule = 'leads')")

Hi Robert...



Its been a while I dint work on this issue... I have a query on Log.Message

Actually what I observed is as below...



Example1:

The below log is displaying details of Code field only. It is not displaying details of Module field. The example given in CreateADOQuery is as below...

Log.Message bCon.FieldByName("Code").Value, bCon.FieldByName("Module").Value



Example2:

As shown below after using '&' symbol in between each field, I'm getting expected output

The below log will display all the details of Code and Module fields from database

Log.Message bCon.FieldByName("Code").Value &" | " &bCon.FieldByName("Module").Value

The module value is, in your first example, being placed in the Additional Information pane of the message view in the log.  so, while you're not seeing it in the main log view, it is being logged.



The reason why the second one works is because you are concatenating the strings together into the main log view and not using the Additional Information.

Hi Robert...



I did not check additional informaiton window... its showing other deatils in that box

Thank you very much...