Remember

WorksheetFunction. in excel VBA

Blog of the Day

Well, the latest update.  I have moved into some VBA code.  So what is VBA?  VBA stands for Visual Basic for Application (Office).  I have to admit at first I was a little stand offish, but after playing with the tools in the Office application I am a bit taken by the power.  Just like all Microsoft products the Object Model is easy to pick up.

 

I am actually developing some reporting in Excel from SharePoint lists being populated by InfoPath Forms.  I hope to be moving into the InfoPath to Web services to SQL server to us SQL reporting soon.  I will keep everyone updated.

 

<moojjoo>

Adding an image

Moojjoo's Blog Image Post

Backup and Restore on SQL Server

thanks to moonguy on MSDN he saved me many hours of work.

When you try to backup, SQL Server 2005 shows the default backup path i.e. c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ . This directory default has SQL user rights. Go to Backup directory and check the user name. Its very big name and I have not checked how SQL server adds it in allowed users list. You can do 2 things:



1. Let the Server take backup on default directory and copy the file in your desired folder. I did that.

2. Give same kind of permissions to your desired folder as Backup directory has.



Hope it solves the issue.



Moonguy

Classes, Objects and Methods oh my!!!!!

I read a great article the other day for the newbies to C# and OO development http://www.devarticles.com/c/a/C-Sharp/Introduction-to-Objects-and-Classes-in-C-sharp/

Take some time to read it.

Settings.Settings .NET C#

I just learned the newest feature for adding configuration to .NET application (Console, Windows Forms, Web forms, ets.)

Settings.Settings are created when you start your application. This allows you to build out Settings that scope either the application or user. But the big question was how do you access them in the code.

Simple.

Try this

Properties.Settins.Default.[Intellisense takes over and find your setting]

That easy.

InfoPath Designer Mode

Well, after all the research I found Tim Pash as you can see in the post below. It seems that in the Corporate Enviroment you have to request that you have the setting deployed to you by your Microsoft Office Deployment and Configuration team. So with that being said, if you do not see the designer feature in InfoPath send a request to you Information Technology Team.

InfoPath

Well,

I am now back contracting because of my love to CODE. I have a new venture at Bank of America using SharePoint, InfoPath, and Visual Basic for Applications (VBA). My first task is to figure out how to enable InfoPath to allow me to design?

I found a great resource from the Tim Pash: http://blogs.msdn.com/timpash/archive/2006/02/08/Prevent-Design-Mode-For-Users.aspx?CommentPosted=true#commentmessage

Update from comparison of two SQL tables

UPDATE o
SET Amount=Price
FROM orders o JOIN items i ON (o.ItemNumber=i.ItemNumber)

UPDATE o SET EmpActive = 0 FROM ComplianceEmployee o
JOIN ComplianceEmployeeLoad i ON (o.EmpID = i.EmpID)

GoDaddy.com System.Net.Mail Solution

Here is the C# version of the GoDaddy.com ASP.NET 2.0 Send Mail by Moojjoo http://www.mmwebs.com

Web.Config
<?xml version="1.0"?>

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<system.web>

<customErrors mode="Off"/>
<compilation debug="true"/> <!-- Be sure to change this to false when going to production -->

</system.web>
<system.net>

<mailSettings>
<smtp>

<network

host="relay-hosting.secureserver.net" />
</smtp>

</mailSettings>
</system.net>

</configuration>



.cs code behind for your .aspx page

protected void btnSubmit_Click(object sender, EventArgs e)
{

MailMessage myMail = new MailMessage();

myMail.From = new MailAddress("from@yourdomain.com");
myMail.Subject = "Contact Form";

MailAddressCollection myMailTo = new MailAddressCollection();
myMail.To.Add("to@domain.com");

StringBuilder sb = new StringBuilder();
sb.Append("Last Name: " + txtFirstName.Text + "<br>");

sb.Append("First Name: " + txtFirstName.Text + "<br>");
sb.Append("Address: " + txtAddress.Text + "<br>");

sb.Append("City: " + txtCity.Text + "<br>");
sb.Append("State: " + ddlState.SelectedValue + "<br>");

sb.Append("Zip: " + txtZip.Text + "<br>");
sb.Append("Email: " + txtEmail.Text + "<br>");

sb.Append("Questions/Comments: " + txtQandC.Text + "<br>");string strBody = sb.ToString();
myMail.Body = strBody;

myMail.IsBodyHtml = true;

SmtpClient smtp = new SmtpClient();
smtp.Credentials = CredentialCache.DefaultNetworkCredentials;

try

{

smtp.Send(myMail);

myMail = null;
}

catch (System.Exception ex)
{

Response.Write(ex.Message);

}

}

