Getting error while trying to retrieve value from db using ADO.CreateCommand()

[sriram_sig]

I'm trying to execute the below code , but for some reason i'm getting errors which i'm unable to solve

AConnection = General.GetConnection()
ADOCommand = ADO.CreateCommand()
ADOCommand.ActiveConnection = AConnection
ADOCommand.CommandText='select count(*) from Notification where EmployeeID=?'
ADOCommand.CreateParameter('EmployeeID',adVarChar,adParamInput,-1,'1000)
RecordSet = ADOCommand.Execute()
  while not RecordSet.EOF:
    Notificationcount = RecordSet.Fields.Item[0].Value
    RecSet.MoveNext()
  AConnection.Close()

def GetConnection():
  AConnection = ADO.CreateADOConnection()
  AConnection.ConnectionString  = "Provider=SQL Server Native Client 11.0;" + "Server=<ServerName>;"+"Database=<dbname>;"+"Trusted_Connection=yes;"
  AConnection.LoginPrompt = False
  AConnection.Open()
  return AConnection

But i get the below error when i try assign this connection to ADOCommand

The method returning the db connection seems to be fine, because the below code which calls the same method executes fine and gives the result

AConnection = General.GetConnection()
  RecSet = AConnection.Execute_("select count(EmployeeID) from Notification where EmployeeID = " + EmployeeID)
  RecSet.MoveFirst()
  while not RecSet.EOF:
    Notificationcount = RecSet.Fields.Item[0].Value
    Log.Message("Count - " + aqConvert.IntToStr(Notificationcount))
    RecSet.MoveNext()
  AConnection.Close()

[tristaanogre · Accepted solution]

In General.GetConnection, change it from CreateADOConnection to just CreateConnection.  The two methods return two different object types.

https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createconnection.html

https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createadoconnection.ht...

The ActiveConnection property of the ADO.CreateConnectionis a Microsft ADO connection object while the ADO.CreateADOConnection is an IAQAADOConnection object.  Since you're using ADO.CreateCommand which ALSO creates a Microsoft object (as opposed to a Borland VCL object), you should use the method that matches.

[sriram_sig]

Thank you! It worked fine after changing it to ADO.CreateConnection(). This is the final working code , thought of posting it here as it might be helpful to someone.

AConnection = General.GetConnection()
ADOCommand = ADO.CreateCommand()
ADOCommand.ActiveConnection = AConnection
ADOCommand.CommandText='select count(*) from Notification where EmployeeID=?'
Prm = ADOCommand.CreateParameter('EmployeeID',adVarChar,adParamInput,-1,'1000)
ADOCommand.Parameters.Append(Prm)
RecordSet = ADOCommand.Execute()
  while not RecordSet.EOF:
    Notificationcount = RecordSet.Fields.Item[0].Value
    RecordSet.MoveNext()
  AConnection.Close()

def GetConnection():
  AConnection = ADO.CreateConnection()
  AConnection.ConnectionString  = "Provider=SQL Server Native Client 11.0;" + "Server=<ServerName>;"+"Database=<dbname>;"+"Trusted_Connection=yes;"
  AConnection.LoginPrompt = False
  AConnection.Open()
  return AConnection