Does SQL Server have anything like VB's On Error GoTo?

To those familiar with Visual Basic, VB provides the option to use the ON ERROR GOTO ... clause to catch practically any error and handle it in one's program.Does SQL Server 2000 have the analogous clause that will jump to a certain part of code in a stored procedure to handle errors?

Solution: Errors must be checked after every SQL statement of interest.

In short: No! SQL Server does not have anything like Visual Basic's "On Error GoTo" construct. It acts as if there was always an "On Error Resume Next" statement in effect.
For effective error handling in a SQL Server stored procedure you must test the value of @@ERROR after EVERY SQL statement that might produce an error. Yes, that's a lot of testing. A well written stored procedure will often be 1/2 or more error handling code.
There's another important consideration in designing error handling: @@ERROR is reset after each and every SQL statement, @@ROWCOUNT is also similarly reset. Therefore, you must capture both of these immediatly after each SQL statement that might produce an error.
I start by declaring two variables to hold the values of @@ERROR and @@ROWCOUNT while the error handling code is working with them. They're declared at the top of every stored procedure and I've added them to my stored procedure template.

DECLARE @myERROR int -- Local @@ERROR , @myRowCount int -- Local @@ROWCOUNT
Next let's say that we had a simple INSERT statement
INSERT INTO Authors (au_id, au_fname, au_lname, contract) VALUES ('123-45-678' , 'Andrew' , 'Novick' , 1 )
Immediately after that statement, you should have the following statement:
SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT
These two assignements must be in the same statement. If you were to split them into two statements, for instance:
SET @myERROR = @@ERRORSET @myRowCOUNT = @@ROWCOUNT
then @myRowCOUNT would always be zero. That's because it's reflecting the number of rows effected by the SET @myERROR = @@ERROR statement that preceeds it.

Thanks,

Arun.

Create Workbook - Single sheet

In some case if you wnat to copy some cell values to a new workbook, in that case you dont want to create a workbook with three sheets(by default), create a workbook with single sheet using the code
------------ ' Workbooks.Add(xlWBATWorksheet) '---------------
this code create a workbook with single sheet.

What is Microsoft .NET?

What is Microsoft .NET?
Microsoft .NET is the Microsoft strategy for connecting systems, information, and devices through Web services so people can collaborate and communicate more effectively. .NET technology is integrated throughout Microsoft products, providing the capability to quickly build, deploy, manage, and use connected, security-enhanced solutions through the use of Web services.

What are Web Services?
Web services are small, reusable applications that help computers from many different operating system platforms work together by exchanging messages. Web services are based on industry protocols that include XML (Extensible Markup Language), SOAP (Simple Object Access Protocol), and WSDL (Web Services Description Language). These protocols help computers work together across platforms and programming languages.
From a business perspective, Web services are used to reenable information technology so that it can change, move, and adapt like other aspects of a business. They not only connect systems, they can help connect people with the information they need, within the software applications they are used to using, and wherever they happen to be.
Microsoft offers a complete range of software that helps organizations and individuals benefit from Web service-based connectivity. These include theMicrosoft Visual Studio .NET 2003 developer tools, the Windows Server System that hosts Web services, and familiar desktop applications such as the Microsoft Office System that "consume" Web services.

What are the Benefits of .NET?
.NET technologies use Web services to help enhance the computing experience with highly integrated communications and information. Because .NET includes the core technologies for building Web services, it benefits everyone: individual users, organizations, and developers. •
.NET benefits organizations by helping them get the most out of their existing technology investments while creating new ways to implement powerful, cost-effective information technology that will meet future needs. .NET technologies and Web services can be used to integrate even the most disparate computing environments. .NET frees organizations from the confines of proprietary technology, providing the flexibility and scalability that can help organizations connect their existing IT systems and build a foundation for the next wave of computer technology. .NET and Web services can help organizations lower operating costs by helping connect systems; increase sales by helping employees access the right information when and where they need it; integrate services and applications with customers and partners; and lower the costs of information technology with tools that help developers quickly create new solutions to address business issues. •
.NET benefits individuals by helping provide a more personal and integrated computing experience. .NET-enabled computing is centered on the user–not on the features of the software or hardware. The user's experience becomes very customizable and provides integrated data and customized interactions that work well with a wide range of computing hardware, such as Pocket PCs, Smartphones, laptops, and other devices. The .NET-based experience enhances the mobile computing experience so user's can get their information regardless of their location. For example, it can take place on a desktop computer, in the car on a Smartphone, or at the store on a Pocket PC. •
.NET benefits developers by providing a tightly integrated set of tools for building and integrating Web services. Visual Studio .NET and the .NET Framework provide comprehensive development platforms that offer the best, fastest, and most cost-effective way to build Web services. With Visual Studio .NET and the .NET Framework, developers can take advantage of a programming model designed from the ground up for creating Web services in a highly productive, multilanguage environment. With scalable, high-performance execution, the .NET tools allow developers to use existing skills to create a wide range of solutions that work across a broad array of computing devices. .NET also provides a foundation for building Service Oriented Architectures (SOA).

What are Service Oriented Architectures (SOA)?
SOA describes an information technology architecture that enables distributed computing environments with many different types of computing platforms and applications. Web services are one of the technologies that help make SOAs possible. As a concept, SOA has been around since the 1980s, but many early IT technologies failed to achieve the goal of linking different types of applications and systems. By making early investments with .NET, Microsoft has helped provide the building blocks that today are putting many enterprise customers on the path to successfully implementing SOAs. With SOAs, companies can benefit from the unimpeded flow of information that is the hallmark of connected systems. What are Web Services

Enhancements for Microsoft .NET (WSE)?
WSE is an add-on to Microsoft Visual Studio .NET and the Microsoft .NET Framework that helps developers build greater security features into Web services using the latest Web services protocol specifications and standards. With WSE 2.0 developers can create security-enhanced connected systems that help improve business processes within–and beyond–corporate trust boundaries and create new revenue-generating opportunities.

What is a Smart Client?
Smart clients are client applications that consume Web services and reside on user hardware such as desktop PCs, laptops, Pocket PCs, and Smartphones. They are easily deployed and managed and provide an adaptive, responsive, and rich interactive experience by taking advantage of the computing resources on the device and intelligently connecting to distributed data sources.

What is .NET Passport?
.NET Passport is a Web-based service that is designed to make signing in to Web sites fast and easy. Passport enables participating sites to authenticate a user with a single set of sign-in credentials, alleviating the need for users to remember numerous passwords and user names.

What are the Current Microsoft Products and Technologies That Use .NET?

Microsoft .NET provides everything that is needed to develop and deploy a Web service-based IT architecture: servers to host Web services; development tools to create Web services; applications to use them; and a network of more than 35,000 Microsoft partners to help organizations deploy and manage them. .NET technologies are supported throughout the family of Microsoft products, including the Windows Server System, the Windows XP desktop operating system, and the Microsoft Office System. And .NET technologies will play an even larger role in future versions of Microsoft products.

Who is Using .NET?
Organizations worldwide are implementing .NET technologies and Web services to create connected businesses and to help individuals communicate and collaborate more effectively. For example, Honeywell, GlaxoSmithKline, Sony, Dollar Rent A Car, Farmers Insurance, and the United States Postal Service are just a few of the well-known names that are using .NET. To read more about .NET technology in use today, visit the Microsoft .NET Case Study site.

How Do I Find a Microsoft Partner to Help Me Connect My Organization Using .NET?
Microsoft Certified Partners are independent companies that can provide you with the highest levels of technical expertise, strategic thinking, and hands-on skills. Microsoft Certified Partners encompass a broad range of expertise and vendor affiliations. Their real-world perspective can help you prioritize and effectively deliver your technology solutions.

Dynamically Create a tool bar in Excel VBA

In Excel the tool bar button is used to call the xla file, for that i written a code to create the tool bar dynamicallyno need to add the xla file manually in to the workbook.Place the code in the one seperate module, the code is written in the "Auto_Open" event of excel workbook,the below code will create a tool bor with two buttons.Take a look at that and try it once,

'--------------------------------------------------------------------
Public Sub Auto_Open()


Dim cm As Office.CommandBar
On Error Resume Next
'Delete the toolbar if it already exists
Application.CommandBars("NewToolBar").delete
On Error GoTo 0
'Create the new toolbar each time this workbook opens

Set cm = Application.CommandBars.Add("NewToolBar", , , Temporary:=True)

With cm.Controls

'Add a button to the tool bar
With .Add(msoControlButton)
.Caption = "New Button"
'While click the button in the tool bar
'the method name entered here will called
.OnAction = "EntertheMethodName"
.FaceId = 1763
.Style = msoButtonIcon
End With

'Can add more than one button in the same tool bar
With .Add(msoControlButton)
.Caption = "New Button1"
'While click the button in the tool bar
'the method name entered here will called
.OnAction = "EntertheMethodName1"
.FaceId = 2882
.Style = msoButtonIcon
End With

End With

cm.Position = msoBarTop
cm.Visible = True

End Sub
'--------------------------------------------------------------------
Cheers........

Creating a simple XML file in VB.net

The below is code for creating the XML file in VB.net.
copy and paste the code in on module and see how its work...
----------------------------------------------------------------
Private sub CreateXMLFile (strRootDirectoryName as string)

Dim mywriter As System.Xml.XmlTextWriter
mywriter = New System.Xml.XmlTextWriter(strRootDirectoryName & "AddressType.xml", Nothing)
mywriter.WriteStartDocument()
mywriter.WriteStartElement("AddressType")
mywriter.WriteElementString("Company", strCompany)
mywriter.WriteElementString("Address1", strAddress1)
mywriter.WriteElementString("Address2", strAddress1)
mywriter.WriteElementString("City", strCity)
mywriter.WriteElementString("State", strState)
mywriter.WriteElementString("Country", strCountry)
mywriter.WriteElementString("Zip", strZip)
mywriter.WriteElementString("Fax", strFax)
mywriter.WriteElementString("Phone", strPhone)
mywriter.WriteElementString("Email", strEmail)
mywriter.WriteEndDocument()
mywriter.Flush()
mywriter.Close()

End Sub
----------------------------------------------------------------
Hope this is more helpfull for XML beginners.

Workbook Opened or not (VBA code)

The below is the VBA code to check whether the workbook is already opened or not.

If the file is already opened means we dont want ot open the file no need to open it again,

just activate the file and proceed.

if the file is open the function returns true other wise false.

copy and pase the below code in one module.

Public Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select

End Function