April 21, 2008 at 12:57 am
Hi friends,
I have some problem when executing following VB Code that query not considering recently added records. Becuase of this function returns Boardno used already and giving me an error of duplication. I think its a problem from Sql Server. Please help me to solve this.
Dim rsmaxId As New ADODB.Recordset
rsmaxId.CursorLocation = adUseClient
strsql = "Select top 1 " & fld & " from " & tbl _
& " Order By Boardno Desc"
rsmaxId.Open strsql, adocon, adOpenStatic, adLockReadOnly
fn_NewId = IIf(IsNull(rsmaxId(0)), 1, rsmaxId(0) + 1)
rsmaxId.Close
Set rsmaxId = Nothing
Sujith Kumar
April 21, 2008 at 1:26 am
[font="Verdana"]
Means you are generating next Id, BoardNo. by the way, why are you passing Tablename as a parameter?
Try this ...
Select Max({Field_1}) From {Table}
Mahesh
[/font]
MH-09-AM-8694
April 21, 2008 at 2:00 am
You use Max(primary_key) to get the next value for insert?
Usually not a good idea. Instead use autoincrement identity type column:
create table X (ID integer identity primary key, data varchar(254))
--you omit primary key column
insert into X (data) values ('test')
--retrieve value of primary key
select @@identity
Alternatively, you can create a key generator, if you need the value in advance, but this would require all clients to use the key generator for inserts.
April 21, 2008 at 6:03 am
1. No problem with function Parameter. I can put tablename directly.
like this,
Dim rsmaxId As New ADODB.Recordset
rsmaxId.CursorLocation = adUseClient
strsql = "Select top 1 Boardno from EnquiryDetail" _
& " Order By Boardno Desc"
rsmaxId.Open strsql, adocon, adOpenStatic, adLockReadOnly
fn_NewId = IIf(IsNull(rsmaxId(0)), 1, rsmaxId(0) + 1)
rsmaxId.Close
Set rsmaxId = Nothing
2. I cannot change table structure, Because I have already data filled in this.
Any other suggession???
April 21, 2008 at 10:20 pm
fn_NewId = IIf(IsNull(rsmaxId(0).field), 1, (rsmaxId(0).field + 1))
Try above code and confirm.
By the way, have you tried my previous post? Also have you tried to debug the routine? If not, debug it with "step in" and give some more detail about the error. what exact error is?
Mahesh
MH-09-AM-8694
April 21, 2008 at 11:13 pm
Hi,
I tried Max(fieldname) also. In this case also same result.
Actually when fetching data query not considering recently added records at all.
Here no error occurs. Query just ignores recently added records.
Error occuring when new boardno generated is not unique. It is already used before in recently added records.
I suspect some data buffer problem between Ado-Sql Server.
Sujith
April 21, 2008 at 11:45 pm
The fundamental problem is that between your query (top or max) and your insert, at least one other user can do the same, so you end up with duplicates.
If you think you can't change the structure because it has data in it, I assure you you can, unless it's a 24/7 production database.
If you really can't change it (your design manager does not allow it), execute the max query the instant before you execute insert, or use generators.
April 21, 2008 at 11:50 pm
[font="Verdana"]I guess, you must be trying to fetch the record before commiting it. Do you have explicit transaction used in your back end code? If so, commit the record after adding it and then try to fetch the same.
Mahesh[/font]
MH-09-AM-8694
April 22, 2008 at 4:43 am
Dear All,
It is not a problem of commmit transaction. I put commit trans statement properly. In most of the cases this code works ok. In some cases only problem occurs.
Sujith
April 22, 2008 at 6:28 am
Your problem could be in your Order By statement. I mean in sorting data. If you deal with varchar field then probably you get strange order, but if your BoardNo is integer then I am wrong. So my suggestion is to run query in query analyzer (i do not know which version of sql server you use) and lookat your data. See what you get re. sort. It could happen someting like this:
9
8
7
6
5
4
3
2
11
10
1
So your .net query would return 10 as last number and 10 already exists. So change your query in order to get real order.
April 22, 2008 at 6:52 am
Hi,
I think this may help:
Dim Q as string
Q= "declare @Id int" & vbcrlf & _
"select @Id = isnull(max(IdField),1) from Table" & vbcrlf & _
"insert into Table (IdField, field2, field3)" & vbcrlf & _
"values (@Id + 1, " & mfield2 & ", " & mfield3 & ")"
debug.print Q
adocon.execute Q
Noel
April 22, 2008 at 9:23 am
Depending if you use this function for inserting new data, you could also create a SQL stored procedure to whom you pass all the values as parameters. Then you can have this stored procedure get the max of the ID that you want and try to insert it with that ID in the same transaction.
You can capture the error and have it rollback and retry the same stored procedure with the same parameters, or have it commit when no error occurs.
But also add a counter to avoid the recurring procedure to end up in an endless loop.
April 22, 2008 at 3:36 pm
Dear all,
1. Its not a problem of simultaneous transaction. I tested it.
2. Boardno field is not varchar, its numeric.
So its some other problem. Its happening for recently added records. I suspect about some refreshing time interval for Sql Server between Commited transaction and query. Still hunting..
Sujith
April 22, 2008 at 10:10 pm
[font="Verdana"]I think you must go the way Robert has suggested you earlier. Ask DB guy to alter the table and add the identity column, so that you’re every call which you make to DB for generating / fetching Next Id will get reduce. Adding identity column to table is not a big deal, if your table has unique data in it.
Mahesh[/font]
MH-09-AM-8694
April 23, 2008 at 3:37 am
I didn't had time to include the SQL code yesterday, so here's the code.
(It could also be helpfull for other purposes)
CREATE PROCEDURE [dbo].[SP_AddRecord]
@FIELD1 INT,
@FIELD2 VARCHAR (50),
@RETRY INT=10 --Times to retry the action
AS
BEGIN
--Insert the new record
BEGIN Tran
INSERT INTO dbo.Tablename (Field1, Field2)
VALUES (@FIELD1, @FIELD2)
IF @@ERROR <> 0
BEGIN
--Undo everything
ROLLBACK Tran
--Retry
SET @RETRY=@RETRY-1
IF @RETRY>0
BEGIN
exec dbo.SP_AddRecord
@FIELD1,
@FIELD2,
@RETRY
END
ELSE BEGIN
/*
Put the action to take if insert was
unsuccesfull after 10 retries here
*/
END
END
ELSE BEGIN
COMMIT Tran
END
END
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply