0 Replies Latest reply: May 6, 2013 1:33 PM by tarun Sharma RSS

    ODBC connection to SQL server (Access Point) - Write Back

      Hi Guys,

       

      I have been able to create a write back script which inserts/modifies/deletes records to/from a SQL server database.

      When I open the QV dashboard from QV access point (using IE plug in), the scripts works perfectly fine if the computer is on the same domain as SQL server

      Issues comes in when:

      1) I open the dashboard (through Access Point) from a computer on a different domain (other than SQL server)

      2) And, a DSN pointing to SQL server is not defined on local machine

       

      Ideally, when accessing dashboard from QV server it should use the DSN's defined on server instead of DSN's defined on local.

      Is there something that I am missing on configuration/settings? I am attaching herewith the script used:

       

       

      Sub Sql_Insert 
      'ActiveDocument.ReloadEx 2,1 'Reload app
      
      
      Dim sServer, sConn, oConn,oRS
      sServer =""
      sConn = "Driver={SQL Server};Server=ServerName;Database=DatabaseName;"
      
      Set oConn = CreateObject("ADODB.Connection")
                oConn.Open sConn
        
      Set oRS =CreateObject("ADODB.Recordset")
      
      
      Set v_Comment_Selected = ActiveDocument.Fields("Input_Analyst_Comment").GetPossibleValues
      v_Input_Comment = v_Comment_Selected.Item(0).Text
      msgbox(v_Input_Comment)
      
      
      Set v_EmpID = ActiveDocument.Fields("TL_TRAN_EMP_SSO").GetPossibleValues
      msgbox(v_EmpID.count)
      
      
      Set v_ReportID = ActiveDocument.Fields("TL_TRAN_REPORT_ID").GetPossibleValues
      msgbox(v_ReportID.count)
      
      
      'Set v_Table = ActiveDocument.Fields("TableName").GetPossibleValues
      'v_table_name = v_Table.Item(0).Text
      
      
      If v_EmpID.Count > 1 And v_ReportID.count > 1 Then
                MsgBox ("Please select atleast one Employee or Expense Report")
      Elseif v_EmpID.Count = 1 And v_ReportID.Count > 1 Then
                'sSQL = "UPDATE [" & v_table_name & "$] SET Analyst_Comment = '" & v_Input_Comment &"' WHERE Employee_ID = " & v_EmpID.Item(0).Text
                sSQL="INSERT INTO [ReviewerComment] (Expense_Report_ID, Employee_ID, Reviewer_Comment) VALUES ('" & v_ReportID.Item(0).Text & "'," & v_EmpID.Item(0).Text & ",'" & v_Input_Comment &"')" 
                Msgbox(sSQL)
                ors.open sSQL, oconn
      Else
                'sSQL = "UPDATE [" & v_table_name & "$] SET Analyst_Comment = '" & v_Input_Comment &"' WHERE Employee_ID = " & v_EmpID.Item(0).Text & " AND Expense_Report_ID = '" & v_ReportID.Item(0).Text & "'"
                sSQL="INSERT INTO [ReviewerComment] (Expense_Report_ID, Employee_ID, Reviewer_Comment) VALUES ('" & v_ReportID.Item(0).Text & "'," & v_EmpID.Item(0).Text & ",'" & v_Input_Comment &"')" 
                Msgbox(sSQL)
                ors.open sSQL, oconn
      End If
      
      
                'Msgbox(sSQL)
                'ors.open sSQL, oconn
                ActiveDocument.ReloadEx 2,1 'Reload app
                'doc.ClearAll 'Clear all selections
                Msgbox("The comment has been successfully updated.")'End message
      End Sub