August 11, 2006 at 6:30 am
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.
Logic in the Web page prevents an insert or update unless there is a value to insert or update. So far, I have only tested the INSERT operation since it is not working properly. During these tests, the page only tries to INSERT a value, not insert and update in the same POST operation.
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.
Someone suggested that the data base engine is becoming confused about whether it should try to insert or update a record. I could understand that if both operations were included in the same POST. But since the first tests only attempt to INSERT a record, it is difficult to understand how that could happen. I have also heard a suggestion to move the insert and update logic to a SQL Server stored procedure and call that from the Web page. I will probably try that, but do not understand how that will help the situation.
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
Mike Rogers
Chattanooga State Technical Community College
Chattanooga, TN, USA
August 11, 2006 at 7:03 am
Not sure what your problem is but I doubt if SQL is confused about INSERT or UPDATE, maybe your code logic is.
To to do what you are doing then I would design the page thus
Declare the input fields first with a suffix of I, e.g.
Field1I Field2I Field3I
etc
Then produce the list of updateable fields (using a loop) and name the fields without any suffix, e.g
Key Field1 Field2 Field3
Key Field1 Field2 Field3
Key Field1 Field2 Field3
Key Field1 Field2 Field3
etc
Notice that the field names for update are all named the same on each line, this will produce arrays
Then in your code, check the validity of the I suffix fields and perform the insert
Then perform a loop n times where n is 25 (if there are 25 fixed lines) or retirieve the number of lines using
Request.Form("Key").count
Then reference the Key and each Field using
Request.Form("Key")(n)
and
Request.Form("Field1")(n)
Request.Form("Field2")(n)
etc
where n is the index number in the loop
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply