locked
Doubt when loading database information RRS feed

  • Question

  • User662762443 posted

    Personally, I have the following problem, I have a web page, I have a search field, I need to search for a specific voucher, but it is related to the number of a process, the process number should be used to make this query, and return with the database information, I have the following fields in my table: codProcInter, Interested, Barcode, Payment date and voucher value. How could I start this logic to display the information in a label?

    Thursday, October 25, 2018 6:37 PM

Answers

  • User-271186128 posted

    Hi rtaVix,

    I have a search field, I need to search for a specific voucher, but it is related to the number of a process, the process number should be used to make this query, and return with the database information, I have the following fields in my table: codProcInter, Interested, Barcode, Payment date and voucher value

    From your description, I'm not sure the relationship between process number the voucher, and the table doesn't contain the process number field.

    So, I guess perhaps there have two tables : TableA contains these fields: codProcInter, Interested, Barcode, Payment date and voucher value. TableB contains the process number field. You want to filter the TableA's data using the TableB's field. If that is the case, TableA and TableB must contains the foreign key, you could get the foreign key based on the process number, then filter the TableA table to get the voucher using this foreign key.

    Besides, here is an sample about query data based on the related entity, you could refer to it:

    The Company and User contain one to many relationship (using Entity Framework Code First),

        public class User
        {
            [Key]
            public Guid userId { get; set; }
            public Guid companyId { get; set; }
            public string firstName { get; set; }
            public string lastName { get; set; }
            public string email { get; set; }
    
            [ForeignKey("companyId")]
            public Company Company { get; set; }
        }
        public class Company
        {
            [Key]
            public System.Guid companyId { get; set; }
            public string companyName { get; set; }
            public string companyUrl { get; set; }
    
            public virtual ICollection<User> Users { get; set; }
        }

    Then I could use the following code to query the user based on the company name:

                    var company = "Google";
                    IQueryable<User> query2 = from item in db.Users
                                  where item.Company.companyName == company
                                  select item;

    More details about querying related entities, please refer to this article.

    http://www.entityframeworktutorial.net/entityframework4.3/explicit-loading-with-dbcontext.aspx

    If above methods not working, please explain more details about the relationship between the process number and your table.

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 26, 2018 2:20 AM
  • User753101303 posted

    Hi,

    So you should have some common value between both table. I assulme a Process is keeping the Voucher id it created ? So it should be something such as :
    SELECT * FROM Voucher JOIN Process ON Process.VoucherId=Voucher.VoucherId

    Now a problem is that you are using ExecuteScalar which returns the first row, first column rather than a full row (you could use a DataReader instead).

    As it seems you are just starting you could consider using EF (whose purpose is to expose db data as .NET objects) and in particular https://docs.microsoft.com/en-us/aspnet/web-forms/overview/presenting-and-managing-data/model-binding/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 29, 2018 12:40 PM

All replies

  • User-271186128 posted

    Hi rtaVix,

    I have a search field, I need to search for a specific voucher, but it is related to the number of a process, the process number should be used to make this query, and return with the database information, I have the following fields in my table: codProcInter, Interested, Barcode, Payment date and voucher value

    From your description, I'm not sure the relationship between process number the voucher, and the table doesn't contain the process number field.

    So, I guess perhaps there have two tables : TableA contains these fields: codProcInter, Interested, Barcode, Payment date and voucher value. TableB contains the process number field. You want to filter the TableA's data using the TableB's field. If that is the case, TableA and TableB must contains the foreign key, you could get the foreign key based on the process number, then filter the TableA table to get the voucher using this foreign key.

    Besides, here is an sample about query data based on the related entity, you could refer to it:

    The Company and User contain one to many relationship (using Entity Framework Code First),

        public class User
        {
            [Key]
            public Guid userId { get; set; }
            public Guid companyId { get; set; }
            public string firstName { get; set; }
            public string lastName { get; set; }
            public string email { get; set; }
    
            [ForeignKey("companyId")]
            public Company Company { get; set; }
        }
        public class Company
        {
            [Key]
            public System.Guid companyId { get; set; }
            public string companyName { get; set; }
            public string companyUrl { get; set; }
    
            public virtual ICollection<User> Users { get; set; }
        }

    Then I could use the following code to query the user based on the company name:

                    var company = "Google";
                    IQueryable<User> query2 = from item in db.Users
                                  where item.Company.companyName == company
                                  select item;

    More details about querying related entities, please refer to this article.

    http://www.entityframeworktutorial.net/entityframework4.3/explicit-loading-with-dbcontext.aspx

    If above methods not working, please explain more details about the relationship between the process number and your table.

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 26, 2018 2:20 AM
  • User662762443 posted

    I did not quite understand, my friend. In my web page I have a textbox where I enter the process number, this process number is in a table called process.

    Friday, October 26, 2018 4:11 PM
  • User-271186128 posted

    Hi rtaVix,

    In my web page I have a textbox where I enter the process number, this process number is in a table called process. I have the voucher table that I have the following fields in the table:

    Voucher table:

    code, type sequence- belongs to the voucher table. (Data_Type: Number)

    Cod_Proc_Inter- This code is referenced in the table called table_proc_Inter. (Data_Type: Number)

    barCode- belongs to the voucher table. (Data_Type: Varchar2: 200 Byte)

    Scheduling date- belongs to the voucher table. (Data_Type: DATE)

    Documentvalue- belongs to the voucher table. (Data_Type: Number (15,2)

    registrydescription- belongs to the voucher table. (Data_Type: Number)

    What's the relationship between the process table and voucher table? How can I find the voucher based on the process number? via Cod_Proc_Inter?

    Best regards,
    Dillion

    Monday, October 29, 2018 5:48 AM
  • User662762443 posted

    Dillion,

    Each voucher has a relationship with the process table, the process to be paid it generates a voucher carrying the other information, so it has this relationship, each voucher has a process, and each process has a voucher, so I do this select, because I must bring the information on the screen for the user to see through the number of the process entered.

    Monday, October 29, 2018 12:26 PM
  • User753101303 posted

    Hi,

    So you should have some common value between both table. I assulme a Process is keeping the Voucher id it created ? So it should be something such as :
    SELECT * FROM Voucher JOIN Process ON Process.VoucherId=Voucher.VoucherId

    Now a problem is that you are using ExecuteScalar which returns the first row, first column rather than a full row (you could use a DataReader instead).

    As it seems you are just starting you could consider using EF (whose purpose is to expose db data as .NET objects) and in particular https://docs.microsoft.com/en-us/aspnet/web-forms/overview/presenting-and-managing-data/model-binding/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 29, 2018 12:40 PM