Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 9th, 2005, 03:22 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default PROBLEM Inserting data into 2 tbls that r JOINED

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>
                        &nbsp; (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>
                        &nbsp; (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>
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <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
Reply With Quote
  #2 (permalink)  
Old March 10th, 2005, 01:25 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

You need to pass an output parameter from the sellers table with the ID just inserted. Then use that ID in the next Insert statement.
Reply With Quote
  #3 (permalink)  
Old March 10th, 2005, 10:40 AM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So I need to create a variable to hold the 'SellerID' and use this variable in my 2nd INSERT command into the Properties table? Did I understand that right?

I am confused with "pass an output parameter" ?

Would it look something like this?

             OleDbParameter sidParam =
                 new OleDbParameter("@SellerID", OleDbType.VarChar, 20);
                                                   IS THE TYPE VarChar for AUTO#?

                 sidParam.Value = ???????? I AM USING AN AUTO # IN DB ?????;
                 dbCommand.Parameters.Add(sidParam);

              How would I pass the AutoNumber generated by db ?


Thanks for your help!!

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem in inserting data in gridview deb_kareng ASP.NET 2.0 Professional 0 August 22nd, 2007 02:07 AM
problem in inserting data monikanit ASP.NET 1.x and 2.0 Application Design 1 July 30th, 2007 02:48 AM
mysterious problem (data no inserting ) gratisaccount VB.NET 2002/2003 Basics 1 June 29th, 2006 08:48 AM
Problem inserting with data adapter cridley ADO.NET 1 February 22nd, 2005 05:02 PM
inserting into joined tables cjennings SQL Server 2000 3 July 23rd, 2003 06:10 AM



All times are GMT -4. The time now is 12:18 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.