CODEWITHSUNDEEP
asp.netsql-serverlabel
lel
lel

Reputation: 327

Database value in label asp.net c#

I am trying to get value from database to be display in label. First i have to get the value of the dropdownlist and retrieved from database based on it. After that, I need to get the titlePromo column into my Label.

Currently i have the code out but i am not sure if it is the right one. There is no error but it displayed the membershipType column instead of the titlePromo. 

  ID  titlePromo   membershipType         defaults  
  --  ----------   --------------         ------    
  1    Promo 1     Membership Promotion      Y      
  2    Promo 2     Membership Renewal        Y       
  3    Promo 3     Membership Grad           Y       
  4    Promo 4     Membership Promotion      N       
  5    Promo 5     Membership Promotion      N      
  6    Promo 6     Membership Grad           N

My codes that i have done so far:

string strConnectionString = ConfigurationManager.ConnectionStrings["FYPDB"].ConnectionString;

            SqlConnection myConnect = new SqlConnection(strConnectionString);


            string strCommandText2 = "select * FROM FYPDB.dbo.Promotions where membershipType = '%' + @membership + '%' AND defaults = 'Y'";

                string ddlmembership = ((DropDownList)dvInsertPromotion.FindControl("ddlAddMembershiplist")).SelectedItem.ToString();

                cmd.Parameters.Add("@membership", SqlDbType.NVarChar);

                cmd.Parameters["@membership"].Value = ddlmembership;

 DataSet da2 = dal.retrieveTitle(ddlmembership);
                    SqlCommand cmd2 = new SqlCommand(strCommandText2, myConnect);

                    ((Label)pnlDefaultPopup.FindControl("Label13")).Visible = true;
                    ((Label)pnlDefaultPopup.FindControl("Label13")).Text = da2.Tables[0].Rows[0]["titlePromo"].ToString();

html:

.cs

  public DataSet retrieveTitle(String membership)
        {
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@membership", SqlDbType.NVarChar),

            };
            parameters[0].Value = membership;

            DataSet ds = new DataSet();
            ds = commons.ExecuteDataSet("Select * FROM Promotions WHERE (membershipType = '" + membership + "') AND defaults = 'Y' ");

            return ds;

        }

Upvotes: 0

Views: 1126

Answers (1)

Patricia
Patricia

Reputation: 71

Before giving you my suggestion I would like to make some remarks to your existing code:

  • you should select only the titlePromo in your query, as you only need one field, and not the entire row (therefore you wouldn't need a dataset in the first place)
  • the naming of your function is not according to its scope, at it does not retrieve the title, but an entire entry in the promotions table.
  • in this structure "membershipType = '%' + @membership + '%'" the syntax is not correct. The wildcards are used together with the "like" keyword

Bellow, you can find my code sample of how would I implement it if I were you:

static void Main(string[] args)
    {
        using (SqlConnection PubsConn = new SqlConnection(yourConnectionString))
        {
            //code to retrieve membership   
            var membership = "Membership Promotion";
            var title = retrieveTitle(PubsConn, membership);
           //code to set up label   
        }      
    }

    public static string retrieveTitle(SqlConnection conn, String membership)
    {
        conn.Open();

        var title = string.Empty;
        string strCommandText = "select top 1 titlePromo FROM Promotions where membershipType = @membership AND defaults = 'Y'";
        SqlCommand commmand = new SqlCommand(strCommandText, conn);
        commmand.Parameters.AddWithValue("@membership", membership);

        try
        {
            using (SqlDataReader reader = commmand.ExecuteReader())
            {
                if (reader != null && reader.Read())
                {
                    title = Convert.ToString(reader["titlePromo"]);
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error while retrieving table: " + ex.Message);
        }

        conn.Close();
        return title;
    }

If you want to use wildcards and 'like', you can do it like this:

string strCommandText = "select top 1 titlePromo FROM membershipTest where membershipType like @membership AND defaults = 'Y'";
SqlCommand commmand = new SqlCommand(strCommandText, conn);
commmand.Parameters.AddWithValue("@membership", "%" + membership + "%");

Upvotes: 1

Related Questions