Community

• 

  Rahul Kapoor

  • February 02, 2021 10:04

  What I understood from your question is that you have a database table and you want data from that table in your dataset but only some filtered rows. 
  To implement this, instead of the Data Source as Database, you have to select "Script".

  Then you can write C# or VB code to write your SQL query with 'where' condition to get selected rows.

  Below is a sample script (for Vendor dataset) that you can refer:

  Imports System.Data.OleDb
  Imports Microsoft.VisualBasic
  Dim objConnection as oledbconnection
  dim objCommand as OleDbCommand
  dim objReader as OleDbDataReader
  dim objRecord as IDataSetRecord
  Try
      objConnection=new oledbconnection("Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxx;PWD=xxx;Initial Catalog=FLEXI;Data Source=tdf-sql")
      objCommand=objConnection.CreateCommand
      objCommand.CommandText="SELECT VendorId,Name1,Street1,City1,State1,Zip1,CountryCd FROM Vendors where Name1 not like 'Unused%'"
      objConnection.Open
      objReader=objCommand.ExecuteReader
      While objReader.Read 
          objRecord=DataSet.CreateRecord
          objRecord.AddValue("Id",objReader(0).ToString)
          objRecord.AddValue("Name",objReader(1).ToString)
          objRecord.AddValue("Street",objReader(2).ToString)
          objRecord.AddValue("City",objReader(3).ToString)
          objRecord.AddValue("State",objReader(4).ToString)
          objRecord.AddValue("ZIP",objReader(5).ToString)
          objRecord.AddValue("CountryCode",objReader(6).ToString)
          DataSet.AddRecord(objRecord)
      end while
      objReader.Close
  Catch Ex As System.Exception
      objRecord=DataSet.CreateRecord
      objRecord.AddValue("Name",ex.tostring)
      DataSet.AddRecord(objRecord)
  Finally
      Try
          objConnection.Close
      Catch Ex1 As System.Exception
      End Try
  End Try

  Hope this resolves your query!

  Regards,
  Rahul Kapoor
  www.linkedin.com/in/rahulkapoor309/

  No 1 Yes
• 

  Dheeraj Sah

  • February 17, 2021 06:47

  Thanks Its worked ,

  But just want to know , i have enable the option operator can edit and add the records in the dataset .

  but seems that option is not available when we are testing it through verification.

  is there any limitation when we use the script in place of database.

   

   

  No 0 Yes

Please sign in to leave a comment.