Imports System.Data
Imports System.Data.OleDb
Partial Class _Default
    Inherits System.Web.UI.Page
    Dim strSQL As String
    Dim objCommand As OleDbCommand
    Dim objDataReader As OleDbDataReader
    Dim objConnection As OleDbConnection
    Dim connString As String
    Dim FilePath As String
    Dim file As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim L, X As Integer
        Dim root As String = "asmarl" 'Be sure to change to your userid
        FilePath = Server.MapPath("~/")
        'Response.Write(FilePath)
        L = Len(root)
        X = InStr(FilePath, root)
        file = Mid(FilePath, 1, X + L) + "database\" & "GirlScoutCookies.accdb"

        'Response.Write("<br>" & file)
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file
        'Response.Write("<br>" & connString)

        Dim objConnection As OleDbConnection

        If Not (IsPostBack) Then 'we only want to populate the dd lists once
            'dimension a datarow object for interrogating the data table
            Dim dr As DataRow
            'load the fields drop down object
            'set a connection and open
            objConnection = New OleDbConnection(connString)
            objConnection.Open()




            'define the SQL statement
            strSQL = "Select G_NAMES from GIRLS order by G_NAMES"
            'define the data adapter and data table
            Dim da = New OleDbDataAdapter(strSQL, objConnection)
            Dim dt = New DataTable
            'fill data adapter
            da.Fill(dt)
            'close the connection
            objConnection.Close()
            'Initialize the list with the wild card (*) character for select all
            Me.ddGirlsName.Items.Add("*")
            'Traverse the data table adding the field TEAM-NAME to ddTeam
            For Each dr In dt.Rows
                Me.ddGirlsName.Items.Add(dr("G_NAMES").ToString)

       
            Next


        End If
    End Sub

    
    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim objConnection As OleDbConnection

        objConnection = New OleDbConnection(connString)


        strSQL = "INSERT INTO COOKIE "
        strSQL = strSQL & " (ID_G_NAME, ORDER_NUMBER, Mints, Samoas, Tagalongs, Dos, Trefoils, Lemonades )  VALUES ("
        'ID_G_NAME, 
        strSQL = strSQL & ", '" & Convert.ToString(ddGirlsName.SelectedItem) & "'"
        strSQL = strSQL & "" & txtOrder.Text & ""

        strSQL = strSQL & ", " & txtMints.Text & ""
        strSQL = strSQL & ", " & txtSamoas.Text & ""
        strSQL = strSQL & ", " & txtTagalongs.Text & ""
        strSQL = strSQL & ", " & txtDos.Text & ""
        strSQL = strSQL & ", " & txtTrefoils.Text & ""
        strSQL = strSQL & ", " & txtLemonades.Text & ""
        strSQL = strSQL + ") WHERE & ddGirlsName.SelectedItem & = ID_G_NAME"
        'WHERE COOKIE.ID_G_NAMES = GIRLS.ID_G_NAMES '& ddGirlsName.SelectedItem &' = COOKIE.ID_G_NAMES "

        Try
            objConnection.Open()
            'set up and define an instance of the connector
            Dim objCommand As OleDbCommand
            objCommand = New OleDbCommand(strSQL, objConnection)
            'execute the SQL statement using the 
            objCommand.ExecuteNonQuery()
            'close the connection 
            objConnection.Close()



            'Response.Write("<br>Command Completed")
            'EMPTY THE FORM CONTENTS ONCE QUESTION IS SAVED
            txtOrder.Text = ""
            txtMints.Text = ""
            txtSamoas.Text = ""
            txtTagalongs.Text = ""
            txtDos.Text = ""
            txtTrefoils.Text = ""
            txtLemonades.Text = ""
            ddGirlsName.SelectedIndex = 0
            'update the user's view
            Response.Write("<br>" & strSQL)
        Catch ex As Exception
            Response.Write(ex)
        End Try




    End Sub
End Class

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!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>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style2
        {
            width: 77px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    <asp:GridView ID="gvDisplay" runat="server">
    </asp:GridView>
    <br />
    <asp:Label ID="Label8" runat="server" Font-Bold="True" Text="Order Number"></asp:Label>
    <asp:TextBox ID="txtOrder" runat="server" Width="86px"></asp:TextBox>
    <br />
    <asp:Label ID="lblName" runat="server" Font-Bold="True" 
        Text="Select Scouts  Name"></asp:Label>
       
      
    <br />
    <asp:DropDownList ID="ddGirlsName" runat="server">
    </asp:DropDownList>
               
    <br />
    <asp:Label ID="Label7" runat="server" Font-Bold="True" 
        Text="Enter Quantity Below"></asp:Label>
    <br />
    <table class="style1">
        <tr>
            <td class="style2">
                <asp:Label ID="Label1" runat="server" Text="Thin Mints"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="txtMints" runat="server" Width="70px" Height="22px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                <asp:Label ID="Label2" runat="server" Text="Samoas"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="txtSamoas" runat="server" Width="70px" Height="22px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                <asp:Label ID="Label3" runat="server" Text="Tagalongs"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="txtTagalongs" runat="server" Width="70px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                <asp:Label ID="Label4" runat="server" Text="Do-Si-Dos"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="txtDos" runat="server" Width="70px" Height="22px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                <asp:Label ID="Label5" runat="server" Text="Trefoils"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="txtTrefoils" runat="server" Width="70px" Height="22px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                <asp:Label ID="Label6" runat="server" Text="Lemonades"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="txtLemonades" runat="server" Width="71px"></asp:TextBox>
            </td>
        </tr>
    </table>
    <asp:Button ID="btnSubmit" runat="server" Text="Submit Order" 
        style="height: 26px" />
    </form>
</body>
</html>