Question 88048 in ASP.NET Question posted in
The official documentation can be found here.

How to assign values from sql table which is stored vertically? – Asp.net

I have been checking many other similar questions but I couldn’t find an answer. I have a situation where I need to show UI a Grid whose values will be pull out from a vertical data table.

For instance, let’s say, In the UI I have to show a grid like below based on the P_ID from database product table

Product name IsLaunched Exp_Price
AC1 1 4000
AC2 0 3000
AC3 0 3000

The values in the Grid will be populated from Database , which is stored in the product table like below:

P_ID Product name IsLaunched Exp_Price
1 LCD TV 1 2000
1 LED TV 1 2000
2 AC1 1 4000
2 AC2 0 3000
2 AC3 0 3000

so if I select 2 from the drop-down , the result grid should show all the products which have the p_id as 2.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    So here is the solution which i implemented which i felt like might be usefull for other who have a similar query.

    In .aspx page

    <table id="tblid" style="width: 100%; text-align: center;" runat="server">
  <tr>
    <td align="center">
      <asp:PlaceHolder ID="DBDataPlaceHolder" runat="server"></asp:PlaceHolder>
    </td>
  </tr>
</table>

    In aspx.cs page

      private void loadProductTable()
    {
        try
        {

            string ProductID =  **id from ui** 
            DataTable dt = GetProductDetails(ProductID);

            StringBuilder htmlTable = new StringBuilder();
            htmlTable.Append("<!DOCTYPE>");
            htmlTable.Append("<table border='1'");
            htmlTable.Append("style ='font-size: 12px; border-spacing: 0px; letter-spacing: 0.48px; font-family: Arial; line-height: 25px; text-align: center; margin : 1%;'>");
            htmlTable.Append("<tr style='background-color:grey; color: White;'>");
            htmlTable.Append("<th style='width : 350px'> Product Name </th>");
            htmlTable.Append("<th style='width : 100px'> Is Lanched</th>");
            htmlTable.Append("<th style='width : 100px'>Expected Price (In USD) </th>");               

            if (dt.Rows.Count > 0)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    htmlTable.Append("<tr>");
                    htmlTable.Append("<td style='text-align: left; padding-left: 10px;'>" + dt.Rows[i]["Product_name"] + "</td>");
                    htmlTable.Append("<td>" + dt.Rows[i]["IsLaunched"] + "</td>");
                    htmlTable.Append("<td>" + dt.Rows[i]["Exp_Price"] + "</td>");
                    htmlTable.Append("</tr>");
                }
                htmlTable.Append("</table>");
                DBDataPlaceHolder.Controls.Add(new Literal { Text = htmlTable.ToString() });                    
                
            }
            else
            {
                htmlTable.Append("<tr>");
                htmlTable.Append("<td align='center' colspan='4'>There is no Record.</td>");
                htmlTable.Append("</tr>");
            }
        }
        catch (SystemException ex)
        {
            ExceptionManager.HandleException(ex);
        }

    }

    and in Stored procedure

    CREATE PROCEDURE GetProductDetails
    (
     @ProductID int 
    )
AS                      
BEGIN                      
SET NOCOUNT ON;    
    
BEGIN try     
      
DECLARE @ErrorMessage  VARCHAR(4000),    
              @ErrorSeverity INT,    
              @ErrorState    INT,    
              @Error         VARCHAR(40)  

select  ROW_NUMBER() OVER
      (
        ORDER BY [PrimaryKeyID] ASC     
      ) 
      AS RowNumber,
      P_ID,
      Product_name,
      CASE WHEN IsLaunched= 0 THEN 'No'
      ELSE 'Yes'
      END AS IsLaunched,
      Exp_Price INTO #temp
      FROM product 
      WHERE p_id = @ProductID     
      
      SELECT  * FROM #temp WHERE RowNumber <> 1 // since i dont want to show the first default product


              
END TRY

---------Begin Catch-------------------------     
  BEGIN catch    
                   SELECT @ErrorMessage = Error_message(),     
                    @ErrorSeverity = Error_severity(),     
                    @Error               =@@Error,     
                    @ErrorState = Error_state();    
    
          RAISERROR ( @ErrorMessage,@ErrorSeverity,@ErrorState );     
    
           
    
END catch    
RETURN 1    
    
SET NOCOUNT OFF;    
    
End

  2. 0

    Example query that results with the data of your example

    select P_ID, `Product name`, IsLaunched, Exp_Price
from product
where P_ID = 2

    So far, so good. Now, we need to make sure that we dynamically receive the value set in the dropdown. That means that on the UI some event happens that changes a value. If you are working on a web app, then this will probably be a request parameter that your server-side will need to handle. If it’s a desktop application, then the backend of the application needs to handle the event. Anyway, depending on the application stack you use, you will need to process the value, build a parameterized query where you pass the chosen ID (and protect against SQL injection shenanigans) to a query executor, which will ultimately run the query and return the results.

    Login or Signup to reply.
  3. 0

    You can do that with filtering by P_ID:

      private void LoadData(string P_ID_Selected)
    {
        try
        {
            //P_ID_Selected parameter send from drop-down selectedIndexChanged event
            string ConnStringDB = "your connection string here";
            string query = "SELECT P_ID, `Product name`, IsLaunched, Exp_Price from product where P_ID = " + P_ID_Selected;
            SqlConnection con = new SqlConnection(ConnStringDB);//connection name
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con)
            {
                CommandType = CommandType.Text
            };
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "DataSetName");
            GridViewName.DataSource = ds.Tables["DataSetName"];
            con.Close();
        }
        catch
        {
            GridViewName.DataSource = null;
        }
        finally
        {
            GridViewName.DataBind();
        }
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search