Moojjoo Õ¿Õ

http://www.mmwebs.com

http://www.autoinventoryonline.com/

Advanced Unit Testing, Part I - Overview - The Code Project - C# Programming

Great article on Unit Testing

Advanced Unit Testing, Part I - Overview - The Code Project - C# Programming

ASP.NET 2.0 - Membership and Roles

Today, I have been working with SQL Server 2000 and Visual Studio 2005 to increase my speed in development by using Strongly Typed Datatypes and the Membership/Roles provided by SQL.

For now I am going to jump into using the Membership/Roles provided by ASP.NET.

Microsoft has made it easy to setup membership/role for you Web application, but did not explain the needs most freelance developers must use in order to use rented Web hosting space.

As for my self I develop on http://www.idevconsultants.com/ servers and they run SQL 2000. And not to mention I do not have keys to the server so I have to develop with what I can without making changes to IIS. This is the same for most developers.

So here is the trick to use this new membership tools.

Scott Gu blog http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx explains the ends and outs, but basically the secret is to run the following tool:

Open a command-line window on your system and run the aspnet_regsql.exe utility that is installed with ASP.NET 2.0 in under your C:\WINDOWS\Microsoft.NET\Framework\v2.0.xyz directory. This tool will build all the Tables, StoredProcs needed to run the membership tools in ASP.NET 2.0.

VERY IMPORTANT: Next in your WEB.CONFIG name your connection string to

















Again DO NOT I REPEAT DO NOT change the name...

Testing Visual Studio with Firefox

In my job I develop for Intranets (IE) and Internet (All Browsers) and order to test Firefox I found the following article which was crucial to setting up the Firefox browser for testing an ASP.NET 2.0 application in order to get NTLM to work.

http://brennan.offwhite.net/blog/2005/07/22/firefox-authentication-with-ntlm/

Be sure to add localhost to your proxy if needed.

SQL

This week I was told about a book titled, "Gurus Guide to Transact SQL". So being told about the book I headed to the local Borders with my daughter to check the book out. This book first and for most is better purchased online it is a lot cheaper.

This book is the ultimate guide to SQL Server Transact-SQL. T-SQL is a must for any developer utlizing SQL Server as their backend DBMS (Data Base Management System).

Back on the blog

Moojjoo here and I am would like to apologize for not writting more. Having a 7 month old is a lot of work and not to mention the full plate I have at work lately.

I am very excited that my current company R.J. Reynolds Tobacco Company is finally moving forward to ASP.NET 2.0 and Visual Studio 2005.

As for coding I have really been researching the ability to use tiers in development so I can use as much resuse as possible.

Til Later

Using .NET Outlook to send formatted email

Imports Microsoft.Office
Imports Microsoft.Office.Core
Imports System.Runtime.InteropServices
Imports Outlook = Microsoft.Office.Interop.Outlook


Public Class TestOutlookMail
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
Private Sub InitializeComponent()

End Sub
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Private app As Outlook.ApplicationClass

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
app = New Outlook.ApplicationClass
End Sub

'Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' CreateEmailMessage()
'End Sub 'button1_Click


'Private Sub CreateEmailMessage()
' 'Initialize the envelope values.
' Dim strTo As String = "yourname@yourdomain.com"
' Dim strBCC As String = "yourname@yourdomain.com"
' Dim strCC As String = "yourname@yourdomain.com"
' Dim strSubject As String = "Outlook Automation"
' Dim strBody As String = "Hello World"

