Excel VBA executing SQL Server multiple stored procedure

Private Sub btnGetProjectInfo_Click()
       
        Dim projectNo As String
        Dim strConn As String
        Dim conn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim ProjectInfoTable As New ADODB.Recordset
        Dim OTTRdatesTable As New ADODB.Recordset
        Dim TasksTable As New ADODB.Recordset
               
        projectNo = "123456"
        strConn = "provider =sqloledb; Data Source = ******; Initial Catalog = *********; User Id = ********; Password = ************;"
            
        conn.ConnectionString = strConn
        conn.Open
           
            'Execute stored procedure for Project Information
            cmd.ActiveConnection = conn
            cmd.CommandType = adCmdStoredProc
            cmd.CommandText = "SP_PROJECT_SELECT"
            cmd.Parameters.Refresh
            cmd.Parameters("@PROJECT_NUM").Value = projectNo
            Set ProjectInfoTable = cmd.Execute
           
            'Execute stored procedure for OTTRdatesTable
            'cmd.ActiveConnetion = conn
            cmd.CommandType = adCmdStoredProc
            cmd.CommandText = "SP_OTTR_Dates_Select"
            cmd.Parameters.Refresh
            cmd.Parameters("@PROJECT_NO").Value = projectNo
            Set OTTRdatesTable = cmd.Execute
           
            'Execute stored procedure for TasksTable
            'cmd.ActiveConnection = conn
            cmd.CommandType = adCmdStoredProc
            cmd.CommandText = "SP_Task_Dates_Select"
            cmd.Parameters.Refresh
            cmd.Parameters("@PROJECT_NO").Value = projectNo
            Set TasksTable = cmd.Execute
                 
            'Get ProjectInfoTable date
            If ProjectInfoTable.EOF Then
                MsgBox ("Project not found.")
            Else
                Dim No As String
                No = ProjectInfoTable.Fields.Item("Process Type")
            End If
           
            'Get OTTRdates Table data
            If OTTRdatesTable.EOF Then
                MsgBox ("Project not found.")
            Else
                Dim No As String
                No = ProjectInfoTable.Fields.Item("Process Type")
            End If
           
            'Get TasksTable Table data
            If TasksTable.EOF Then
                MsgBox ("Project not found.")
            Else
                Dim No As String
                No = ProjectInfoTable.Fields.Item("Process Type")
            End If
               
        conn.Close
        Set ProjectInfoTable = Nothing
        End Sub

Comments

Post a Comment

Popular posts from this blog

Sending email using Powershell script

Image
 Below is a script that can be used to send email using Powershell. If ((Get-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )   { Add-PSSnapIn -Name Microsoft.SharePoint.PowerShell } $currentDate = Get-Date -Format g $HeaderOne $HeaderTwo $HeaderThree $HeaderFour $Outputreport = "<HTML><TITLE> Test E-Mail notification </TITLE> <head> <style>body {font-family: Calibri;font-size: 14px;}table {border-collapse: collapse;}table, th, td {border: 1px solid black;font-family: Calibri,Verdana;font-size: 14px;}th {background-color: #4CAF50;color:white;}</style></head>                      <BODY>                                           <H2> Test E-Mail notification on $($currentDate) CET </H2>  ...
Read more

Convert List Collection to DataTable using C#

public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)         {             PropertyDescriptorCollection properties =                TypeDescriptor.GetProperties(typeof(T));             System.Data.DataTable table = new System.Data.DataTable();             foreach (PropertyDescriptor prop in properties)                 table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);             foreach (T item in data)             {                 DataRow row = table.NewRow();                 foreach (PropertyDescriptor prop in properties)                ...
Read more

Difference Between Site Template and Site Definition

Difference Between Site Template and Site Definition Before going into direct comparison, let me define both: Site Templates  : Are  snapshots of  sites at a point in time. When a user customizes a site from the UI or SPD, the custom template consists of the difference between the original state of the site (determined by its definition) and the state of the site or list when the custom template is generated. Custom templates remain tied to a particular site definition (for example, the site definition for SharePoint Web sites or Meeting Workspace Web sites), so that if the site definition is not present or is changed, the custom template cannot work. Site Definitions  : As the name implies, A Site definition is "core definition of a site”. Each site definition emerges through a combination of files that are placed in the 12 hive of WFE during installation of SharePoint. Site definition files include .xml, .aspx, .ascx, and .master page files, as well as docum...
Read more