April 23, 2008 at 5:40 am
Hi,
I dont understand what this procedure is doing and how this one will helpfull in my case??
I have problem in number generation. Proper number generation is not happening because query cant access recently added records. see my code.
If a new number is generated insertion have no problem. If number generated is an existing one Sql Server not allowing to insert because of duplicate key. I indexed this field. But the field is not primary key.
Expecting reply
Sujith
April 23, 2008 at 10:59 am
If you believe that there are uncommited transactions causing the problem, then you will have to change something.
Is your application the only application creating the new BoardNos? Are you using transactions?
Most of the advice given so far has been right regarding generating the new number at the same time as the INSERT. Whether by making it an IDENTITY field, or calculating the next number yourself doesn't matter.
A transaction that goes like this is going to have problems:
1. BEGIN TRANSACTION
2. Get the next available BoardNo.
3. Figure out what other values go with the record.
4. Break for lunch.
5. Insert the new record.
6. COMMIT TRANSACTION
The exageration is intentional to make the point. The only way to reserve your new BoardNo is to create the record and commit it at the same time.
April 24, 2008 at 2:39 am
Hi Friend(s),
Here I am Adding My full code for insertion.
First part to Enquiry master details & then to Enquiry Details.
Please see this. Problem in Enquiry detail Section where new board no is generated. some times its working without any problem. But when frequent insertion coming query in the part of number generation failed to get maximum number because recently inserted Records are neglected. Those records are visible in table. I can see for eg. 14222 as maximum number but query getting 14215 as last record number. Then when i try to insert 14216 duplication error returns.
************************************************
On Error GoTo errhndl
adocon.BeginTrans
'Inserting details to Enquiry master
Dim strsql As String
Dim inttend As Integer
If blnTender Then
inttend = 1
Else
inttend = 0
End If
If blnRevision Then
intEnqtype = Val(lblEnqtypecode.Caption)
intconscode = Val(lblConsCode.Caption)
' intcustcode = Val(lblCustCode.Caption)
Else
intEnqtype = cboEnqType.ItemData(cboEnqType.ListIndex)
intconscode = cboConsultant.ItemData(cboConsultant.ListIndex)
' intcustcode = Val(lblCustCode.Caption)
End If
strconsNo = "CR" & CStr(Format(intconscode, "000#"))
Dim cm As New ADODB.Command
cm.ActiveConnection = adocon
cm.CommandType = adCmdStoredProc
cm.CommandText = "sp_insert_EnquiryMaster"
cm.CreateParameter "eNo", adVarChar, adParamInput
cm.CreateParameter "eDate", adDate, adParamInput
cm.CreateParameter "rDate", adDate, adParamInput
cm.CreateParameter "via", adVarChar, adParamInput
cm.CreateParameter "eType", adVarChar, adParamInput
cm.CreateParameter "se", adVarChar, adParamInput
cm.CreateParameter "ee", adVarChar, adParamInput
cm.CreateParameter "de", adVarChar, adParamInput
cm.CreateParameter "cnCode", adVarChar, adParamInput
cm.CreateParameter "prName", adVarChar, adParamInput
cm.CreateParameter "rem", adVarChar, adParamInput
cm.CreateParameter "ref", adVarChar, adParamInput
cm.CreateParameter "EUser", adVarChar, adParamInput
cm.CreateParameter "IsT", adSmallInt, adParamInput
cm.CreateParameter "Rev", adSmallInt, adParamInput
cm.Parameters(1).value = txtEnquiryNo
cm.Parameters(2).value = Format(dtpEnqDate, "mm/dd/yyyy")
cm.Parameters(3).value = Format(DTPReqDate, "mm/dd/yyyy")
cm.Parameters(4).value = cboVia.Text
cm.Parameters(5).value = intEnqtype
cm.Parameters(6).value = cboSEngr
cm.Parameters(7).value = txtEengr
cm.Parameters(8).value = txtDEngr
cm.Parameters(9).value = strconsNo
cm.Parameters(10).value = txtProject
cm.Parameters(11).value = txtRemarks
cm.Parameters(12).value = txtRef
cm.Parameters(13).value = txtEndUser
cm.Parameters(14).value = inttend
cm.Parameters(15).value = 0
cm.Execute
Call WriteToLog("EnquiryMaster", txtEnquiryNo, Date, Time, "Insert")
' Setting User Details
strsql = "insert into enqUser values('" & txtEnquiryNo & "'," _
& GUsercode & ",1,1,1)"
adocon.Execute strsql
Call WriteToLog("EnqUser", txtEnquiryNo, Date, Time, "Insert")
Dim row As Long, col As Integer
With TDBBoardDetails
row = 0
With xarrDb
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
' strsql = "Select " & fld & " from " & tbl & " Order By Boardno"
' rsmaxId.MoveLast
IntnewBno= IIf(IsNull(rsmaxId(0)), 1, rsmaxId(0) + 1)
rsmaxId.Close
Set rsmaxId = Nothing
For row = 0 To .UpperBound(1)
.value(row, 13) = IntnewBno
IntnewBno = IntnewBno + 1
Next
End With
End With
xarrDb.QuickSort xarrDb.LowerBound(1), xarrDb.UpperBound(1), 13, _
XORDER_ASCEND, XTYPE_INTEGER
'Inserting details to Enquiry Detail
'Dim row As Long, col As Integer
row = 0
col = 0
With TDBBoardDetails
row = 0
With xarrDb
For row = 0 To .UpperBound(1)
If Val(.value(row, 6)) = 0 Then
.value(row, 6) = 2
End If
cm.ActiveConnection = adocon
cm.CommandType = adCmdStoredProc
cm.CommandText = "sp_insert_EnquiryDetail"
cm.CreateParameter "eno", adVarChar, adParamInput
cm.CreateParameter "bName", adVarChar, adParamInput
cm.CreateParameter "qty", adSingle, adParamInput
cm.CreateParameter "ptcode", adSmallInt, adParamInput
cm.CreateParameter "pcCode", adSmallInt, adParamInput
cm.CreateParameter "Loc", adVarChar, adParamInput
cm.CreateParameter "IP", adSmallInt, adParamInput
cm.CreateParameter "bno", adBigInt, adParamInput
cm.CreateParameter "rev", adSmallInt, adParamInput
cm.CreateParameter "BDesc", adSmallInt, adParamInput
' cm.CreateParameter "Pstype", adSmallInt, adParamInput
cm.Parameters(1).value = txtEnquiryNo
cm.Parameters(2).value = .value(row, 14)
cm.Parameters(3).value = .value(row, 2)
cm.Parameters(4).value = .value(row, 1)
cm.Parameters(5).value = Val(.value(row, 6))
cm.Parameters(6).value = .value(row, 7)
cm.Parameters(7).value = Val(.value(row, 8))
cm.Parameters(8).value = Val(.value(row, 13)) 'IntnewBno
cm.Parameters(9).value = Val(lblRevision)
cm.Parameters(10).value = .value(row, 0)
cm.Execute
Call WriteToLog("EnquiryDetail", txtEnquiryNo, Date, Time, "Insert")
Next
End With
End With
adocon.CommitTrans
MsgBox "New Enquiry [" & txtEnquiryNo & "] Saved", vbOKOnly, "New Entry"
prc_insert_Data = True
Exit Function
errhndl:
adocon.RollbackTrans
Call prc_WriteErr
MsgBox "Transaction Cancelled", vbCritical
prc_insert_Data = False
****************************************************
April 24, 2008 at 3:11 am
The problem you have has something to do with recordset you generate to pickup boardno value. First I would try to calculate number of records returned to see if master table was populated with all needed records. If number of records is ok, then your select statement is wrong. The other possibility is that recordset does not handle well values form sql string you created. Remembered before for vba and access database that you had to write rst.movelast and rst.movefirst to get correct value. But it was long time ago. Hope it was fixed in the meantime.
The second option is to move everything into stored procedure, set transactions there and try. Your loop could have call to one stored procedure which should have:
1. begin trans
2. insert into master
3. select boardno
4. insert into detail
5. commit
6. use @@error after every statement or SET XACT_ABORT ON
7. rollback
I do believe that using sql strings is wrong way of executing sql server queries.
April 24, 2008 at 3:15 am
[font="Verdana"]
For row = 0 To .UpperBound(1)
.value(row, 13) = IntnewBno
IntnewBno = IntnewBno + 1
Next
Can you explain the above code is for what? As your are already fetching the Top 1 records from table and again you are incrementing the value for IntnewBno through the loop.
Mahesh
[/font]
MH-09-AM-8694
April 24, 2008 at 7:50 am
Dear Mahesh,
When I get a new number this is for only for first row, for the rest of the row I am incrementing values through the loop.
Sujith
April 24, 2008 at 8:22 am
Why not commit after each row. Not only that, but drop the connection and get a new one for the next insert. You probably won't notice any performance difference, but even if there is a little more overhead, the cleanup should give you a little more control over your troubleshooting.
April 25, 2008 at 8:18 am
SUJITH KUMAR (4/23/2008)
Hi,If a new number is generated insertion have no problem. If number generated is an existing one Sql Server not allowing to insert because of duplicate key. I indexed this field. But the field is not primary key.
Sujith
When defining the index for the field did you select unique? - even though it is not the primary key setting the index property to Unique will give you the problem.
April 25, 2008 at 3:28 pm
Dear ,
I have problem in number generation only.
You mistaken by my sentences that I have problem in insertion.
Problem in insertion is due to wrong number generation.
Sujith
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply