Forum Discussion

sriram_sig's avatar
sriram_sig
Contributor
6 years ago
Solved

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

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()

 

2 Replies

Sort By
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    6 years ago

    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.html

     

    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's avatar
      sriram_sig
      Contributor
      6 years ago

      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