Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    13

    Unanswered: Insert and Update a Table in Single Web Page

    A problem has come up in designing a Web page to maintain a small reference table in SQL Server 2000 (9 columns, about 25 records).

    I tried to design the Web page to allow INSERT and UPDATE operations using a single page that posts back to itself. The page contains a set of empty form fields for a new record, followed by a set of filled-in form field for each row in the table. The form fields for existing records are given a unique name based on the field name concatenated with the primary key value for that row.

    If I set up the page to INSERT only, it works properly. But when I add the fields for existing records, the INSERT operation malfunctions. Specifically, anytime a set of existing fields for a particular column is added to the page, the INSERT will no longer work properly for that column. This is true for all fields except the primary key field. It always INSERTs correctly. I tried adding only some columns to the set of existing form fields. In that case, the INSERT operation added the correct values for the fields that were not listed in the existing records section, but failed for the others.

    I am using the INSERT INTO syntax for that operation and the recordset .Update syntax for the edits. I tried using the recordset .AddNew/.Update syntax for the insert, but it exhibited the same problems. The column data types contain smallint, bit, nvarchar, and ntext types.

    I know that the correct values are being put into the INSERT statement. I also tried renaming the INSERT form fields to be totally different than the names of the existing record fields. But the problem comes back no matter what.

    If necessary, I can split the logic so that inserts and updates are handled by different pages. But I would like to make this work if possible. If a reader knows why SQL Server is causing this problem, any help would be greatly appreciated.
    Mike Rogers
    Chattanooga State Technical Community College
    Chattanooga, TN, USA

  2. #2
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Is it possible for you to have this page call a stored procedure? In that test for existence, if the record exists do an update, if not an insert. I had to write a web based app a few years ago and used stored procedures so I would have greater control.

  3. #3
    Join Date
    May 2003
    Posts
    13

    Follow-Up

    Would stored procedures solve the problem? It sounds like something is confusing the data base about which information to put in the fields. I thought this might be because the field names were similar, but a test proved the same problem happened even if the insert and update fields had totally different names.

    A stored procedure might or might not fix this situation.
    Mike Rogers
    Chattanooga State Technical Community College
    Chattanooga, TN, USA

  4. #4
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    It seems the the application does not understand the state(new/edited) of the record being created/modified.
    It should be a good idea to create a business/data access layer class for handling the back-end operations.
    The Stored procedures will definitely help to keep this working properly as long as you have right kind of logic running in the Business/Data access layer.
    In GOD we believe. Everything else we Test!

  5. #5
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    I just recently created a webpage in asp which dealt with some of the same issues. I used 1 page for insert and update. Here is my logic:

    1. The page where you came from needs to tell you what you are going to do; insert or update. I did this by using the GET method in asp.

    2. If insert, then all control values = "" else control values = dbvalue

    3. I used the html <form method="POST">, with once again some bool variable telling me if it was to insert or update. By using the post I did however use another page to do the actual select or update, but if everything was successful it redirects the page back to the main page where the changes are displayed


    Hopes this helps

  6. #6
    Join Date
    May 2003
    Posts
    13

    More About Insert/Update in the Same Page

    This is a response to a couple of the suggestions in this thread.

    One thing that confuses me is that so far, I have only tested the page using one operation or the other. Mostly I have tested just the insert function. The page logic ensures that the update does not run unless something has changed in an existing record. So one would think that the data base should not be confused by a request to insert a single record, even if update logic exists that is not called.

    If the data base is getting confused by insert and update, why does a stored procedure help the situation? It looks like the stored procedure merely takes the data base logic and moves it to the DB server instead of the ASP processor. I am presuming that such a data base stored procedure would take parameters passed from the web page and create an INSERT or UPDATE statement to do the work.

    For the person who did this by using a different page to insert or update and then redirecting to the original page, was it essential to use a separate page to do the work? My page is POSTing to itself. The insert/update logic is placed above the page display code.

    This has been an ...interesting... situation to discuss. Thanks for the input.
    Mike Rogers
    Chattanooga State Technical Community College
    Chattanooga, TN, USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •