[baachitech]

Happy Morning!

I posted my VB6 code here, I need it in Sql Server 2000 Stored Procedure. How can i convert this code into stored procedure...

Private Sub Update_Modify_Delete_Record()
	
	On Error GoTo ErrHndIt
	
	If MsgBox("Are you sure! Do You Want to Update?", vbQuestion + vbYesNo, "Confirmation") = vbYes Then
		Dim sSql As String
		
		Dim MITEM_CD As String
		Dim MPURNO As Integer
		Dim MPURDT As String
		Dim MPICES As Integer
		Dim MMTRS As Double
		Dim MRATE As Double
		Dim mslno As Integer
		Dim MAMT As Double
		Dim MPONO As Integer
		Dim MFLG As Boolean
		Dim dVOUAMT As Double
		Dim dVATAMT As Double
		Dim dTRNVAL As Double
		
		Dim MTRN_TYPE As String, MPUR_TYPE As String, MVOU_TYPE As String, MSTK_TYPE As String
		Dim MPURACCODE As String, MPURSUBCODE As String, MTRN_VAL As Double, MCU_PONO As String
		 
		MFLG = True	'IF TRUE CLOSE THE PO ORDER ELSE LEAVE IT
		
		MTRN_TYPE = "P"
		MPUR_TYPE = "PU"		' CONFIRM IT WITH MR.RAMESH - 'MPTP_CODE
		MVOU_TYPE = MPUR_TYPE
		MSTK_TYPE = MPUR_TYPE
		
		Dim MCTR As Integer
		
		If erecordmode = adEditDelete Then
			If MsgBox("Be sure ! Do you want to Delete (Y/N) ? ", vbYesNo + vbQuestion, "Deletion!") = vbNo Then
				Exit Sub
			End If
		End If
		If erecordmode = adEditDelete Or erecordmode = adEditInProgress Then
			' DELETE THE RECORD
				
			' display message "Deletion in Progress.."
				
			' SELECT TRANSACT		   SEEK MVOU_TYPE+STR(MVOU_NO,6)
			' SELECT PURCHASE		   SEEK MPUR_TYPE+STR(MVOU_NO,6)
			' SELECT ACMASTER		   SELECT ITEM
			' SELECT STOCK			  SEEK MSTK_TYPE+STR(MVOU_NO,6)
	
			' TRANSACTION FILE
			If rsTemp.State = adStateOpen Then Set rsTemp = Nothing
			rsTemp.CursorLocation = adUseClient
			sSql = "SELECT * FROM TAGENTRN WHERE F_COCODE=" & gCCD & " AND F_VOU_TYPE='" & MVOU_TYPE & "' AND F_VOU_NO = " & Int(MVOU_NO)
			rsTemp.Open sSql, cnUnique, adOpenKeyset, adLockPessimistic
			While Not rsTemp.EOF
				Call upd_abal(Trim(rsTemp!F_ACCODE), rsTemp!F_VOU_AMT, rsTemp!F_AMT_TYPE, rsTemp!F_VOU_DATE, "D")
				rsTemp.Delete adAffectCurrent
				rsTemp.MoveNext
			Wend
			rsTemp.Update
				
			' PURCHASE TABLES
			If rsTemp.State = adStateOpen Then Set rsTemp = Nothing
			rsTemp.CursorLocation = adUseClient
			sSql = "SELECT * FROM TAPDTTRN WHERE F_COCODE=" & gCCD & " AND F_PUR_TYPE='" & MPUR_TYPE & "' AND F_VOU_NO = " & Int(MVOU_NO)
			rsTemp.Open sSql, cnUnique, adOpenKeyset, adLockPessimistic
			While Not rsTemp.EOF
				'Delete Received Mtrs in TAPODTRN table
				sSql = "UPDATE TAPODTRN SET F_RECD_PICES = F_RECD_PICES - " & rsTemp!F_QTY & ", F_RECD_MTRS = F_RECD_MTRS - " & rsTemp!F_MTRS & _
						" WHERE F_COCODE=" & gCCD & " AND F_PO_NO = " & MPONO & " AND F_ITEM_CD = '" & Trim(rsTemp!F_ITEM_CD) & "'"
				cnUnique.Execute sSql

				sSql = "UPDATE TAITMMST SET F_CUR_STKP = F_CUR_STKP - " & rsTemp!F_QTY & ", F_CUR_STKM = F_CUR_STKM - " & rsTemp!F_MTRS & ", F_ORD_REC_QTY = F_ORD_REC_QTY - " & rsTemp!F_MTRS & " WHERE F_COCODE = " & gCCD & " AND F_ITEM_CD = '" & Trim(MITEM_CD) & "'"
				cnUnique.Execute sSql
				rsTemp.Delete adAffectCurrent
				rsTemp.MoveNext
			Wend
			rsTemp.Update
			cnUnique.Execute "DELETE FROM TAPHDTRN WHERE F_COCODE=" & gCCD & " AND F_VOU_TYPE='" & MVOU_TYPE & "' AND F_VOU_NO = " & Int(MVOU_NO)

			' STOCK TABLE
			sSql = "DELETE FROM TASTKTRN WHERE F_COCODE=" & gCCD & " AND F_STK_TYPE='" & Trim(MSTK_TYPE) & "' AND F_TRN_NO = " & Int(MVOU_NO)
			cnUnique.Execute sSql
			If erecordmode = adEditDelete Then
				mflag = True
				Call clearFlex
				HGRID.rows = 100
				rows = 1
				Call hgrid_setup
				HGRID.Row = 1
				InputBoxAlighnment (0)
				If MPTP_CODE.Enabled = True Then MPTP_CODE.SetFocus
				Exit Sub
			End If
		End If

		'UPDATING FILES: TASTKTRN, COMPANY
		cnUnique.BeginTrans

		If MVCHR_AM = "A" Then
			If rsTemp.State = adStateOpen Then Set rsTemp = Nothing
			rsTemp.CursorLocation = adUseClient
			Set rsTemp = cnUnique.Execute("SELECT * FROM TAPTPMST WHERE F_COCODE = " & gCCD & " AND F_PTP_CODE='" & Trim(MPTP_CODE) & "'")
			MPURACCODE = rsTemp!F_ACCODE & ""
			MPURSUBCODE = rsTemp!f_sub_code & ""
			MVOU_NO = rsTemp!F_LST_PNO + 1
			Set rsTemp = Nothing
			cnUnique.Execute ("UPDATE TAPTPMST SET F_LST_PNO  = " & Int(MVOU_NO))
		End If

		' Update Purchase Header File
		sSql = "INSERT INTO TAPHDTRN (F_COCODE,F_PUR_TYPE,F_VOU_NO,F_VOU_DATE,F_CR_FLAG,F_CU_CODE,F_AMT_TOT,F_DISC_PER, F_DISC_TOT, F_VAT_AMT, F_BILL_AMT, F_PAID_AMT) VALUES ("
		sSql = sSql & gCCD & ",'" & MPUR_TYPE & "'," & Int(MVOU_NO) & ",'" & SQLDate(MVOU_DT.Value, "T") & "','" & Mid(Trim(CMB_CR), 1, 1) & "','" & Trim(MCU_CODE) & "'," & _
					  CDbl(nvl(MAMT_TOT)) & "," & CDbl(nvl(MDISC_PER)) & "," & CDbl(nvl(MDISC_AMT)) & "," & CDbl(nvl(MVAT_AMT)) & "," & CDbl(nvl(MVOU_AMT)) & ",0)"
		cnUnique.Execute sSql
		
		If rsPur.State = adStateOpen Then Set rsPur = Nothing
		rsPur.CursorLocation = adUseClient
		rsPur.Open "Select * from TAPDTTRN WHERE F_COCODE=" & gCCD & " AND F_VOU_NO=0", cnUnique, adOpenKeyset, adLockOptimistic
		
		For MCTR = 1 To HGRID.rows - 1
			
			MITEM_CD = RetCode("TAITMMST", "F_ITEM_NM", Trim(HGRID.TextMatrix(MCTR, 1)), "F_ITEM_CD", False)
			MPONO = Int(nvl(HGRID.TextMatrix(MCTR, 2)))
			'MPURDT = SQLDate(HGRID.TextMatrix(MCTR, 3), "T")
			MMTRS = CDbl(nvl(HGRID.TextMatrix(MCTR, 5)))
			MPICES = Int(nvl(HGRID.TextMatrix(MCTR, 6)))
			MRATE = CDbl(nvl(HGRID.TextMatrix(MCTR, 7)))
			mslno = Int(HGRID.TextMatrix(MCTR, 0))
			
			If MITEM_CD = Empty Or MMTRS = Empty Or MMTRS <= 0 Then
				Exit For
			End If
			
			MAMT = MMTRS * MRATE
			
			' Update Item Table -> F_CUR_STKP, F_CUR_STKM, F_ORD_REC_QTY
			sSql = "UPDATE TAITMMST SET F_CUR_STKP = F_CUR_STKP + " & MPICES & ", F_CUR_STKM = F_CUR_STKM + " & MMTRS & ", F_ORD_REC_QTY = " & MMTRS & " WHERE F_COCODE = " & gCCD & " AND F_ITEM_CD = '" & Trim(MITEM_CD) & "'"
			cnUnique.Execute sSql
			
			' Update Purchase Detailed Table (TAPDTTRN)
			rsPur.AddNew
			rsPur!F_COCODE = gCCD: rsPur!F_PUR_TYPE = MPUR_TYPE: rsPur!F_VOU_NO = Int(MVOU_NO)
			rsPur!F_VOU_DATE = MVOU_DT.Value: rsPur!F_CU_CODE = Trim(MCU_CODE): rsPur!F_SLNO = mslno
			rsPur!F_ITEM_CD = Trim(MITEM_CD): rsPur!F_QTY = MPICES: rsPur!F_MTRS = MMTRS
			rsPur!F_RATE = MRATE: rsPur!F_AMT = MAMT: rsPur!F_NET_AMT = MAMT: rsPur!F_PO_NO = MPONO
					
			If rsMtrs.State = adStateOpen Then Set rsMtrs = Nothing
			rsMtrs.CursorLocation = adUseClient
			sSql = "SELECT * FROM #TEMP_PURCHASE WHERE F_COCODE = " & gCCD & " AND F_PUR_TYPE='" & Trim(MPTP_CODE) & "' AND F_VOU_NO = " & Int(MVOU_NO) & " AND F_SLNO = " & mslno & " AND F_NODE_NAME = '" & Trim(mNodeName) & "'"
			rsMtrs.Open sSql, cnUnique, adOpenKeyset, adLockPessimistic
		
			If rsMtrs.RecordCount > 0 Then
				rsPur!F_QTY1 = rsMtrs!F_QTY1:  rsPur!F_MTRS1 = rsMtrs!F_MTRS1
				rsPur!F_QTY2 = rsMtrs!F_QTY2:  rsPur!F_MTRS2 = rsMtrs!F_MTRS2
				rsPur!F_QTY3 = rsMtrs!F_QTY3:  rsPur!F_MTRS3 = rsMtrs!F_MTRS3
				rsPur!F_QTY4 = rsMtrs!F_QTY4:  rsPur!F_MTRS4 = rsMtrs!F_MTRS4
				rsPur!F_QTY5 = rsMtrs!F_QTY5:  rsPur!F_MTRS5 = rsMtrs!F_MTRS5
				rsPur!F_QTY6 = rsMtrs!F_QTY6:  rsPur!F_MTRS6 = rsMtrs!F_MTRS6
				rsPur!F_QTY7 = rsMtrs!F_QTY7:  rsPur!F_MTRS7 = rsMtrs!F_MTRS7
				rsPur!F_QTY8 = rsMtrs!F_QTY8:  rsPur!F_MTRS8 = rsMtrs!F_MTRS8
				rsPur!F_QTY9 = rsMtrs!F_QTY9:  rsPur!F_MTRS9 = rsMtrs!F_MTRS9
				rsPur!F_QTY10 = rsMtrs!F_QTY10:  rsPur!F_MTRS10 = rsMtrs!F_MTRS10
			End If
			
			rsPur.Update
			Set rsMtrs = Nothing
				
			'IF MCTR = LEN(MARR_PUR)
			'	MTRN_VAL = MVOU_AMT - MTOT_VAL
			'Else
			'	MTRN_VAL = Round((MQTY * MRATE) * MVOU_AMT / MTOTAL, 1)
			'End If
			'MTOT_VAL += MTRN_VAL
			MTRN_VAL = 0
			' Update Stock Table (TASTKTRN)
			sSql = "INSERT INTO TASTKTRN (F_COCODE,F_ITEM_CD,F_STK_TYPE,F_TRN_TYPE,F_TRN_NO,F_TRN_DATE,F_TRN_QTY,F_TRN_MTRS,F_TRN_VAL) VALUES (" & _
					gCCD & ",'" & MITEM_CD & "','" & MSTK_TYPE & "','" & MTRN_TYPE & "'," & Int(MVOU_NO) & ",'" & SQLDate(MVOU_DT.Value, "T") & "'," & MPICES & "," & MMTRS & "," & MAMT & ")"
			cnUnique.Execute sSql
			
			If MPONO > 0 Then
				'Update received Mtrs in TAPODTRN table
				sSql = "UPDATE TAPODTRN SET F_RECD_PICES = F_RECD_PICES + " & MPICES & ", F_RECD_MTRS = F_RECD_MTRS + " & MMTRS & _
						" WHERE F_COCODE=" & gCCD & " AND F_PO_NO = " & MPONO & " AND F_ITEM_CD = '" & Trim(rsTemp!F_ITEM_CD) & "'"
				cnUnique.Execute sSql
				' CLOSE THE PO ORDER IF RECEIVED QUANTITY REACH ORDERED QUANTITY (F_STATUS = C)
				If rsTemp.State = adStateOpen Then Set rsTemp = Nothing
				sSql = "SELECT * FROM TAPODTRN WHERE F_COCODE = " & gCCD & " AND F_PO_NO = " & MPONO & " AND F_NO_MTRS > F_RECD_MTRS "
				rsTemp.CursorLocation = adUseClient
				rsTemp.Open sSql, cnUnique, adOpenKeyset, adLockPessimistic
				If rsTemp.RecordCount > 0 Then
				Else
					sSql = "UPDATE TAPOHTRN SET F_STATUS = 'C' WHERE F_COCODE = " & gCCD & " AND F_PO_NO=" & MPONO
					cnUnique.Execute sSql
				End If
			Else
				' Remars: If purchase number not found, where we should update the quantity
			End If
		Next
			
		' Update Transact Table (TAGENTRN)
		sSql = "INSERT INTO TAGENTRN (F_COCODE,F_VOU_TYPE,F_VOU_NO,F_REF_NO,F_VOU_DATE,F_SLNO,F_ACCODE,F_VOU_AMT,F_AMT_TYPE,F_NARRATN, F_CHEQUENO,F_CHEQUEDT,F_BANK_CD) VALUES (" & _
			   gCCD & ",'" & MVOU_TYPE & "'," & Int(MVOU_NO) & ",'" & MCU_PONO & "','" & SQLDate(MVOU_DT.Value, "T") & "',1,'" & MACCODE & "'," & CDbl(nvl(MVOU_AMT)) & ",'C','PURCHASE','" & MCHQ_NO & "','" & SQLDate(MCHQ_DT.Value, "T") & "','" & MBANK_CD & "')"
		cnUnique.Execute sSql
		
		Call upd_abal(MACCODE, CDbl(MVOU_AMT), "C", MVOU_DT.Value, "A")
		
		sSql = "INSERT INTO TAGENTRN (F_COCODE,F_VOU_TYPE,F_VOU_NO,F_REF_NO,F_VOU_DATE,F_SLNO,F_ACCODE,F_SUB_CODE,F_VOU_AMT,F_AMT_TYPE,F_NARRATN, F_CHEQUENO,F_CHEQUEDT,F_BANK_CD,F_STS_CD) VALUES (" & _
			   gCCD & ",'" & MVOU_TYPE & "'," & Int(MVOU_NO) & ",'" & MCU_PONO & "','" & SQLDate(MVOU_DT.Value, "T") & "',2,'" & MPURACCODE & "','" & MPURSUBCODE & "'," & _
			   CDbl(nvl(MVOU_AMT)) - CDbl(nvl(MVAT_AMT)) & ",'D','PURCHASE','" & MCHQ_NO & "','" & SQLDate(MCHQ_DT.Value, "T") & "','" & MBANK_CD & "','" & MSTS_CD & "')"
		
		cnUnique.Execute sSql
		
		If CDbl(nvl(MVAT_AMT)) > 0 Then
			sSql = "INSERT INTO TAGENTRN (F_COCODE,F_VOU_TYPE,F_VOU_NO,F_REF_NO,F_VOU_DATE,F_SLNO,F_ACCODE,F_VOU_AMT,F_AMT_TYPE,F_NARRATN, F_CHEQUENO,F_CHEQUEDT,F_BANK_CD,F_STS_CD) VALUES (" & _
					gCCD & ",'" & MVOU_TYPE & "'," & Int(MVOU_NO) & ",'" & MCU_PONO & "','" & SQLDate(MVOU_DT.Value, "T") & "',3,'" & MVAT_CD & "'," & _
					CDbl(nvl(MVAT_AMT)) & ",'D','VAT ON PURCHASE BILL NO." & MVOU_NO & "','" & MCHQ_NO & "','" & SQLDate(MCHQ_DT.Value, "T") & "','" & MBANK_CD & "','" & MSTS_CD & "')"
			cnUnique.Execute sSql
		End If
		
		MsgBox "Successfuly Saved!", vbOKOnly, "Message"
		
		cnUnique.CommitTrans
		
		mflag = True
		Call clearFlex
		HGRID.rows = 100
		rows = 1
		Call hgrid_setup
		HGRID.Row = 1
		InputBoxAlighnment (0)
		If MPTP_CODE.Enabled = True Then MPTP_CODE.SetFocus
	Else
		TXTINPUT(0).SetFocus
	End If
	
	'delete all records in temporary files
	sSql = "DELETE FROM #TEMP_PURCHASE"
	cnUnique.Execute sSql

	Exit Sub
	
ErrHndIt:
	' Roll Back if error occuered
	cnUnique.RollbackTrans
	
	MsgBox "Error Occured while Updating Table !" & vbCrLf & Err.Number & " : " & Err.Description
	Call UpdErrLog(Err, "Update_Modify_Delete_Record", sSql)
	Exit Sub
End Sub

Please help.

ThanQ for you help

Take kare
Sudha