Dynamic Control Generation (need help)

Question

User-1335965041 posted

Generating controls from Dataset or table onto detail page and filtering it from controls on master?

This is a dynamic shopping cart

I want to dynamically load option controls onto a product page that are stored in a table or dataset

Option controls need to be filtered by product id on master

would like to submit input data to order table?

Way over my head?

Need Help

 

Answer 1

User-1760427068 posted

Before posting an example, I need to know if you use VB or C#.

[Idea]

A working example is available further into the posting that will be of interest to any beginner.  In addition, work is in progress at this time to build a next generation of this -- more features, more things to learn.  We hope you enjoy this.

-Larry

Answer 2

User-1335965041 posted

I am using C#

Answer 3

User-1760427068 posted

Instructions and comments will be placed here.  Items will be separated by lines of asterisks.  Let me know if you have any questions or comments about this format -- always looking for improvement.

I'm assuming you can create tables and populate them if I give SQL statements.  If not I can provide a program to help with that.

(you may notice this before all the items have been posted..)

****************************

1. Introduction / Manifest / SQL Statements to create and populate tables
2. CS_ECommerce1.aspx / CS_ECommerce1.aspx.cs
3. CS_CustomerLoginRegister.aspx / CS_CustomerLoginRegister.aspx.cs
4. Comments / Explanations / Plans for improvements, etc.

*****************************

CREATE TABLE Paints
(id smallint IDENTITY(1,1) PRIMARY KEY,
ProductCode nvarchar(10) NOT NULL,
PaintName nvarchar(50) NOT NULL,
Price money NOT NULL)

CREATE TABLE PaintSales
(id smallint IDENTITY(1,1) PRIMARY KEY,
Quantity int NOT NULL,
ProductCode nvarchar(10) NOT NULL,
CustomerID nvarchar(10) NOT NULL,
DateTime datetime NOT NULL)

CREATE TABLE PaintCustomers
(id smallint IDENTITY(1,1) PRIMARY KEY,
CustomerID nvarchar(20) NOT NULL,
CustomerName nvarchar(50) NOT NULL,
CustomerPassword nvarchar(47) NOT NULL,
CustomerEmail nvarchar(50) NOT NULL)

INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic11','Acrylic Exterior White Paint', '20')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic12','Acrylic Exterior Pastel Base', '19')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic13','Acrylic Exterior Midtone Base', '18')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic14','Acrylic Exterior Deep Base', '17')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic15','Acrylic Exterior Accent Base', '16')

INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic21','Acrylic Interior White Paint', '18')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic22','Acrylic Interior Pastel Base', '17')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic23','Acrylic Interior Midtone Base', '16')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic24','Acrylic Interior Deep Base', '15')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic25','Acrylic Interior Accent Base', '14')

INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic31','Acrylic Ceiling White Paint', '16')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic32','Acrylic Ceiling Pastel Base', '15.5')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic33','Acrylic Ceiling Midtone Base', '15')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic34','Acrylic Ceiling Deep Base', '14.5')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('acrylic35','Acrylic Ceiling Accent Base', '14')

INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('alkyd11','Alkyd Kitchen & Bath White Paint', '21')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('alkyd12','Alkyd Kitchen & Bath Pastel Base', '20')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('alkyd13','Alkyd Kitchen & Bath Midtone Base', '19')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('alkyd14','Alkyd Kitchen & Bath Deep Base', '18')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('alkyd15','Alkyd Kitchen & Bath Accent Base', '17')

INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('finish01','High Sheen Clear Furniture Finish', '16')
INSERT INTO Paints (ProductCode, PaintName, Price) VALUES ('finish02','Gloss White Furniture Finish', '15')

Answer 4

User-1760427068 posted

<%@ Page Language="C#" AutoEventWireup="true" Debug="true" CodeFile="CS_CustomerLoginRegister.aspx.cs" Inherits="CS_CustomerLogin" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Customer Login</title>
</head>
<body>
    <form id="form1" runat="server">
   <b>LOGIN:</b><br />
   CustomerID: <asp:TextBox ID="LoginCustomerIDTextBox" runat="server"></asp:TextBox> <span id="customerid" runat="server" /> <br /> 
   Password: <asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password"> </asp:TextBox> <span id="loginpassword" runat="server" />
    
   <br /><br /> 
    
    <asp:Button ID="LoginButton" runat="server" Text="Login" 
        onclick="LoginButton_Click" />
        
   <br /><br />

   <div id="helplines" runat="server" />
   
   <br />
   or
   <br /><br /><br />
   
   <b>REGISTER:</b><br />
   Your Name: <asp:TextBox ID="YourNameTextBox" runat="server"></asp:TextBox> <span id="yourname" runat="server" /> <br /> 
   Your Email: <asp:TextBox ID="YourEmailTextBox" runat="server"></asp:TextBox> <span id="youremail" runat="server" /> <br /> 
   Choose a CustomerID: <asp:TextBox ID="RegisterCustomerIDTextBox" runat="server"></asp:TextBox> <span id="registercustomerid" runat="server" /> <br /> 
   Choose Password: <asp:TextBox ID="ChoosePasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <span id="chosenpassword" runat="server" />
   <br />
   Retype Password: <asp:TextBox ID="RetypePasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <span id="retypedpassword" runat="server" />  
 
    <br /><br /> 
    
    <asp:Button ID="RegisterButton" runat="server" Text="Register" 
        onclick="RegisterButton_Click" />  

    </form>
</body>
</html>

 

********************************************************************************

 

using System;
using System.Text; // This was needed to make ASCIIEncoding function
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Security.Cryptography; // This was needed to make everything related to MD5 function

public partial class CS_CustomerLogin : System.Web.UI.Page
{
    string tempString;
    string passwordhash;

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void RegisterButton_Click(object sender, EventArgs e)
    {
        if (YourNameTextBox.Text == "")
        {
            yourname.InnerHtml = "(5) Please type in your name...";
        }
        else
        {
            if (YourEmailTextBox.Text == "")
            {
                youremail.InnerHtml = "(6) Please type in your email...";
            }
            else
            {
                if (RegisterCustomerIDTextBox.Text == "")
                {
                    registercustomerid.InnerHtml = "(7) Please type a customerID...";
                }
                else
                {
                    if (ChoosePasswordTextBox.Text == "")
                    {
                        chosenpassword.InnerHtml = "(8) Please type your password...";
                    }
                    else
                    {
                        if (RetypePasswordTextBox.Text == "")
                        {
                            retypedpassword.InnerHtml = "(9) Please type your password again to confirm it...";
                        }
                        else
                        {
                            if (ChoosePasswordTextBox.Text != RetypePasswordTextBox.Text)
                            {
                                retypedpassword.InnerHtml = "(10) The two passwords do not match...";
                                RetypePasswordTextBox.Text = "";
                            }
                            else
                            {

                                string encodedpassword = EncodePassword(ChoosePasswordTextBox.Text);

                                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                                conn.Open();
                                tempString = "INSERT INTO PaintCustomers (CustomerID, CustomerName, CustomerPassword, CustomerEmail) VALUES ('";
                                tempString += RegisterCustomerIDTextBox.Text + "','";
                                tempString += YourNameTextBox.Text + "','";
                                tempString += encodedpassword + "','";
                                tempString += YourEmailTextBox.Text + "')";
                                SqlCommand cmd = new SqlCommand(tempString, conn);
                                cmd.ExecuteNonQuery();
                                Session["CustomerName"] = YourNameTextBox.Text;
                                Response.Redirect("CS_ECommerce1.aspx");

                            }
                        }
                    }
                }
            }
        }
    }



