Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-08-29 : 09:37:37
|
| Mike writes "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. Any help would be greatly appreciated." |
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-08-29 : 15:49:39
|
Mike,If you move the insert/update to a stored procedure, you can use code there to decide what the operation is, using logic likeif exists(select whatever_key_field from whatever_table where whatever_key_field = your_parameter) -- do the updateelse -- do the insert There wouldn't be any confusion there. (It's also safer for you, as if you use parameters to execute the stored procedure, you eliminate SQL injection issues.)Ken |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-08-29 : 16:10:57
|
| Absolutely move the code to a stored procedure. Keep data-logic in the database. We can assist you in writing the sproc if you need help. |
|
|
|
|
|
|
|
|
INSERT and UPDATE Ops in the Same Web Page