| Author |
Topic |
|
JeffT
Posting Yak Master
111 Posts |
 Posted - 2007-03-08 : 17:03:50
|
| Hi,I have an ASP program calling a stored procedure. Users enter their data, click submit, and the ASP program calls the SP which inserts the data into the SQL table. Initially it did this without checking for duplicate recs but now I have added code to block dup recs in the stored procedure. The SP is blocking the dups but when it returns to the ASP program I get the error:ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. There was no change of code in the ASP code but it is pointing to a line in that code. Not sure what is causing this,Thanks,J. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-08 : 17:13:58
|
| That sounds like you are trying to read a column in the ASP code that is not in the result set that is being returned by the SP. |
 |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-03-08 : 17:30:47
|
| Thanks snSQL. Prior to changing the Stored Procedure, it unconditionally updated an IDENTITY field in the table, which is not being updated now since the duplicate is being blocked, so I'm guessing the column it is trying to read is that IDENTITY field which is probably NULL ?? Does that make sense ?Thanks again,J. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-08 : 17:49:04
|
| No, the error sounds more like you are trying to access a column in the ASP code that is not being returned. Check the SELECT at the end of the SP carefully and see if the columns exactly match the columns that the ASP code tries to use. This is all assuming that there is a SELECT in the SP and that the ASP code uses the result set returned by the SELECT. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-08 : 22:10:01
|
| Post your stored proc here.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-03-09 : 07:25:36
|
| Ok snSQL, I see what you mean. I did add a SELECT statement to the SP which, prior to the "commit tran" statement, checks to see if approx. 16 of the 20 table fields entered by the user matches any row already there as a means of duplicate checking. The remaining four of the 20 fields are create date, update date etc. fields and would never match. Also the IDENTITY field is not updated until it is determined the new record is not a duplicate and it is also not part of the SELECT but is referenced by the ASP. Is there a way around this, in other words a better way of dup checking?Thanks,J. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-03-09 : 07:55:05
|
| insert into #tempselect @field1,......if not exists(select * from table1 t1 join #temp t2 on t1.field1=t2.field2 and....) insert into table1 select ...from #tempelse select 'duplicate'--------------------keeping it simple... |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-03-09 : 08:07:19
|
| Thanks jen! Could you explain the line that begins with "if not exists(" ? I'm not following what is happening there.Thanks,J. |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-03-19 : 15:23:24
|
| I tried Jen's suggestion above and the compare of the fields in the temp table vs. the permanent table in the "if not exists" statement seems to work if the fields being compared are populated. However, if t1.field1=t2.field1 and both are blank or both are NULL, which was the case with 3-4 of the fields, the record is not flagged as a duplicate.Thanks,J. |
|
|
|
|
ASP error calling stored procedure