    protected void LoginButton_Click(object sender, EventArgs e)
    {
        if (LoginCustomerIDTextBox.Text == "")
        {
            customerid.InnerHtml = "(1) Please type your Customer ID...";
        }
        else
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            conn.Open();
            tempString = "SELECT CustomerName FROM PaintCustomers WHERE CustomerID ='" + LoginCustomerIDTextBox.Text + "'";
            SqlCommand cmd = new SqlCommand(tempString, conn);
            Session["CustomerName"] = Convert.ToString(cmd.ExecuteScalar());
            conn.Close();
            if (Session["CustomerName"] == "")
            {
                customerid.InnerHtml = "(2) Incorrect CustomerID...";
            }
            else
            {
                if (PasswordTextBox.Text == "")
                {
                    loginpassword.InnerHtml = "(3) Please type in your password...";
                }
                else
                {
                    conn.Open();
                    tempString = "SELECT CustomerPassword FROM PaintCustomers WHERE CustomerID ='" + LoginCustomerIDTextBox.Text + "'";
                    cmd = new SqlCommand(tempString, conn);
                    string hashedcustomerpassword = Convert.ToString(cmd.ExecuteScalar());
                    string passwordattempt = EncodePassword(PasswordTextBox.Text);
                    if (hashedcustomerpassword != passwordattempt)
                    {
                        loginpassword.InnerHtml = "(4) Incorrect password.";
                    }
                    else  //correct login, now we need to confirm their password
                    {
                        Session["CustomerID"] = LoginCustomerIDTextBox.Text;
                        Response.Redirect("CS_ECommerce1.aspx");
                    }
                }
            }
        } 
    }

    public string EncodePassword(string originalPassword)
    {
        Byte[] originalBytes;
        Byte[] encodedBytes;
        MD5 md5;
        md5 = new MD5CryptoServiceProvider();
        originalBytes = ASCIIEncoding.Default.GetBytes(originalPassword);
        encodedBytes = md5.ComputeHash(originalBytes);
        return BitConverter.ToString(encodedBytes);
    }
}

 

Answer 5

User-1760427068 posted

 

<%@ Page Language="C#" AutoEventWireup="true" Debug="true" CodeFile="CS_ECommerce1.aspx.cs" Inherits="CS_ECommerce1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

                    <br /><br />
                    <div id="welcomeline" runat="server" />
                    <br /><br />
    
                    <asp:PlaceHolder runat="server" ID="mainplaceholder"></asp:PlaceHolder>
                    <br /><br />
                    
                    <asp:Button runat="server" ID="c_button" Text="Send Me" />
                    
                    <br /><br />
                    
                    <div id="thelines" runat="server" />

    </form>
</body>
</html>

 

*********************************************************************

 

using System;
using System.Data; // This was added to make CommandType function
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient; // This was added to make SqlConnection function
using System.Configuration; // This was added to make ConfigurationManager function 
using System.Text; // This was added to make StringBuilder function


public partial class CS_ECommerce1 : System.Web.UI.Page
{
    int count; // used to see how many products (and thus number of textboxes)
               // the array is hard-coded to 50; 
               // catch-22 we can't "see" the tables yet, I tried to connect--didn't work
    string[] productcode = new string[50];
    string[] paintname = new string[50];
    double[] price = new double[50];

    private TextBox[] m_dynamicTextBoxes;

    int loggedin = 0; // loggedin = 1 indicates they are logged in

    protected override void OnInit(EventArgs e)
    {
        StringBuilder csm = new StringBuilder();

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string sqlcommand = "SELECT COUNT(id) FROM Paints";
        SqlCommand cmd = new SqlCommand(sqlcommand, conn);

        conn.Open();
        count = Convert.ToInt32(cmd.ExecuteScalar());
        //everything from here through the reader loop builds an array of data from the table
        //for code, name, cost
        cmd.CommandText = "SELECT * FROM Paints";
        cmd.CommandType = CommandType.Text;
        SqlDataReader reader = cmd.ExecuteReader();
        int j = 0;
        while (reader.Read())
        {
            productcode[j] = reader["productcode"].ToString().Trim();
            paintname[j] = reader["paintname"].ToString().Trim();
            price[j] = Convert.ToDouble(reader["price"]);
            j += 1;
        }
        reader.Close();
        conn.Close();

        base.OnInit(e);
        c_button.Click += new EventHandler(ButtonClick);
        m_dynamicTextBoxes = new TextBox[count];
        for (int i = 0; i < count; i++)
        {
            PlaceHolder holder = new PlaceHolder();

            Label autoLabel = new Label();
            autoLabel.Text = paintname[i] + ": ";
            holder.Controls.Add(autoLabel);

            TextBox textBox = new TextBox();
            textBox.ID = "textBox" + i.ToString();
            textBox.Width = 50;
            holder.Controls.Add(textBox);

            holder.Controls.Add(new LiteralControl("&lt;br />"));

            m_dynamicTextBoxes[i] = textBox;

            mainplaceholder.Controls.Add(holder);
        }

    }

        protected void Page_Load(object sender, EventArgs e)
    {
            if (Session["CustomerID"] != "")
            {
                loggedin = 1;
                welcomeline.InnerHtml = "Welcome, " + Session["CustomerName"] + "!";
            }
            else
            {
                Response.Redirect("CS_CustomerLogin.aspx");
            }
    }

    protected void ButtonClick(object sender, EventArgs e)
    {
        //the primary key of StarfireTransactions, transid, must be determined now before we can do anything else
        //we will insert the name "qwertyuiop" into the next record and then ask the table for the transid of 
        //the record with the name "qwertyuiop"

        DateTime Now = DateTime.Now;

        StringBuilder csm = new StringBuilder();

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        conn.Open();

        int j = 0;
        int quantity;
        foreach (TextBox textBox in m_dynamicTextBoxes)
        {
            if (textBox.Text != "") // we're assuming if they type anything they will correctly type an integer--else it will crash :-(
            {
                quantity = Convert.ToInt32(textBox.Text);
                csm.Append(" INSERT INTO PaintSales (Quantity, ProductCode, CustomerID, Datetime) VALUES ('");
                csm.Append(quantity + "', '");
                csm.Append(productcode[j] + "', '");
                csm.Append(Session["CustomerID"] + "', '");
                csm.Append(Now + "') ");
    
            }
            j += 1;
        }
        SqlCommand cmd = new SqlCommand(csm.ToString(), conn);
        cmd.ExecuteNonQuery();
        conn.Close();
    }
}

 

Answer 6

User-1760427068 posted

It is hoped that both beginner and intermediate level programmers can copy the files and put it to work, and then go back and see new things in the code.

Our project here will be "third generation".  Generation One provided dynamic controls that worked.  Generation Two give One some simplifications and added cryptography for the login and register features.  It's up to you to decide what Generation Three might add to this. (one possibility -- replacing the arrays with ArrayList controls)

Code features to look for include:

• The original premise was that a more complexed program would take customer choices (like when you drill down: electronics --> computers --> hard drives --> internal) and then there would be an unknown number of products to display.
• We had an assumption (built in: string[] productcode = new string[50];) that there would always be less than 50 entries.  Three arrays hold information collected when the dynamic controls are built {{forward thinking- ArrayLists could do this}}

• private TextBox[] m_dynamicTextBoxes; is declared before the subroutines

• Notice that there is a placeholder on the aspx page

• Notice that when the dynamic controls are prepared, a placeholder with the name 'holder' is created dynamically for each iteration (an iteration needs to build a label and textbox -- we might have it show a picture and a price when upgrading the program)

• Notice that we .Add things to 'holder' and at the very end we .Add to mainplaceholder

 

Answer 7

User-1335965041 posted

I think i am missing a .linq assembly reference

Is there something i should download for this from the asp.net website?

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0234: The type or namespace name 'Linq' does not exist in the namespace 'System' (are you missing an assembly reference?)

Source Error:

Line 2: using System.Text; // This was needed to make ASCIIEncoding function Line 3: using System.Collections.Generic; Line 4: using System.Linq; Line 5: using System.Web; Line 6: using System.Web.UI;

Source File: c:\ASP\DynamicControls\CustomerLoginRegister.aspx.cs    Line: 4

Answer 8

User-1760427068 posted

This might be an easy one:

I got that error when I put pages, designed to be ASP.NET 3.5 on my GoDaddy account which was still configured at ASP.NET 2.0.

The remedy was to to comment out Line 4.  

// using System.Linq;

 

I commented it out rather than delete it in case later I moved the file to a different system or if my server upgraded.

Try this and let me know what happens.

-Larry

Answer 9

User-1335965041 posted

Great, that was it. (Thanks)

On the CS_ECommerce1.aspx page

The format is like this showing the closed end of the tags

What have I done wrong?


Welcome, 1!

Acrylic Exterior White Paint: <INPUT id=textBox0 style="WIDTH: 50px" name=textBox0><br />Acrylic Exterior Pastel Base: <INPUT id=textBox1 style="WIDTH: 50px" name=textBox1><br />Acrylic Exterior Midtone Base: <INPUT id=textBox2 style="WIDTH: 50px" name=textBox2><br />

and I get an error when I send.  (SqlCommand cmd = new SqlCommand(csm.ToString(), conn);

cmd.ExecuteNonQuery();

conn.Close();

 

Answer 10

User-1335965041 posted

Duh.

I have to put input data on the form.
my bad got in a hurry.

Can we add Options to the Paint Database such as colors sizes etc. and can they be linked to the Paint table and generated out under options such as

OptionID,OptionColumn,OptionCaption,DataType,etc,etc,ProductID

Such as OptionID,OptionColumn,OptionCaption,DataType,SortOrder,Enabled,etc,etc,ProductID

 

Answer 11

User-1760427068 posted

I'll open a new site and paste in the content to see if I can generate the same error.

-Larry

OK, this will fix it.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["CustomerID"] == null)
        {
            Response.Redirect("CS_CustomerLoginRegister.aspx");
        }
        else
        {
            loggedin = 1;
            welcomeline.InnerHtml += "Welcome, " + Session["CustomerName"] + "!";
        }
    }

 

Answer 12

User-1760427068 posted

I think I figured it out.   You uncovered a "glitch" or "bug" in the program in that you can get in without logging in.

If you go to the login/register page first and register, it should then give your login name instead of 'Welcome, 1" (or was it "Welcome, !").

I'll go to work on this.   I also will continue making the new demo web site that can be zipped (and downloaded or emailed).

-Larry

<br /> tag problem solved.

Answer 13

User-1760427068 posted

I went in and decided to replace the arrays with ArrayLists and that seemed to work OK (will continue testing).

Also, I figured out what went wrong with those br tags.  It turns out that sometimes when I post code, it changes 'less than symbol', part of the tag, to the four characters that construct it

ampersand l t semicolon

When this was copied over it copied as the four characters so that the code no longer saw a tag.  By replacing those four characters with a less than sign, the tag became a tag and that fixed the second error that you saw.

I'm going to come back to this line in a few minutes and do planning for the next stage, based on your last post.  In what time zone do you live, so I can compare your time to my time.

GOALS

1. Something happens after putting in the order -- right now it just stays on the same page (do you have a gmail account? if yes we could work email into this)
2. Fix <br /> thing - done
3. Add DropDownList control - done
4. Add RadioButtonList control - done
5. modify table to include more fields (quantity, price, color, size) - pending
6. create new records to put into products table -- pending
7. Related to 1-- make it possible to go to confirmation page where customer reviews their order and then processes it

 

-Larry

Answer 14

User-1760427068 posted

Can we add Options to the Paint Database such as colors sizes etc. and can they be linked to the Paint table and generated out under options such as

OptionID,OptionColumn,OptionCaption,DataType,etc,etc,ProductID

Such as OptionID,OptionColumn,OptionCaption,DataType,SortOrder,Enabled,etc,etc,ProductID

Yes, we can!  I'm excited about it.  I had wanted to upgrade things following the paint analogy (see my web site www.wemakebetterpaint.com ) and I see two opportunities.

We can start with color and sizes and descriptions.  I'd like to try a dropdownmenu with color and a radiobuttonlist with size (quart, gallon or 5-gallon).

I'm thinking we would stay with just textboxes and dropdownmenus on a commercial version of this, but I'd like to be able to say I've done it using a radiobuttonlist.

-Larry

Answer 15

User-1335965041 posted
Sounds great I'm on central time [e-mail] Looks like you made a lot of progress Loyd

Answer 16

User-1760427068 posted

Question:  Should I add CSS in this example, or would you rather I wait for the final version? 

If it doesn't matter, I'll build it in now.  I see you have a gmail.  I'll build in a reply page to the example.  One IMPORTANT thing to consider.

If you decide to post an example, the page with the authentication INCLUDES YOUR PASSWORD so you want to make certain that prior to posting, you change this to something other than your password (I write password).  I copy the code and paste it to a Word document and change it there, and visually verify.  I then copy from there to the forum and check while it is within the window here.  I then glance a third time immediately after posting.  I WANT THIS MEMORY TO STICK for you or anyone else who comes here later.  I don't normally use uppercase. ; - )

-Larry

Answer 17

User-1760427068 posted

Such as OptionID,OptionColumn,OptionCaption,DataType,SortOrder,Enabled,etc,etc,ProductID

I just noticed this part--focus on the word SortOrder.  If I understand, possibly this would mean something such that the items could be reordered based on price like if you go to a web site like Circuit City or Best Buy?  Were you thinking this?  This post could turn into a few pages but that doesn't bother me.  Since a person coming to the thread can jump to the end, I'm thinking I could write the last post and make it have everything in it and then I would put a caption in my first reply "Anyone coming here to get the code, go to the end and find it there."

-Larry

Answer 18

User-1335965041 posted

Are you going to use an external css sheet? And sort order was for sequencing of the controls and the ability to turn on or off.
Is it possible to create separate tables and have a Category table with CategoryID, Category(for Paints,Hardware,Misc)|CatTypeID,CatType(for interior,exterior) | ProductTypeID,ProductType(alkaloid,Laquers,latex) and then ProductID, Products(What's already there) and finaly and option table with all the options. and should these by child tables or just combo boxes querying other tables

 I'm in the window covering business and we use alot of paint in our business to paint shutters

Loyd

Answer 19

User-1760427068 posted

OK, maybe we make the model to have three categories as you say, and two or three products for each category.

Because there has to be compatibility between a table holding products and the table that holds the records for sales, I'm thinking we'd want to have  a product table and there is a field in it that specifies paints, hardware, misc or whatever else is added in the future.  If we go this route we wouldn't have to build a new table or add tables, we'd just put in more records and have one more word for the category (example: we add a new category: garden tools).  The query that builds the temporary table would sort through the table and only accept records with a category match.

We might go back and forth in discussion to get this nailed down good.

For simplicity I'm running with a few colors.  I'm thinking for most of these things we can get by with two or three just to prove that it works.

For your work, would a one gallon paint container have a different code than a five gallon paint container even though they held the same paint.  I'm hoping you  say yes because then there would be a different record for the one gallon and the five gallon and we would only need to worry about one price field in the Product Table in the database.

-Larry

Answer 20

User-1335965041 posted

Yes absolutely,

Can the column containing the dynamic controls be mixed with different type of control information?

Such as   record1  textbox   record2 dropdown  record3 yes/no record4 radio button?

Answer 21

User-1760427068 posted

Yes. The demo I tested last night included a dropdownlist and a radiobuttonlist.  The code looks as follows:

 

    protected override void OnInit(EventArgs e)
    {
        StringBuilder csm = new StringBuilder();

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string sqlcommand = "SELECT COUNT(id) FROM Paints";
        SqlCommand cmd = new SqlCommand(sqlcommand, conn);

        conn.Open();
        count = Convert.ToInt32(cmd.ExecuteScalar());
        //everything from here through the reader loop builds an array of data from the table
        //for code, name, cost
        cmd.CommandText = "SELECT * FROM Paints";
        cmd.CommandType = CommandType.Text;
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            productcodeArrayList.Add(reader["productcode"].ToString().Trim());
            paintnameArrayList.Add(reader["paintname"].ToString().Trim());
            priceArrayList.Add(Convert.ToDouble(reader["price"]));
        }
        reader.Close();
        conn.Close();

        base.OnInit(e);
        c_button.Click += new EventHandler(ButtonClick);
        m_dynamicTextBoxes = new TextBox[count];
        for (int i = 0; i < count; i++)
        {
            PlaceHolder holder = new PlaceHolder();

            Label autoLabel = new Label();
            autoLabel.Text = paintnameArrayList[i] + ": ";
            holder.Controls.Add(autoLabel);

            TextBox textBox = new TextBox();
            textBox.ID = "textBox" + i.ToString();
            textBox.Width = 50;
            holder.Controls.Add(textBox);

            holder.Controls.Add(new LiteralControl("   Color: "));

            DropDownList dropDownList = new DropDownList();
            dropDownList.ID = "dropDownList" + i.ToString();
            dropDownList.Items.Add("Cyan");
            dropDownList.Items.Add("Yellow");
            dropDownList.Items.Add("Magenta");
            dropDownList.Items.Add("Black");

            holder.Controls.Add(dropDownList);


            PlaceHolder rblHolder = new PlaceHolder();

            rblHolder.Controls.Add(new LiteralControl("&lt;div >   Size: </div>"));

            RadioButtonList radioButtonList = new RadioButtonList();
            radioButtonList.ID = "radioButtonList" + i.ToString();
            radioButtonList.Items.Add("Quart");
            radioButtonList.Items.Add("Gallon");
            radioButtonList.Items.Add("5Gallon");


            rblHolder.Controls.Add(radioButtonList);

            holder.Controls.Add(rblHolder);

            holder.Controls.Add(new LiteralControl("&lt;br />"));

            m_dynamicTextBoxes[i] = textBox;

            mainplaceholder.Controls.Add(holder);
        }

    }

 protected override void OnInit(EventArgs e)

{

StringBuilder csm = new StringBuilder();

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

string sqlcommand = "SELECT COUNT(id) FROM Paints";SqlCommand cmd = new SqlCommand(sqlcommand, conn);

conn.Open();

count = Convert.ToInt32(cmd.ExecuteScalar());

//everything from here through the reader loop builds an array of data from the table

//for code, name, cost

cmd.CommandText = "SELECT * FROM Paints";

cmd.CommandType = CommandType.Text;

SqlDataReader reader = cmd.ExecuteReader();while (reader.Read())

{

productcodeArrayList.Add(reader["productcode"].ToString().Trim());

paintnameArrayList.Add(reader["paintname"].ToString().Trim());priceArrayList.Add(Convert.ToDouble(reader["price"]));

}

reader.Close();

conn.Close();

base.OnInit(e);

c_button.Click += new EventHandler(ButtonClick);

m_dynamicTextBoxes = new TextBox[count];for (int i = 0; i < count; i++)

{

PlaceHolder holder = new PlaceHolder();

Label autoLabel = new Label();autoLabel.Text = paintnameArrayList[i] + ": ";

holder.Controls.Add(autoLabel);

TextBox textBox = new TextBox();

textBox.ID = "textBox" + i.ToString();

textBox.Width = 50;

holder.Controls.Add(textBox);

holder.Controls.Add(new LiteralControl(" &nbsp; Color: "));

DropDownList dropDownList = new DropDownList();

dropDownList.ID = "dropDownList" + i.ToString();

dropDownList.Items.Add("Cyan");

dropDownList.Items.Add("Yellow");

dropDownList.Items.Add("Magenta");

dropDownList.Items.Add("Black");

holder.Controls.Add(dropDownList);

 

PlaceHolder rblHolder = new PlaceHolder();

rblHolder.Controls.Add(new LiteralControl("<div > &nbsp; Size: </div>"));

RadioButtonList radioButtonList = new RadioButtonList();

radioButtonList.ID = "radioButtonList" + i.ToString();

radioButtonList.Items.Add("Quart");

radioButtonList.Items.Add("Gallon");

radioButtonList.Items.Add("5Gallon");

 

rblHolder.Controls.Add(radioButtonList);

holder.Controls.Add(rblHolder);

holder.Controls.Add(new LiteralControl("<br />"));

m_dynamicTextBoxes[i] = textBox;

mainplaceholder.Controls.Add(holder);

}

}

You can see how similar the code is to code we've used previously.

I'm going to assume that we have paint in the sizes quart, gallon and five gallon and that each of the three has a different product code.

If this is not the case we can modify it so that each order has a productcode and a subproductcode (both strings).  For paint, subproductcode could be size.  For something else it could be null, or if there was a need for a subproductcode for a different reason, we would have it available.

I'm thinking of adding a field, orderstatus, so that an order might have a status of placed, filling, or shipped.  I realize we don't want to pile too much on just yet, but this would give us some options for building display pages (dropdown decides whether we see orders placed or orders filling or orders shipped).

Summary: we are going to have new tables: Products, Customers, Sales

I will post SQL statements for these, and follow that with code for pages.

-Larry

Answer 22

User-1760427068 posted

 

