locked
How do i select a recored from table A based on the condition in table B in database RRS feed

  • Question

  • User-2074858223 posted

    i have two tables A and B, but i want to select records in table A based on the numbers selected from table B

    here is my code from table A

    protected void btnSubscrib_Click(object sender, EventArgs e)
        {
    
    
             if (txtPIN.Text == "")
            {
                ScriptManager.RegisterClientScriptBlock(btnSubscrib, this.GetType(), "alert", "<script>alert('Insert Pin Number ... !!')</script>", false);
            }
            else
            {
                lblMessage2.Text = "";
    
               
            if (this.Page.User.Identity.IsAuthenticated)
            {
                string username = this.Page.User.Identity.Name;
                GetUserScraps(username);
            }
            else
            {
    
            }
                }
            }
        
    
        public void GetUserScraps(string username)
        {
    
            string constr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    //SqlCommand cmd4 = new SqlCommand("Update Customer_Order set OrderDate= @OrderDate, Posted_By = @Posted_By, Branch=@Branch, CustomerID= @CustomerID, Payment_Methode= @Payment_Methode, Product_Code =@Product_Code, Category =  @Category, Brand = @Brand, Delivery_Manager =@Delivery_Manager, Quantity_By_Customer = @Quantity_By_Customer, Discount =@Discount, Unit_Price = @Unit_Price, Bank_Name = @Bank_Name, Delivery_Location= @Delivery_Location, Delivery_Date = @Delivery_Date, Total_Cost = @Total_Cost, Grand_Total = @Grand_Total WHERE Invoice_No= @Invoice_No", con9);
                    using (SqlCommand cmd = new SqlCommand("SELECT s.Id,v.Subject,v.Score,v.SendDate FROM  SchoolResult  WHERE UserName=@UserName   "))
                    {
                       
                     
                        cmd.Parameters.AddWithValue("@Id", "Id");
                      
                      
                        cmd.Parameters.AddWithValue("@Subject", "Subject");
                        cmd.Parameters.AddWithValue("@Score", "Score");
                        cmd.Parameters.AddWithValue("@SendDate", "SendDate");
    
                       
                        cmd.Parameters.AddWithValue("@UserName", username);
    
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                display.DataSource = dt;
                                display.DataBind();
                            }
                        }
                    }
                }
    
            
    
      
        }
        }

    this is the table B

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[PinCode](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[PinNumber] [float] NULL,
    	[date] [datetime] NULL,
     CONSTRAINT [PK_PinCode] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    

    Sunday, June 12, 2016 8:50 AM

Answers

  • User1559292362 posted

    Hi micah2012,

    What I want to achieve is this, I have a table in database that holds all the records of users, but I want each user too be able to use a bumblebee cord to access his or her records.. like example if user A wants to view his record a code or number will be given to the user to user, once the user inserts the code the user can now have access to his records, is like a pin card which the user have to buy from the admin to use it to access his records . I don't know if it's clear to you now?

    According to your description, it seems that you want to retrieve user information via table named PinCode. and according to your code snippet, it seems that the table named PinCode need a field associated with the table named SchoolResult (such as Id). if the table named PinCode has the field, you could use subquery to achieve it. like this:

    using (SqlCommand cmd = new SqlCommand("SELECT s.Id,v.Subject,v.Score,v.SendDate FROM  SchoolResult  WHERE UserName =   
        (SELECT UserName 
         FROM PinCode
         WHERE PinNumber= @PinNumber)"))
    {
    // other code
    }

     

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 13, 2016 6:34 AM

All replies

  • User475983607 posted

    i have two tables A and B, but i want to select records in table A based on the numbers selected from table B

    That's called a join and it is a design requirement that you must resolve.  

    http://sqlmag.com/t-sql/t-sql-join-types

    Looking at your code, I don't see how PinCode is possibly related to SchoolRecords or Customer_Order.  You'll need to explain what you're trying to do exactly as the problem statement is very vague.

    Sunday, June 12, 2016 11:17 AM
  • User-2074858223 posted
    What I want to achieve is this, I have a table in database that holds all the records of users, but I want each user too be able to use a bumblebee cord to access his or her records.. like example if user A wants to view his record a code or number will be given to the user to user, once the user inserts the code the user can now have access to his records, is like a pin card which the user have to buy from the admin to use it to access his records . I don't know if it's clear to you now?
    Sunday, June 12, 2016 2:30 PM
  • User1559292362 posted

    Hi micah2012,

    What I want to achieve is this, I have a table in database that holds all the records of users, but I want each user too be able to use a bumblebee cord to access his or her records.. like example if user A wants to view his record a code or number will be given to the user to user, once the user inserts the code the user can now have access to his records, is like a pin card which the user have to buy from the admin to use it to access his records . I don't know if it's clear to you now?

    According to your description, it seems that you want to retrieve user information via table named PinCode. and according to your code snippet, it seems that the table named PinCode need a field associated with the table named SchoolResult (such as Id). if the table named PinCode has the field, you could use subquery to achieve it. like this:

    using (SqlCommand cmd = new SqlCommand("SELECT s.Id,v.Subject,v.Score,v.SendDate FROM  SchoolResult  WHERE UserName =   
        (SELECT UserName 
         FROM PinCode
         WHERE PinNumber= @PinNumber)"))
    {
    // other code
    }

     

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 13, 2016 6:34 AM