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.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Trigger go Nuts over ConnString and DSN

Author  Topic  reyboy Starting Member 12 Posts Posted - 2004-10-29 : 05:55:37 Public Sub ConnOpen() Set cnMyConn = New ADODB.Connection DoEvents 'Makes trigger go crazy DataConn.OpenDB mConnStr 'makes trigger function properly DataConn.OpenDB "DSN=MyDSN" Set cnMyConn = DataConn.Conn cnMyConn.Open mConnStr DoEvents cnMyConn.CursorLocation = adUseClient DoEvents OpenConn = True End Sub -------------------------- -------------------------- 'Dataconn is an object Public Function OpenDB(ByVal vConnectingSting As String) As Boolean ' OUTPUT ' True = database was successfully opened ' gConn (private) and Conn (public) is set to be the universal ADO Connection ' False = something went wrong On Error GoTo Eror Set gConn = New ADODB.Connection With gConn .ConnectionString = vConnectingSting .CursorLocation = adUseClient .Open End With OpenDB = True mConnStr = vConnectingSting Exit Function Eror: MsgBox Err.Description, vbCritical End Function -------------------------------- -------------------------------- 'Trigger that goes crazy(makes a duplicate record) CREATE TRIGGER UpdateMRPRed ON dbo.mat_tblPurchaserequisitiondet FOR UPDATE, INSERT AS UPDATE srv_tblMaterialsReqDet SET srv_tblMaterialsReqDet.PRed = (SELECT SUM(QTY) FROM mat_tblPurchaserequisitiondet WHERE MRNo = srv_tblMaterialsReqDet.MRNo AND StockCode = srv_tblMaterialsReqDet.StockCode) WHERE (srv_tblMaterialsReqDet.StockCode = (SELECT TOP 1 StockCode FROM inserted)) AND (srv_tblMaterialsReqDet.MRNo = (SELECT TOP 1 MRNo FROM inserted)) -------------------------------------- -------------------------------------- We wondered why we always have duplicate records but when we changed the connection string to DSN everything went fine. Proud To Be Pinoy