CREATE TABLE Products
(id smallint IDENTITY(1,1) PRIMARY KEY,
Category nvarchar(15) NOT NULL,
SupplierCode nvarchar(10),
ProductCode nvarchar(10) NOT NULL,
ProductName nvarchar(50) NOT NULL,
ProductPrice money NOT NULL,
ProductDescription nvarchar(200) NOT NULL)

CREATE TABLE Sales
(id smallint IDENTITY(1,1) PRIMARY KEY,
Quantity int NOT NULL,
ProductCode nvarchar(10) NOT NULL,
ProductSubCode nvarchar(10),
Price money NOT NULL,
Color nvarchar(10) NOT NULL,
CustomerID nvarchar(10) NOT NULL,
OrderStatus nvarchar(1) NOT NULL DEFAULT 'R',
DateTime datetime NOT NULL)

CREATE TABLE Customers
(id smallint IDENTITY(1,1) PRIMARY KEY,
CustomerID nvarchar(20) NOT NULL,
CustomerFirstName nvarchar(30) NOT NULL,
CustomerLastName nvarchar(30) NOT NULL,
CustomerPassword nvarchar(47) NOT NULL,
CustomerEmail nvarchar(50) NOT NULL)


INSERT INTO Products (ProductCode, Category, ProductName, ProductPrice, ProductDescription) VALUES ('acrylic11', 'Paint', 'Gallon Acrylic Exterior White Paint', 5, 'Excellent hide paint')
INSERT INTO Products (ProductCode, Category, ProductName, ProductPrice, ProductDescription) VALUES ('acrylic12', 'Paint', 'Gallon Acrylic Exterior Pastel Base', 4.75, 'Beautiful light colors with more hide')
INSERT INTO Products (ProductCode, Category, ProductName, ProductPrice, ProductDescription) VALUES ('acrylic13', 'Paint', 'Gallon Acrylic Exterior Midtone Base', 4.5, 'Beautiful light colors with more color strength' )

INSERT INTO Products (ProductCode, Category, ProductName, ProductPrice, ProductDescription) VALUES ('utensil1', 'Garden', 'Rake', 5, 'Removes leaves and accentuates the green beauty of your lawn')
INSERT INTO Products (ProductCode, Category, ProductName, ProductPrice, ProductDescription) VALUES ('utensil2', 'Garden', 'Hoe', 4.75, 'Gives your plants a chance to breathe')
INSERT INTO Products (ProductCode, Category, ProductName, ProductPrice, ProductDescription) VALUES ('utensil3', 'Garden', 'Edger', 4.5, 'Enhances the beauty of your sidewalk' )

 

****************Our Notes*******************************

[Idea] 

I just realized that ProductSubCode doesn't work.  The logic is that a product code by itself must uniquely identify the item which was sold.  Now there might be another code (an optional code) like Supplier Code.

We might buy 100123 from a supplier and the supplier sells it in three sizes as 100123 but our product code is 10012301, 10012302, 10012303 so we have unique product codes.  Possibly you are already doing something like this?

At first it might seem that having ProductPrice in the Sales table is a violation of Normalization but there is a business logic reason for this: we want to know the price of something at the time of the point of sale.  If we relied on an innerjoin to pull the price from the table listing the products the invoice would change every time that the price of the product changed.  Both of us will be looking with an eagle eye for things like this as the collection of pages develops.

