Shutdown PC command line

  shutdown /s

Run VBA reports in Multiple Instances - Shell

Sub RunExcelMultiple(RecCnt)

    Dim lPid As Long, i As Long

    Dim sRemoteWbName As String, sExeName As String, sParams As String

    sExeName = "excel.exe"

    

    sRemoteWbName = """" & ActiveWorkbook.FullName & """"

    

    sParams = " /e" & " -!- " & " "

    

    If (RecCnt < 11) Then

      lPid = Shell(sExeName & sParams & sRemoteWbName, vbHide)

    Else

        For ShellInstance = 1 To Round(WorksheetFunction.Min(8, RecCnt / 4), 0)

            lPid = Shell(sExeName & sParams & sRemoteWbName, vbHide)

        Next ShellInstance

    End If

    

       

End Sub

Cannot connect to MS SQL 2008 R2 locally or remotely - Error 53

• A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

  solution

  Error 53 sql server 2008 r2

  
  

  To enable a server network protocol

  1.
  

  In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.

  
  

  2.

  In the console pane, click Protocols for <instance name>.

  
  

  3.

  In the details pane, right-click the protocol you want to change, and then click Enable or Disable.

  
  

  4.

  In the console pane, click SQL Server Services.

  
  

  5.

  In the details pane, right-click SQL Server (<instance name>), and then click Restart, to stop and restart the SQL Server service.

  
  

  http://msdn.microsoft.com/en-us/library/ms191294.aspx

  
  

  
  

  
  

Login from SQL-server in ASP.Net C#

    private bool SiteLevelCustomAuthenticationMethod(string UserName, string Password)
    {

        bool boolReturnValue = false;

        string strConnection = ConnectionString;

        SqlConnection Connection = new SqlConnection(strConnection);
        string strUserID = UserName;
        String strSQL = "select * from UserDetails where UDUSRID=\'" + (strUserID + "\'");
        SqlCommand command = new SqlCommand(strSQL, Connection);
        SqlDataReader Dr;
        Connection.Open();
        Dr = command.ExecuteReader();
        while (Dr.Read())
        {
            string UserNameDB = Dr["UDUSRID"].ToString();
            string PasswordDB = Dr["UDPASSWD"].ToString();
            //  string UserRoleDB = Dr["UDROLE"].ToString();


            if ((UserName == UserNameDB.TrimEnd().TrimStart()) & (Password == PasswordDB.TrimEnd().TrimStart()))
            {
                Session["Usrid"] = UserNameDB;
                Session["Uspasswd"] = PasswordDB;
                //    Session["Usrole"] = UserRoleDB;
                //  Response.Write(Session["Usrole"]);
                // Response.End();

                boolReturnValue = true;
                break;

            }

        }

        Dr.Close();

        return boolReturnValue;

    }

Send email in a format c#

    void sndemail()
    {
        MailMessage objEmail = new MailMessage();
        objEmail.To = "xxxxx@xxxxxx.com";//Session["OEMAL"].ToString().Trim();//Emald.ToString().Trim();//txtTo.Text;
        //objEmail.To = "dostind@gmail.com";//Session["OEMAL"].ToString().Trim();//Emald.ToString().Trim();//txtTo.Text;
   
        //objEmail.From = "rajeev@xxxxxx.net";
        objEmail.From = "noreply@xxxxxx.com";

        //objEmail.Cc = txtCc.Text;
        objEmail.Subject = "HI -- Notification";// +Strrslt.ToString();
        //Response.Write(Session["oename"].ToString());  HI Quiz – Course Completion Notification
        //Response.End();
        //objEmail.Body = "<table border=1 bgcolor=#F3EFE0><tr><td>Date :</td><td><b>" + DateTime.Today.ToLongDateString() + "</td></tr><tr><td>Exam Id:</td><td>" + Request["id"].ToString() + "</td></tr><tr><td>Trainee:</td><td> </b> " + Session["repname"].ToString() + "</td></tr><tr><td>Total Correct:</td><td>" + cortans + " Out of:-" + totrec + "</td></tr><tr><td>Owner: </td><td>" + Session["oename"].ToString() + "</td></tr><tr><td>Program:</td><td>Home Impovement</td></tr><tr><td><b>Result:</b></td><td><b>" + Strrslt.ToString() + "</b></td></tr>  </table>";
        objEmail.Body = "<b>The following trainee has completed the Home Improvement training course:</b><br><br>";
        objEmail.Body += "<table border=0 width=50%   bgcolor=#CCCCCC cellpadding=10 ><tr><td>Trainee :</td><td><b>" + Session["repname"].ToString() + "</td></tr><tr><td>Market:</td><td>" + Session["oeMarket"].ToString() + "</td></tr><tr><td>ICD:</td><td> </b> " + Session["owniNam"].ToString().Replace("'"," ") + "</td></tr><tr><td>Date:</td><td>" + DateTime.Today.ToLongDateString() + "</td></tr><tr><td>Total Correct: </td><td>" + cortans + " Out of:-" + totrec + "</td></tr><tr><td><b>Status:</b></td><td><b>" + Strrslt.ToString() + "</b></td></tr>  </table>";
      
        //objEmail.Body = Label4.Text;

        //objEmail.Priority = MailPriority.High;
        objEmail.BodyFormat = MailFormat.Html;//MailFormat.Html; Session["oeMarket"]
        // Make sure you have appropriate replying permissions from your local system

       SmtpMail.SmtpServer = "10.0.10.123";
        //SmtpMail.SmtpServer = "localhost";

        try
        {
            SmtpMail.Send(objEmail);


        }
        catch (Exception exc)
        {
            Response.Write("Send failure: " + exc.ToString());
        }

    }


    private string ConnectionString
    {
        get
        {
            string connectionString = ConfigurationManager.ConnectionStrings["smdbConnectionString51"].ConnectionString;
            return connectionString;
        }
    }

banner maker

http://www.mybannermaker.com/

Extract Excel input string "+"

DECLARE @test varchar(100) = '+HARIA+123+MALE+STUDENT+HOUSEWIFE+jhgjh+54545+hgdfjhg+xxxxc'

SELECT TOP 8000

    Num

INTO

    #Number

FROM

    (

    SELECT

       ROW_NUMBER() OVER (ORDER BY c1.object_id) AS Num

    FROM

       sys.columns c1, sys.columns c2, sys.columns c3

    ) N

SELECT

    ROW_NUMBER() OVER (ORDER BY Num) AS Rank,

    LTRIM(RTRIM(SUBSTRING(@test,

                          Num,

                          CHARINDEX('+', @test + '+', Num) - Num

                ))) AS Value

FROM

    #Number

WHERE

    Num <= LEN (@test)

    AND

    SUBSTRING('+' + @test, Num, 1) = '+'

DROP TABLE #Number

Source :stackoverflow

Excel vba 2010 browse select folder and open file

Private Sub CommandButton1_Click()

Dim myfile As Variant

Dim counter As Integer

Dim path As String

myfolder = "c:\tmp\"

ChDir myfolder

myfile = Application.GetOpenFilename(, , , , True)

counter = 1

If IsNumeric(myfile) = True Then

MsgBox "No files selected"

End If

While counter <= UBound(myfile)

path = myfile(counter)

Workbooks.Open path

counter = counter + 1

Wend

End Sub

Compatibility Between the 32-bit and 64-bit vba excel

http://msdn.microsoft.com/en-us/library/ee691831(office.14).aspx#odc_office2010_Compatibility32bit64bit_Comparing32BitSystemsto64BitSystems

excel vba 64 bit issue

http://www.jkp-ads.com/articles/apideclarations.asp

 

How to Save a PDF to a SQL Server

http://www.ehow.com/how_7334919_save-pdf-sql-server.html

How to select cells/ranges by using Visual Basic procedures in Excel

ActiveSheet.Cells(5, 4).Select  

or 

  
ActiveSheet.Range("D5").Select  

http://support.microsoft.com/kb/291308

Excel VBA ComboBox Populate dynamic value

Private Sub UserForm_Click()

With ComboBox1

For Row = 1 To 10 'Each cell in the range

If ActiveSheet.Cells(2, Row) <> "" Then

.AddItem ActiveSheet.Cells(2, Row)

End If

Next Row

End With

ActiveSheet.Cells(5, 5) = ComboBox1.Value

End Sub

Write A Report in Excel using VBA - SQL-server

I wrote first report in Excel-VBA using data from SQL-Server 

Sub runreports()

    

    Dim cnt As ADODB.Connection

    Dim rst As ADODB.Recordset

    Dim stSQL As String

    Dim wbBook As Workbook

    Dim wsSheet As Worksheet

    Dim rnStart As Range

    

     

    Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=xxxxx;Data Source=devDB;User ID=myuser;Password=mypass; Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=myPC;Use Encryption for Data=False;Tag with column collation when possible=False"

    

     

    Set wbBook = ActiveWorkbook

    Set wsSheet = wbBook.Worksheets(5)

    

    With wsSheet

        Set rnStart = .Range("A7")

    End With

    'Report Name

    Cells(1, 10).Value = "Interco Report"

    

    stSQL = "SELECT top 100 * FROM tbl_intercoRep"

    'stSQL = "SP_Int_DeleteThis"

'    stSQL = "SP_IntercoReport"

   

     

    Set cnt = New ADODB.Connection

    

    With cnt

        .CursorLocation = adUseClient

        .Open stADO

        .CommandTimeout = 0

        Set rst = .Execute(stSQL)

    End With

   

'Worksheets("sheet5").Activate

Dim ii As Integer

    For ii = 0 To rst.Fields.Count - 1

    Cells(6, ii + 1).Value = rst.Fields(ii).Name

Next ii

 

    With Range("A1:ZZ1").Font

    .Bold = True

    .Size = 15

   

    End With

   

    With Range("A6:ZZ6").Font

    .Bold = True

   

    

    End With

   

     'Here we add the Recordset to the sheet from A1

    rnStart.CopyFromRecordset rst

    

     'Cleaning up.

    rst.Close

    cnt.Close

    Set rst = Nothing

    Set cnt = Nothing

End Sub

Building a Word Document Using SQL Server Data

In this walkthrough, you will first create a Microsoft® Office Word 2003 document that contains bookmarks as the location for inserting text retrieved from the Microsoft SQL Server Northwind sample database. You will then use ADO.NET to connect to and retrieve the data. You'll insert the data in the Word document at the specified bookmark.

http://msdn.microsoft.com/en-us/library/office/aa192487(v=office.11).aspx

http://www.codeproject.com/Articles/96969/A-Dynamic-Word-Template-that-Generates-information