' 'Automate the Outlook mail item.
' app = New Outlook.ApplicationClass
' Dim mItem As Outlook.MailItemClass = CType(app.CreateItem(Outlook.OlItemType.olMailItem), Outlook.MailItemClass)
' 'Dim As Outlook.MailItemClass = CType(doc.MailEnvelope.Item, Outlook.MailItemClass)
' 'Outlook.MailItemClass mi = (Outlook.MailItemClass)app.CreateItem
' mItem.To = strTo
' mItem.BCC = strBCC
' mItem.CC = strCC
' mItem.Subject = strSubject
' mItem.BodyFormat = Outlook.OlBodyFormat.olFormatHTML
' mItem.HTMLBody = strBody
' 'mItem.ItemEvents_Event_Close += New Outlook.ItemEvents_CloseEventHandler(Me.wApp_Close)

' 'wApp.Visible = True

' ' Loop until there are no more references to release.
' While Marshal.ReleaseComObject(mItem) > 0
' End While
' mItem = Nothing

' ' Invoke the .NET garbage collector.
' GC.Collect()
' GC.WaitForPendingFinalizers()
'End Sub 'CreateEmailMessage

Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Subject As String = "This is my Subject"
Dim Body As String = "This is my Body"


Dim mi As Outlook.MailItemClass = CType(app.CreateItem(Outlook.OlItemType.olMailItem), Outlook.MailItemClass)
mi.BodyFormat = Outlook.OlBodyFormat.olFormatHTML
mi.HTMLBody = Body
mi.Subject = Subject
mi.Display(New Object)

Dim ni As Outlook.NoteItemClass = CType(app.CreateItem(Outlook.OlItemType.olNoteItem), Outlook.NoteItemClass)
ni.Body = "This is my Body again"
ni.Display(New Object)

Dim pi As Outlook.PostItemClass = CType(app.CreateItem(Outlook.OlItemType.olPostItem), Outlook.PostItemClass)
pi.Subject = Subject
pi.BodyFormat = Outlook.OlBodyFormat.olFormatHTML
pi.Body = Body

pi.Display(New [Object])
End Sub 'button1_Click


End Class

DropDown in DataGrid, How to Select

This is a two parter. First we will discuss on to add a "Select Value" to the Drop Down List after we populate the DropDown with Data from the Database then I will discuss how to Select that value using the OnItemDataBound from the DataGrid.

Inside the DataGrid you must use a Template like so

SQL Time Converter

Private Sub BuildSQLTime(ByVal strProdDate, ByVal strProdTime)
Dim SQLDateTime As DateTime
Dim strDate As String = strProdDate
Dim strTime As String = strProdTime

Dim DateArray(2) As String
DateArray(0) = strDate.Substring(0, 2)
DateArray(1) = strDate.Substring(2, 2)
DateArray(2) = strDate.Substring(4, 2)

Dim tempDateTime As String
Dim newDateTime As DateTime

tempDateTime = DateArray(1) & "/" & DateArray(2) & "/" & DateArray(0)
newDateTime = Convert.ToDateTime(tempDateTime & " " & strTime)
'newDateTime = DateTime.Now

lblResults.Text = newDateTime
End Sub

Update to Access Folders without reboot

Need to access a folder you were just given access to? No need to reboot. Simply go to Start > Run > Type CMD At the dos prompt type gpupdate

SQL Server Leading Zeros for CHAR Type

declare @DirectiveNumber char(6)
declare @DirLength int



SET @DirectiveNumber = (SELECT DirectiveNumber FROM dbo.tblPlants WHERE PlantId = '1201')

--UPDATE dbo.tblPlants SET DirectiveNumber =
SET @DirectiveNumber = @DirectiveNumber + 1

Select @DirLength = LEN(@DirectiveNumber)

IF (@DirLength = 1)
SET @DirectiveNumber = '00000' + @DirectiveNumber
IF (@DirLength = 2)
SET @DirectiveNumber = '0000' + @DirectiveNumber
IF (@DirLength = 3)
SET @DirectiveNumber = '000' + @DirectiveNumber
IF (@DirLength = 4)
SET @DirectiveNumber = '00' + @DirectiveNumber
IF (@DirLength = 5)
SET @DirectiveNumber = '0' + @DirectiveNumber

UPDATE dbo.tblPlants SET DirectiveNumber = @DirectiveNumber WHERE PlantId = '1201'

SELECT @DirectiveNumber