(where am I right now -- I'm working to get the value of the dynamic control from the control and to put it into a string and from there to get it into the table in the database.)  I'll write here when this is solved...

-Larry

Answer 23

User-1335965041 posted

I believe we could leave product code and productid alone and use sku as specific part#

as for the invoice price changing could we use a timestamp to qualify

any posting or an order history table and with an invoiced history table   

Loyd      

Answer 24

User-1760427068 posted

OK.

Is it safe to say that you would change your database tables each year? (example, in three weeks I'll close the database table for 2008 and start a new one).

If so, I'm thinking we can do what you said and have the the datetime stamp serve to confirm when the purchase was made.  We could use an ExecuteScalar to search through the price table starting at the time date of the timestamp and to go backwards in time (ORDER BY time DESC) and since it was an ExecuteScalar it would stop at the first date it found and ignore all others.  Of course we would test this to make sure it is working.

I am still banging my head on a wall about reading the contents of a dynamic dropdownlist--this will be a very sweet victory when I can tell you I got it (you'll hear me without the need for a telephone). 

-Larry

Answer 25

User-1335965041 posted

Where are the values for the dropdown coming from?

Are they coming from another table or are they static?

They may not be loading before the control is generated.

I've got a link to a website that sells blinds and shades, it is very robust to say the least
and has alot of product options.
What I can't tell is if they are static or dynamically generated. If Static alot of maintenance.
http://www.blinds.com/control/subCategory/categoryID,10?vall=19
Do you sell blinds @ your paint store?

Loyd

 

Answer 26

User-1760427068 posted

For Version II, they will be static -- I want the example to still be fairly simple so someone can follow it.

However, I also think we could have a table that does nothing but associate dropdownlink items with a department and a dropdown category (three field example)

• Paint, Color,  Red
• Paint, Color, Orange
• Paint, Color, Yellow

 It might be necessary to have it then loops such that the first loop is subdivided into departments and then nested within that is another loop based on type of products.

OK, the next two posts will have the two pages for Example II.  I've been really looking forward to reaching this point.

-Larry

Answer 27

User-1760427068 posted

 

CREATE TABLE Products
(id smallint IDENTITY(1,1) PRIMARY KEY,
Department nvarchar(15) NOT NULL,
ProductCode nvarchar(10) NOT NULL,
ProductName nvarchar(50) NOT NULL,
ProductPrice money NOT NULL,
ProductDescription nvarchar(200) NOT NULL)

CREATE TABLE Sales
(id smallint IDENTITY(1,1) PRIMARY KEY,
Quantity int NOT NULL,
ProductCode nvarchar(10) NOT NULL,
ProductPrice money NOT NULL,
CustomerID nvarchar(10) NOT NULL,
OrderStatus nvarchar(1) NOT NULL DEFAULT 'R',
Comments nvarchar(200),
DateTime datetime NOT NULL)

CREATE TABLE Customers
(id smallint IDENTITY(1,1) PRIMARY KEY,
CustomerID nvarchar(20) NOT NULL,
CustomerFirstName nvarchar(30) NOT NULL,
CustomerLastName nvarchar(30) NOT NULL,
CustomerPassword nvarchar(47) NOT NULL,
CustomerEmail nvarchar(50) NOT NULL)

INSERT INTO Products (ProductCode, Department, ProductName, ProductPrice, ProductDescription) VALUES ('acrylic11', 'Paint', 'Gallon Acrylic Exterior White Paint', 5, 'Excellent hide paint')
INSERT INTO Products (ProductCode, Department, ProductName, ProductPrice, ProductDescription) VALUES ('acrylic12', 'Paint', 'Gallon Acrylic Exterior Pastel Base', 4.75, 'Beautiful light colors with more hide')
INSERT INTO Products (ProductCode, Department, ProductName, ProductPrice, ProductDescription) VALUES ('acrylic13', 'Paint', 'Gallon Acrylic Exterior Midtone Base', 4.5, 'Beautiful light colors with more color strength' )

Answer 28

User-1760427068 posted

 

<%@ Page Language="C#" AutoEventWireup="true" Debug="true" CodeFile="Original_LoginRegister.aspx.cs" Inherits="Original_LoginRegister" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Customer Login</title>
</head>
<body>
    <form id="form1" runat="server">
   <b>LOGIN:</b><br />
   CustomerID: <asp:TextBox ID="LoginCustomerIDTextBox" runat="server"></asp:TextBox> <span id="customerid" runat="server" /> <br /> 
   Password: <asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password"> </asp:TextBox> <span id="loginpassword" runat="server" />
   <br /><br /> 
    
    <asp:Button ID="LoginButton" runat="server" Text="Login" 
        onclick="LoginButton_Click" />
        
   <br /><br />

   <div id="helplines" runat="server" />
   
   <br />
   or
   <br /><br /><br />
   
   <b>REGISTER:</b><br />
   Your First Name: <asp:TextBox ID="YourFirstNameTextBox" runat="server"></asp:TextBox> <span id="yourfirstname" runat="server" /> <br /> 
   Your Last Name: <asp:TextBox ID="YourLastNameTextBox" runat="server"></asp:TextBox> <span id="yourlastname" runat="server" /> <br /> 
   Your Email: <asp:TextBox ID="YourEmailTextBox" runat="server"></asp:TextBox> <span id="youremail" runat="server" /> <br /> 
   Choose a CustomerID: <asp:TextBox ID="RegisterCustomerIDTextBox" runat="server"></asp:TextBox> <span id="registercustomerid" runat="server" /> <br /> 
   Choose Password: <asp:TextBox ID="ChoosePasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <span id="chosenpassword" runat="server" />
   <br />
   Retype Password: <asp:TextBox ID="RetypePasswordTextBox" runat="server" TextMode="Password"></asp:TextBox> <span id="retypedpassword" runat="server" />  
 
    <br /><br /> 
    
    <asp:Button ID="RegisterButton" runat="server" Text="Register" 
        onclick="RegisterButton_Click" />  

    </form>
</body>
</html>

 *********************************************************

using System;
using System.Text; // This was needed to make ASCIIEncoding function
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Security.Cryptography; // This was needed to make everything related to MD5 function


public partial class Original_LoginRegister : System.Web.UI.Page
{
    string tempString;
    string passwordhash;

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void RegisterButton_Click(object sender, EventArgs e)
    {
        if (YourFirstNameTextBox.Text == "")
        {
            yourfirstname.InnerHtml = "Please type in your first name...";
        }
        else
        {
            if (YourLastNameTextBox.Text == "")
            {
                yourlastname.InnerHtml = "Please type in your last name...";
            }
            else
            {
                if (YourEmailTextBox.Text == "")
                {
                    youremail.InnerHtml = "Please type in your email...";
                }
                else
                {
                    if (RegisterCustomerIDTextBox.Text == "")
                    {
                        registercustomerid.InnerHtml = "Please type a customerID...";
                    }
                    else
                    {
                        if (ChoosePasswordTextBox.Text == "")
                        {
                            chosenpassword.InnerHtml = "Please type your password...";
                        }
                        else
                        {
                            if (RetypePasswordTextBox.Text == "")
                            {
                                retypedpassword.InnerHtml = "Please type your password again to confirm it...";
                            }
                            else
                            {
                                if (ChoosePasswordTextBox.Text != RetypePasswordTextBox.Text)
                                {
                                    retypedpassword.InnerHtml = "The two passwords do not match...";
                                    RetypePasswordTextBox.Text = "";
                                }
                                else
                                {
                                    string encodedpassword = EncodePassword(ChoosePasswordTextBox.Text);

                                    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                                    conn.Open();
                                    tempString = "INSERT INTO Customers (CustomerID, CustomerFirstName, CustomerLastName, CustomerPassword, CustomerEmail) VALUES ('";
                                    tempString += RegisterCustomerIDTextBox.Text + "','";
                                    tempString += YourFirstNameTextBox.Text + "','";
                                    tempString += YourLastNameTextBox.Text + "','";
                                    tempString += encodedpassword + "','";
                                    tempString += YourEmailTextBox.Text + "')";
                                    SqlCommand cmd = new SqlCommand(tempString, conn);
                                    cmd.ExecuteNonQuery();
                                    Session["CustomerFirstName"] = YourFirstNameTextBox.Text;
                                    Session["CustomerID"] = RegisterCustomerIDTextBox.Text;
                                    Response.Redirect("Original2.aspx");

                                   // helplines.InnerHtml = tempString;
                                }
                            }
                        }
                    }
                }
            }
        }
    }



    protected void LoginButton_Click(object sender, EventArgs e)
    {
        if (LoginCustomerIDTextBox.Text == "")
        {
            customerid.InnerHtml = "Please type your Customer ID...";
        }
        else
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            conn.Open();
            tempString = "SELECT CustomerID FROM Customers WHERE CustomerID ='" + LoginCustomerIDTextBox.Text + "'";
            SqlCommand cmd = new SqlCommand(tempString, conn);
            Session["CustomerID"] = Convert.ToString(cmd.ExecuteScalar());
            conn.Close();
            if (Session["CustomerID"] == "")
            {
                customerid.InnerHtml = "Incorrect CustomerID...";
            }
            else
            {
                if (PasswordTextBox.Text == "")
                {
                    loginpassword.InnerHtml = "Please type in your password...";
                }
                else
                {
                    conn.Open();
                    tempString = "SELECT CustomerPassword FROM Customers WHERE CustomerID ='" + LoginCustomerIDTextBox.Text + "'";
                    cmd = new SqlCommand(tempString, conn);
                    string hashedcustomerpassword = Convert.ToString(cmd.ExecuteScalar());
                    string passwordattempt = EncodePassword(PasswordTextBox.Text);
                    if (hashedcustomerpassword != passwordattempt)
                    {
                        loginpassword.InnerHtml = "Incorrect password.";
                    }
                    else   
                    {
                        tempString = "SELECT CustomerFirstName FROM Customers WHERE CustomerID ='" + LoginCustomerIDTextBox.Text + "'";
                        cmd = new SqlCommand(tempString, conn);
                        Session["CustomerFirstName"] = Convert.ToString(cmd.ExecuteScalar());
                        Response.Redirect("Original2.aspx");
                    }
                }
            }
        }
    }

    public string EncodePassword(string originalPassword)
    {
        Byte[] originalBytes;
        Byte[] encodedBytes;
        MD5 md5;
        md5 = new MD5CryptoServiceProvider();
        originalBytes = ASCIIEncoding.Default.GetBytes(originalPassword);
        encodedBytes = md5.ComputeHash(originalBytes);
        return BitConverter.ToString(encodedBytes);
    }
}

 

Answer 29

User-1760427068 posted

 

<%@ Page Language="C#" AutoEventWireup="true" Debug="true" CodeFile="Original2.aspx.cs" Inherits="Original2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

                    <br /><br />
                    <div id="welcomeline" runat="server" />
                    <br /><br />
    
                    <asp:PlaceHolder runat="server" ID="mainplaceholder"></asp:PlaceHolder>
                    <br /><br />
                    
                    <asp:Button runat="server" ID="c_button" Text="Send Order" />
                    
                    <br /><br />
                    
                    <div id="theorder" runat="server" />
    </form>
</body>
</html>

 *****************************************************************

using System;
using System.Data; // This was added to make CommandType function
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient; // This was added to make SqlConnection function
using System.Configuration; // This was added to make ConfigurationManager function 
using System.Text; // This was added to make StringBuilder function
using System.Collections; // This enabled ArrayList

public partial class Original2 : System.Web.UI.Page
{
    ArrayList productcodeArrayList = new ArrayList();
    ArrayList productnameArrayList = new ArrayList();
    ArrayList productpriceArrayList = new ArrayList();

    private TextBox[] m_dynamicTextBoxes;
    private DropDownList[] m_dynamicDropDownLists;

    int loggedin = 0; // loggedin = 1 indicates they are logged in

    protected override void OnInit(EventArgs e)
    {
        StringBuilder csm = new StringBuilder();
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string sqlcommand = "SELECT COUNT(id) FROM Paints";
        SqlCommand cmd = new SqlCommand(sqlcommand, conn);

        conn.Open();
        int count = Convert.ToInt32(cmd.ExecuteScalar());
        //everything from here through the reader loop builds an array of data from the table
        //for code, name, cost
        cmd.CommandText = "SELECT * FROM Paints";
        cmd.CommandType = CommandType.Text;
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            productcodeArrayList.Add(reader["ProductCode"].ToString().Trim());
            productnameArrayList.Add(reader["ProductName"].ToString().Trim());
            productpriceArrayList.Add(Convert.ToDouble(reader["ProductPrice"]));
        }
        reader.Close();
        conn.Close();

        base.OnInit(e);
        c_button.Click += new EventHandler(ButtonClick);
        m_dynamicTextBoxes = new TextBox[count];
        m_dynamicDropDownLists = new DropDownList[count];
        for (int i = 0; i < count; i++)
        {
            PlaceHolder holder = new PlaceHolder();

            Label autoLabel = new Label();
            autoLabel.Text = productnameArrayList[i] + ": ";
            holder.Controls.Add(autoLabel);

            holder.Controls.Add(new LiteralControl(" Quantity: "));

            TextBox textBox = new TextBox();
            textBox.ID = "textBox" + i.ToString();
            textBox.Width = 50;
            holder.Controls.Add(textBox);

            m_dynamicTextBoxes[i] = textBox;

            holder.Controls.Add(new LiteralControl(" Color: "));

            DropDownList dropDownList = new DropDownList();
            dropDownList.ID = "dropDownList" + i.ToString();
            dropDownList.Items.Add("Red");
            dropDownList.Items.Add("Orange");
            dropDownList.Items.Add("Yellow");
            holder.Controls.Add(dropDownList);

            m_dynamicDropDownLists[i] = dropDownList;

            holder.Controls.Add(new LiteralControl("&lt;br />"));

            mainplaceholder.Controls.Add(holder);
        }

    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["CustomerID"] != "")
        {
            loggedin = 1;
            welcomeline.InnerHtml = "Welcome, " + Session["CustomerFirstName"] + "!";
        }
        else
        {
            Response.Redirect("Original_LoginRegister.aspx");
        }
    }

    protected void ButtonClick(object sender, EventArgs e)
    {
        DateTime Now = DateTime.Now;
        StringBuilder csm = new StringBuilder();
        StringBuilder order = new StringBuilder();

        order.Append("Order by: " + Session["CustomerID"] + "&lt;br /><br />");

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        conn.Open();

        int j = 0;
        int quantity;
        string color;
        int number;
        string prenumber;
        string thecomments;

        foreach (TextBox textBox in m_dynamicTextBoxes)
        {
            thecomments = "";
            if (textBox.Text != "") 
            {
                quantity = Convert.ToInt32(textBox.Text);

                foreach (DropDownList dropDownList in m_dynamicDropDownLists)
                {
                    prenumber = Convert.ToString(dropDownList.ID);
                    number = Convert.ToInt32(prenumber.Substring(12));
                    if (number == j)
                    {
                        color = Convert.ToString(dropDownList.SelectedValue);
                        thecomments += "Tint to color " + color + "; ";
                    }
                }
                
                csm.Append(" INSERT INTO Sales (Quantity, ProductCode, ProductPrice, CustomerID, OrderStatus, Comments, Datetime) VALUES ('");
                csm.Append(quantity + "', '");
                csm.Append(productcodeArrayList[j] + "', '");
                csm.Append(productpriceArrayList[j] + "', '");
                csm.Append(Session["CustomerID"] + "', '");
                csm.Append("R', '");

                order.Append(quantity + " of " + productcodeArrayList[j]);
                order.Append("   price: " + productpriceArrayList[j]);
                order.Append("&lt;br />");

                if (thecomments == "")
                {
                    csm.Append("No Comments','");
                    order.Append("No Comments.");
                }
                else
                {
                    csm.Append(thecomments + "','");
                    order.Append("Comments: " + thecomments + "&lt;br /><br />");
                }

                csm.Append(Now + "') ");
            }
            j += 1;
        
        }

        SqlCommand cmd = new SqlCommand(csm.ToString(), conn);
        cmd.ExecuteNonQuery();
        conn.Close();

        theorder.InnerHtml = order.ToString();


        // if you need to debug, comment out the ExecuteNonQuery and uncomment the InnerHtml
        // thelines.InnerHtml = csm.ToString();  
    }

    // http://forums.asp.net/t/1357688.aspx (this thread might interest us)
}

 

Answer 30

User-1760427068 posted

The primary objective of Example II is to show the use of more than one type of dynamically generated control on a page.  I could not find a pure example example of this elsewhere.

Note: the forum takes a br tag and changes < to &lt; and you will need to repair a few of these when you take the code.  I am sorry that this happens.

 There are several things to look for with regard to the dynamic controls.  First, we have a structure with the word private

private TextBox[] m_dynamicTextBoxes;
private DropDownList[] m_dynamicDropDownLists;

 These are used later as

m_dynamicTextBoxes = new TextBox[count];
m_dynamicDropDownLists = new DropDownList[count];

 They collect information when the dynamic controls are defined:

TextBox textBox = new TextBox();
textBox.ID = "textBox" + i.ToString();
textBox.Width = 50;
holder.Controls.Add(textBox);

m_dynamicTextBoxes[i] = textBox;


DropDownList dropDownList = new DropDownList();
dropDownList.ID = "dropDownList" + i.ToString();
dropDownList.Items.Add("Red");
dropDownList.Items.Add("Orange");
dropDownList.Items.Add("Yellow");
holder.Controls.Add(dropDownList);

m_dynamicDropDownLists[i] = dropDownList;

 And finally in the code there is the point where the program loops through the possible controls to get information.  In our example the first objective is to loop through the textboxes that collect quantity values and check for information.  If a textbox for quantity is blank then there is no reason to do anything. 

