How to convert the contents of a Column to Proper case/ Title Case

If for any reason you want to change the text data that is stored in the database to Title case, follow the following steps:

1. In Microsoft SQL Server Management Studio, Select the Database and Create a New Query

Now Paste the following code. This will create a function.

CREATE FUNCTION dbo.TitleCaseText(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS 
BEGIN
  DECLARE @position INT
    WHILE IsNull(@position,Len(@input)) > 1
     SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))), 
@position = charindex(' ',@input,IsNull(@position,1)) + 1
RETURN (@input)
END

2. To just display the contents of the column in Title case, use the following syntax

SELECT dbo.TitleCaseText(ColumnName) FROM TableName

3. To update the contents of the column to Title Case, use the following syntax

UPDATE TableName SET ColumnName = dbo.TitleCaseText(ColumnName)

Avoiding Date Format errors while writing SQL Queries

Many times we face the task of displaying records falling between two dates. We ask the user to enter the date range. The date format for user is dd/MM/yyyy (in India).

First assign the dates from the Text Box to some variables of Date type

  Dim tmpDateFrom As Date
  Dim tmpDateTo As Date
  Try
     tmpDateFrom = Date.ParseExact(txtDateFrom.Text, "dd/MM/yyyy",                           Globalization.CultureInfo.InvariantCulture)
     tmpDateTo = Date.ParseExact(txtDateTo.Text, "dd/MM/yyyy",                               Globalization.CultureInfo.InvariantCulture)
  Catch ex As Exception
     lblErrorMessage.Text = "Enter Date in dd/MM/yyyy format only!"
     pnlError.Visible = True
     Exit Sub
  End Try

When we need to insert the above dates in SQL query use the following syntax:

"WHERE (CONVERT(date, DOJ)>='" & Format(tmpDateFrom, "yyyy-MM-dd") & "' AND CONVERT(date, DOJ) <='" & Format(tmpDateTo, "yyyy-MM-dd") & "')"


When using parameters, use the following syntax:
 cmdInsert.Parameters.Add("@DateFrom", SqlDbType.Date)
 cmdInsert.Parameters("@DateFrom").Value = tmpDateFrom
 cmdInsert.Parameters.Add("@DateTo", SqlDbType.Date)
 cmdInsert.Parameters("@DateTo").Value = tmpDateTo

How to deploy Web Application that uses Crystal Report?

We faced lot of problems when we created Crystal Reports and deployed the pages on the server. The page would display but the Report wouldn't.

Please follow the following steps to display the Crystal Reports on pages deployed on the server.

1. Go to “C:\inetpub\wwwroot\aspnet_client\system_web\4_0_30319″ on the web server.
2. Copy “crystalreportviewers13″ folder & paste it to the application

Now add the following lines in the web.config file

  <configSections>
    <sectionGroup name="businessObjects">
      <sectionGroup name="crystalReports">
        <section name="rptBuildProvider" type="CrystalDecisions.Shared.RptBuildProviderHandler, CrystalDecisions.Shared, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304, Custom=null"/>
        <section  name="crystalReportViewer" type="System.Configuration.NameValueSectionHandler" />
      </sectionGroup>
    </sectionGroup>
  </configSections>
   <businessObjects>
    <crystalReports>
      <rptBuildProvider>
        <add embedRptInResource="true" />
      </rptBuildProvider>
      <crystalReportViewer>
        <add  key="ResourceUri" value="/crystalreportviewers13" />
      </crystalReportViewer>
    </crystalReports>

  </businessObjects>

This should work.

Display all constraints in all Tables of a Database Using SQL Query

Sometimes you may want to list all the constraints like Primary Keys, Foreign Keys etc. of all the tables in a database. Use the following SQL Query to achieve that


SELECT OBJECT_NAME(object_id) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
ORDER BY OBJECT_NAME(object_id)

Changing the Schema of All Tables

Sometimes we require to change the schema of all the tables in a database.

For example, you have a database whose tables are in dpu schema and now you want to change the schema to dbo,  use the folowing code:


DECLARE @sql Varchar(8000), @table Varchar(1000), @SchemaToChange Varchar(1000), @NewSchema Varchar(1000)

SET @SchemaToChange = 'dpu'
SET @NewSchema = 'dbo'

WHILE exists(SELECT * FROM sys.tables WHERE schema_name(schema_id) = @SchemaToChange)
BEGIN
SELECT @table = name from sys.tables WHERE object_id in
       (SELECT min(object_id) FROM sys.tables WHERE schema_name(schema_id) = @SchemaToChange)

SET @sql = 'alter schema ' + @NewSchema + ' transfer ' + @SchemaToChange + '.' + @table

EXEC(@sql)

END

Caution when using UNION

The UNION Clause is used to combine the output of two SQL Queries. But beware, using UNION will remove duplicate rows from the output. If you need to have all the rows from both the queries, use UNION ALL clause.

Example:

(SELECT A.CardNo as EntryCode, A.CardNo as BillNo, A.CardDate As BillDate,C.CustomerName As Details, ' ' As Inward,  B.Quantity As Outward, 'OnCard' as TransType FROM OnCard as A, OnCardDetails As B, MasterCustomers as C WHERE  A.CardNo=B.CardNo AND A.CustomerCode=C.CustomerCode AND B.ProductCode=28935)
UNION ALL
(SELECT A.BillNo as EntryCode, A.BillNo, A.BillDate, A.CustomerName As Details, ' ' As Inward, B.Quantity As Outward, 'Sale' as TransType FROM BillSale As A, BillSaleDetails as B WHERE A.BillNo=B.BillNo  AND B.ProductCode=28935 )

Display Primary Key Using SQL Query

If you want to know the primary key of any table, use the following SQL Query

SELECT * FROM sysobjects WHERE xtype='pk' AND parent_obj IN (SELECT id FROM sysobjects WHERE name='tablename')