I have a little project thrown at me and my group by my teacher, I can’t seem to get the where clause correct. I think it’s a where clause I need to use. Anyways here’s the scenario. The web app has to insert into table COOKIE :
ID_G_NAME, ORDER_NUMBER, Mints, Samoas, Tagalongs, Dos, Trefoils, Lemonades
There is a drop down list for the names and from that I need the ID (ID_G_NAME ) From the table GIRLS. It’s probably something silly but I can’t seem to get it. Also when I comment out the ID_G_NAME and the code that goes with it all the rest does insert into the database
Thanks in advance for the help
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>
SQL INSERT/WHERE problem
Posted - 2009-11-03 : 21:42:58