Within the this loop there is a second loop that will go through all the dropdownlists.  It only wants to find the dropdownlist with an index matching the counter j (this assumes that we have a program like this one where dropDownList[3] corresponds to textBox[3], dropDownList[4] corresponds to textBox[4], etc.

It does this by taking the ID of the dropdownlist (example: dropDownList3) and removing the first twelve characters leaving a number (example: 3) and then converting this to an integer.

With regard to the StringBuilder comments.  Right now these are just put into a div at the bottom.  However, the next generation will have these emailed to you and the customer.

-Larry

 

Answer 31

User-1760427068 posted

Work has begun that will result in our having price as as time dependent variable.  The system will know when price changes occured.  SQL code as of 12-7-8 is

 

CREATE TABLE Prices
(id smallint IDENTITY(1,1) PRIMARY KEY,
ProductCode nvarchar(10) NOT NULL,
price money NOT NULL,
pricedate NOT NULL)

INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20, '01-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20.5, '03-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20.3, '07-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20.4, '09-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20.7, '11-01-08')

INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19, '01-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19.5, '03-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19.3, '07-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19.4, '09-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19.7, '11-01-08')

INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18, '01-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18.5, '03-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18.3, '07-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18.4, '09-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18.7, '11-01-08')

 

Answer 32

User-1335965041 posted

left out data type for pricedate above

 This should fix it

CREATE TABLE Prices
(id smallint IDENTITY(1,1) PRIMARY KEY,
ProductCode nvarchar(10) NOT NULL,
price money NOT NULL,
pricedate smalldatetime NOT NULL)

INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20, '01-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20.5, '03-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20.3, '07-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20.4, '09-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic11', 20.7, '11-01-08')

INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19, '01-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19.5, '03-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19.3, '07-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19.4, '09-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic12', 19.7, '11-01-08')

INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18, '01-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18.5, '03-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18.3, '07-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18.4, '09-01-08')
INSERT INTO Prices (ProductCode, price, pricedate) VALUES ('acrylic13', 18.7, '11-01-08')

Answer 33

User-1760427068 posted

This question came up in a previous discussion post. The code below demonstrates a system where a table keeps track of the prices of products as a function of date, thus product, price and date in each record.

The idea is if you record the time at which the purchase was made, you can go to this table and start looking at the point corresponding to this time (or just under it actually) and the start going backwards until you find the product (at this point it is the latest price just prior to the time of the point of sale).

Hopefully this is intuitive when you see the SQL command. 

Please find the SQL statements to create and populate the table, followed by the complete aspx and aspx.cs pages.

************************************************************

 

CREATE TABLE ProductPriceByDate
(id smallint IDENTITY(1,1) PRIMARY KEY,
Product nvarchar(20) NOT NULL,
Price money NOT NULL,
Date datetime NOT NULL)

INSERT INTO ProductPriceByDate (Product, Date, Price) VALUES ('Acrylic Paint', '01-01-2008', 20)
INSERT INTO ProductPriceByDate (Product, Date, Price) VALUES ('Alkyd Paint', '02-02-2008', 21)
INSERT INTO ProductPriceByDate (Product, Date, Price) VALUES ('Acrylic Paint', '03-03-2008', 24)
INSERT INTO ProductPriceByDate (Product, Date, Price) VALUES ('Alkyd Paint', '04-04-2008', 25)
INSERT INTO ProductPriceByDate (Product, Date, Price) VALUES ('Acrylic Paint', '05-05-2008', 26)
INSERT INTO ProductPriceByDate (Product, Date, Price) VALUES ('Alkyd Paint', '06-06-2008', 27)
INSERT INTO ProductPriceByDate (Product, Date, Price) VALUES ('Acrylic Paint', '07-07-2008', 19)
INSERT INTO ProductPriceByDate (Product, Date, Price) VALUES ('Alkyd Paint', '08-08-2008', 20)

 *************************

<%@ Page Language="C#" AutoEventWireup="true" Debug="true" CodeFile="CS_PriceByDate.aspx.cs" Inherits="CS_PriceByDate" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

    <div id="showthetable" runat="server" />
    
    <br /><br />
    
    <asp:SqlDataSource ID="SelectProductSqlDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            SelectCommand="SELECT [Product] FROM [ProductPriceByDate]"></asp:SqlDataSource>
    
    <asp:DropDownList ID="SelectProductDropDownList" runat="server" 
            DataSourceID="SelectProductSqlDataSource" DataTextField="Product" 
            DataValueField="Product">
    </asp:DropDownList>
    
    <br /><br />
    
    Purchase Date: <asp:TextBox ID="PurchaseDateTextBox" runat="server" /><br /><br />
    
    <asp:Button ID="SendButton" runat="server" Text="Send" onclick="SendButton_Click" />
    
    <br /><br />
    
    <br /><br />
    
    Chosen Date: <div id="chosendate" runat="server" /><br />
    Chosen Price: <div id="chosenprice" runat="server" />
    

    </form>
</body>
</html>

 

**************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class CS_PriceByDate : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            string sqlcommand = "SELECT * FROM ProductPriceByDate";
            SqlCommand cmd = new SqlCommand(sqlcommand, conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                showthetable.InnerHtml += reader["Product"].ToString() + "   ";
                showthetable.InnerHtml += reader["Price"].ToString() + "   ";
                showthetable.InnerHtml += reader["Date"].ToString() + "<br />";
            }
            reader.Close();
            conn.Close();
        }
    }

    protected void SendButton_Click(object sender, EventArgs e)
    {

        DateTime thedatetime = Convert.ToDateTime(PurchaseDateTextBox.Text);
        string stringdate = thedatetime.Month + "-" + thedatetime.Day + "-" + thedatetime.Year;

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string sqlcommand = "SELECT Date FROM ProductPriceByDate WHERE Date < '" + stringdate + "' ORDER BY Date DESC" ;
        SqlCommand cmd = new SqlCommand();
        cmd = new SqlCommand(sqlcommand, conn);

        conn.Open();

        string thedate = Convert.ToString(cmd.ExecuteScalar());

        sqlcommand = "SELECT Price FROM ProductPriceByDate WHERE Date < '" + stringdate + "' ORDER BY Date DESC";
        cmd = new SqlCommand(sqlcommand, conn);

        string theprice = Convert.ToString(cmd.ExecuteScalar());

        conn.Close();

        chosendate.InnerHtml = thedate;
        chosenprice.InnerHtml = theprice;
    }
}

 

Answer 34

User-1760427068 posted

 Note, if you have not yet worked with ExecuteScalar() we used it because it searches until it find the first instance where everything specified is true, then it takes the value and it doesn't care if there are any other scenarios also true that could be found if it continued.  This works to our advantage here because by using an ORDER by DESC we start at the latest value (within the specified date) and then we work backwards to find the latest record matching all the conditions--ie, the last time the product changed for the product.

The question could come up, how do I do use this if I want to show the price at the time of sale if I am selling the product right now?

To accommodate this, remove the WHERE clause from the SQL.   Now the searching starts at the very last entry of the table and works backwards until it finds the most recent price change for the product.

-Larry

 

Answer 35

User-821206271 posted

Hi i am using visual web developer 2008. VB.. How to do shopping cart? Cause i couldnt get the output..

,

Answer 36

User-1760427068 posted

Hi:

You can find work in progress (almost finished) at http://forums.asp.net/t/1364807.aspx and you are welcome to join us.

Dalphis:  (Dalphis is one of the two people who worked on this CS version)

I added a class to work done in the VB Shopping Cart project (1364807--see above).  I don't know if this is of interest to you--If I had to guess I would say you are already working with classes so it isn't new to you.  However, if not and this is of interest, let me know -- it was fun working with you Dalphis.

-Larry