Here is my problem:
I have 1 form that inserts data into 2 tables in one db.
The tables:
Sellers and Property which I want to submit data into.
The tables are joined in the DB with a 1 to many relationship
one Seller to many properties
Primary Keys are: [u]SellerID</u> and [u]PropertyID </u>
SellerID is also a foreign key in the Property Table
I am using 2 seperate INSERT commands which worked fine before I tried to join the tables(which is necessary)
I am recieving the following error:
You cannot add or change a record because a related record is required in table 'Sellers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'Sellers'.
In manually entering data into the Access DB, I was successfull if I entered into the Seller table 1st, Populating SellerID which is an AutoNumber, which then could be passed to the Property table as a foreign key.
My code is set up to enter the Seller info 1st and reconnects with the Property info insert.
What can I do to make this work???????????????? Please help
Here is my code:
<%@ Page Language="C#" Debug="true" %>
<%@ Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
// Gobal variable message
string strMessage = "Thank You!" + "\n" + "The database has been updated.";
void btnSubmit_Click(object sender, EventArgs e) {
//connection string to database
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+
"Ole DB Services=-4; "+
"Data Source=C:\\ASP.NET\\WNAFormTest\\WNAFormTest.mdb";
OleDbConnection dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
//Insernt Command INTO Sellers Table
string commandString = "INSERT INTO Sellers(FirstName, LastName, HomePhone, WorkCellPhone, Availability, Email)" +
"Values (@FirstName, @LastName, @HomePhone, @WorkCellPhone, @Availability, @Email)" ;
OleDbCommand dbCommand = new OleDbCommand(commandString, dbConnection);
OleDbParameter fnParam =
new OleDbParameter("@FirstName", OleDbType.VarChar, 20);
fnParam.Value = txtFirstName.Text;
dbCommand.Parameters.Add(fnParam);
OleDbParameter lnParam =
new OleDbParameter("@LastName", OleDbType.VarChar, 20);
lnParam.Value = txtLastName.Text;
dbCommand.Parameters.Add(lnParam);
OleDbParameter hpParam =
new OleDbParameter("@HomePhone", OleDbType.VarChar, 50);
hpParam.Value = txtHomePhone.Text;
dbCommand.Parameters.Add(hpParam);
OleDbParameter wcpParam =
new OleDbParameter("@WorkCellPhone", OleDbType.VarChar, 50);
wcpParam.Value = txtWorkCellPhone.Text;
dbCommand.Parameters.Add(wcpParam);
OleDbParameter avParam =
new OleDbParameter("@Availability", OleDbType.VarChar, 50);
avParam.Value = Convert.ToString(cboAvailability.SelectedValue) ;
dbCommand.Parameters.Add(avParam);
OleDbParameter emParam =
new OleDbParameter("@Email", OleDbType.VarChar, 50);
emParam.Value = txtEmail.Text;
dbCommand.Parameters.Add(emParam);
///Insernt Command INTO Property Table
string commandString2 = "INSERT INTO Property(Address1, Address2, City, County, ZipCode, HeatedSquareFt, Bedrooms, Baths, Pool, Garage, Construction, Appliances) " +
"Values (@Address1, @Address2, @City, @County, @ZipCode, @HeatedSquareFt, @Bedrooms, @Baths, @Pool, @Garage, @Construction, @Appliances)";
OleDbCommand dbCommand2 = new OleDbCommand(commandString2, dbConnection);
OleDbParameter add1Param =
new OleDbParameter("@Address1", OleDbType.VarChar, 75);
add1Param.Value = txtAddress1.Text;
dbCommand2.Parameters.Add(add1Param);
OleDbParameter add2Param =
new OleDbParameter("@Address2", OleDbType.VarChar, 75);
add2Param.Value = txtAddress2.Text;
dbCommand2.Parameters.Add(add2Param);
OleDbParameter ctyParam =
new OleDbParameter("@City", OleDbType.VarChar, 50);
ctyParam.Value = txtCity.Text;
dbCommand2.Parameters.Add(ctyParam);
OleDbParameter cntParam =
new OleDbParameter("@County", OleDbType.VarChar, 50);
cntParam.Value = Convert.ToString(cboCounty.SelectedValue) ;
dbCommand2.Parameters.Add(cntParam);
OleDbParameter zipParam =
new OleDbParameter("@ZipCode", OleDbType.VarChar, 50);
zipParam.Value = txtZip.Text;
dbCommand2.Parameters.Add(zipParam);
OleDbParameter hsfParam =
new OleDbParameter("@HeatedSquareFt", OleDbType.VarChar, 50);
hsfParam.Value = txtHeatedSquareFt.Text;
dbCommand2.Parameters.Add(hsfParam);
OleDbParameter bedParam =
new OleDbParameter("@Bedrooms", OleDbType.VarChar, 50);
bedParam.Value = Convert.ToString(cboBedrooms.SelectedValue) ;
dbCommand2.Parameters.Add(bedParam);
OleDbParameter bthParam =
new OleDbParameter("@Baths", OleDbType.VarChar, 50);
bthParam.Value = Convert.ToString(cboBathrooms.SelectedValue) ;
dbCommand2.Parameters.Add(bthParam);
OleDbParameter poolParam =
new OleDbParameter("@Pool", OleDbType.VarChar, 50);
poolParam.Value = Convert.ToString(cboPool.SelectedValue) ;
dbCommand2.Parameters.Add(poolParam);
OleDbParameter grgParam =
new OleDbParameter("@Garage", OleDbType.VarChar, 50);
grgParam.Value = Convert.ToString(cboGarage.SelectedValue) ;
dbCommand2.Parameters.Add(grgParam);
OleDbParameter cstrParam =
new OleDbParameter("@Construction", OleDbType.VarChar, 50);
cstrParam.Value = Convert.ToString(cboConstruction.SelectedValue) ;
dbCommand2.Parameters.Add(cstrParam);
// appliance variables
string strRefridgerator = "";
string strRange = "";
string strDishwasher = "";
string strWasherDryer= "";
if (chkRefridgerator.Checked )
{
strRefridgerator = "refridgerator, ";
}
if (chkRange.Checked )
{
strRange = "range, ";
}
if (chkDishwasher.Checked )
{
strDishwasher = "dishwasher, ";
}
if (chkWasherDryer.Checked )
{
strWasherDryer = "washer/dryer ";
}
string strAppliances = strRefridgerator + strRange + strDishwasher + strWasherDryer;
OleDbParameter appParam =
new OleDbParameter("@Appliances", OleDbType.VarChar, 50);
appParam.Value = strAppliances ;
dbCommand2.Parameters.Add(appParam);
dbCommand.ExecuteNonQuery();
dbCommand2.ExecuteNonQuery();
dbConnection.Close();
lblSuccess.Text = strMessage;
txtFirstName.Text = "";
txtLastName.Text = "";
txtHomePhone.Text = "";
txtWorkCellPhone.Text = "";
txtEmail.Text = "";
txtConfirmEmail.Text = "";
}
// reset fields
void btnReset_Click(object sender, EventArgs e)
{
txtFirstName.Text = "";
txtLastName.Text = "";
txtHomePhone.Text = "";
txtWorkCellPhone.Text = "";
txtEmail.Text = "";
txtConfirmEmail.Text = "";
lblSuccess.Text = "Please Enter Values";
}
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<span><label>
<table style="WIDTH: 537px; HEIGHT: 371px" height="371" cellpadding="2" width="537" align="center">
<tbody>
<tr>
<td bgcolor="#e0e0e0" colspan="3">
<strong>Personal Information</strong></td>
</tr>
<tr>
<td>
<asp:Label id="Label1" runat="server" width="127px" font-bold="True" font-size="X-Small">First
Name:</asp:Label></td>
<td>
<asp:TextBox id="txtFirstName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server" Font-Size="X-Small" Font-Bold="True" ControlToValidate="txtFirstName" ErrorMessage="First Name Required" Width="137px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label2" runat="server" width="150px" font-bold="True" font-size="X-Small">Last
Name:</asp:Label></td>
<td>
<asp:TextBox id="txtLastName" runat="server"></asp:TextBox>
</td>
<td>
<div align="left">
<asp:RequiredFieldValidator id="RequiredFieldValidator8" runat="server" Font-Size="X-Small" Font-Bold="True" ControlToValidate="txtLastName" ErrorMessage="Last Name Required" Font-Names="Times New Roman"></asp:RequiredFieldValidator>
</div>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label3" runat="server" width="150px" font-bold="True" font-size="X-Small">Home
Phone: </asp:Label></td>
<td>
<asp:TextBox id="txtHomePhone" runat="server"></asp:TextBox>
(xxx) xxx-xxxx</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label4" runat="server" width="150px" font-bold="True" font-size="X-Small">Work/Cell
Phone:</asp:Label></td>
<td>
<asp:TextBox id="txtWorkCellPhone" runat="server"></asp:TextBox>
(xxx) xxx-xxxx</td>
<td>
<div align="left">
<asp:RequiredFieldValidator id="RequiredFieldValidator4" runat="server" Font-Size="Smaller" Font-Bold="True" ControlToValidate="txtWorkCellPhone" ErrorMessage="Phone Number Required"></asp:RequiredFieldValidator>
</div>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label5" runat="server" width="150px" font-bold="True" font-size="X-Small">Availability:</asp:Label></td>
<td>
<asp:DropDownList id="cboAvailability" runat="server" Width="144px" DataSource="<%# Page %>">
<asp:ListItem Value="Please Select">Please Select</asp:ListItem>
<asp:ListItem Value="Afternoon">Afternoon</asp:ListItem>
<asp:ListItem Value="Evening">Evening</asp:ListItem>
<asp:ListItem Value="Morning">Morning</asp:ListItem>
</asp:DropDownList>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label6" runat="server" width="150px" font-bold="True" font-size="X-Small">Email:</asp:Label></td>
<td>
<asp:TextBox id="txtEmail" runat="server"></asp:TextBox>
</td>
<td>
<div align="left">
<asp:RequiredFieldValidator id="RequiredFieldValidator6" runat="server" Font-Size="X-Small" Font-Bold="True" ControlToValidate="txtEmail" ErrorMessage="Email Required"></asp:RequiredFieldValidator>
</div>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label7" runat="server" font-bold="True" font-size="X-Small">Confirm
Email:</asp:Label></td>
<td>
<asp:TextBox id="txtConfirmEmail" runat="server"></asp:TextBox>
</td>
<td>
<div align="left">
<asp:CompareValidator id="CompareValidator1" runat="server" Font-Size="X-Small" Font-Bold="True" ControlToValidate="txtEmail" ErrorMessage="Email Does Not Match" ControlToCompare="txtConfirmEmail"></asp:CompareValidator>
</div>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td bgcolor="lightgrey" colspan="3">
<strong>Property Information</strong></td>
</tr>
<tr>
<td>
<p>
<asp:Label id="Label8" runat="server" width="127px" font-bold="True" font-size="X-Small">Address:</asp:Label>
</p>
</td>
<td colspan="2">
<asp:TextBox id="txtAddress1" runat="server" Width="201px"></asp:TextBox>
<asp:RequiredFieldValidator id="RequiredFieldValidator3" runat="server" Font-Size="X-Small" Font-Bold="True" ControlToValidate="txtAddress1" ErrorMessage="Address Required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
</td>
<td colspan="2">
<asp:TextBox id="txtAddress2" runat="server" Width="201px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label9" runat="server" width="127px" font-bold="True" font-size="X-Small">City:</asp:Label></td>
<td>
<asp:TextBox id="txtCity" runat="server"></asp:TextBox>
</td>
<td>
<div align="left">
<asp:RequiredFieldValidator id="RequiredFieldValidator5" runat="server" Font-Size="X-Small" Font-Bold="True" ControlToValidate="txtCity" ErrorMessage="City Required"></asp:RequiredFieldValidator>
</div>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label10" runat="server" width="127px" font-bold="True" font-size="X-Small">County:</asp:Label></td>
<td>
<asp:DropDownList id="cboCounty" runat="server" Width="144px" DataSource="<%# Page %>">
<asp:ListItem Value="Please Select">Please Select</asp:ListItem>
<asp:ListItem Value="Hillsborough">Hillsborough</asp:ListItem>
<asp:ListItem Value="Pasco">Pasco</asp:ListItem>
<asp:ListItem Value="Pinellas">Pinellas</asp:ListItem>
<asp:ListItem Value="Other">Other</asp:ListItem>
</asp:DropDownList>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label11" runat="server" width="127px" font-bold="True" font-size="X-Small">Zip
Code:</asp:Label></td>
<td>
<asp:TextBox id="txtZip" runat="server" Width="87px"></asp:TextBox>
</td>
<td>
<div align="left">
<asp:RequiredFieldValidator id="RequiredFieldValidator7" runat="server" Font-Size="X-Small" Font-Bold="True" ControlToValidate="txtZip" ErrorMessage="Zip Code Required"></asp:RequiredFieldValidator>
</div>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label12" runat="server" width="127px" font-bold="True" font-size="X-Small">Heated
Square Ft:</asp:Label></td>
<td>
<asp:TextBox id="txtHeatedSquareFt" runat="server" Width="87px"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label13" runat="server" width="127px" font-bold="True" font-size="X-Small">Bedrooms:</asp:Label></td>
<td>
<asp:DropDownList id="cboBedrooms" runat="server" Width="52px" DataSource="<%# Page %>">
<asp:ListItem Value="1">1</asp:ListItem>
<asp:ListItem Value="2">2</asp:ListItem>
<asp:ListItem Value="3">3</asp:ListItem>
<asp:ListItem Value="5">4</asp:ListItem>
<asp:ListItem Value="5">5</asp:ListItem>
<asp:ListItem Value="6">6</asp:ListItem>
</asp:DropDownList>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label14" runat="server" width="127px" font-bold="True" font-size="X-Small">Bathrooms:</asp:Label></td>
<td>
<asp:DropDownList id="cboBathrooms" runat="server" Width="52px" DataSource="<%# Page %>">
<asp:ListItem Value="1">1</asp:ListItem>
<asp:ListItem Value="1.5">1.5</asp:ListItem>
<asp:ListItem Value="2">2</asp:ListItem>
<asp:ListItem Value="2.5">2.5</asp:ListItem>
<asp:ListItem Value="3">3</asp:ListItem>
<asp:ListItem Value="3.5">3.5</asp:ListItem>
<asp:ListItem Value="4">4</asp:ListItem>
</asp:DropDownList>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label15" runat="server" width="127px" font-bold="True" font-size="X-Small">Garage:</asp:Label></td>
<td>
<asp:DropDownList id="cboGarage" runat="server" Width="114px" Font-Names="Arial" DataSource="<%# Page %>">
<asp:ListItem Value="Please Select">Please Select</asp:ListItem>
<asp:ListItem Value="1 Car">1 Car</asp:ListItem>
<asp:ListItem Value="2 Car">2 Car</asp:ListItem>
<asp:ListItem Value="2.5 Car">2.5 Car</asp:ListItem>
<asp:ListItem Value="3 Car">3 Car</asp:ListItem>
<asp:ListItem Value="1 Car Carport">1 Car Carport</asp:ListItem>
<asp:ListItem Value="2 Car Carport">2 Car Carport</asp:ListItem>
<asp:ListItem Value="None">None</asp:ListItem>
</asp:DropDownList>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label id="Label16" runat="server" width="127px" font-bold="True" font-size="X-Small">Pool:</asp:Label></td>
&nbs
PROBLEM Inserting data into 2 tbls that r JOINED
