How to give reference of other table to current table in fields?

Hello friends,

Thanks for all your support, Please read this new post to know how to give reference of other table to current table in fields.

You can find if any table with this name is exist in database of not, if you found delete it and create this one.

To do this operation use this code,

--Table Name: XYZ_SongRequest

    IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('XYZ_SongRequest'))
        DROP TABLE XYZ_SongRequest
    GO

Now by using below code, you can create reference of other table let say, XYZ_User, XYZ_City, XYZ_Rj to table XYZ_SongRequest

--Table Name: XYZ_SongRequest

CREATE TABLE XYZ_SongRequest (
    iRequestID            BIGINT            NOT NULL PRIMARY KEY IDENTITY(1,1),
    iMemberId            BIGINT            NOT NULL CONSTRAINT FK_SongRequest_iMemberId References XYZ_User(iMemberID),
    iCityID                INT                NOT NULL CONSTRAINT FK_SongRequest_iCityID References XYZ_City(iCityID),
    iRjId                INT                NOT NULL CONSTRAINT FK_SongRequest_iRjId References XYZ_Rj(iRJid),
    sRequest            VARCHAR(1000)    NOT NULL, 
    dRequestDate        DATETIME        NOT NULL DEFAULT GETDATE(),
    dProcessDate        DATETIME        ,
    sStatus                Varchar(20)        CHECK(sStatus in ('Pending','Approved')) NOT NULL DEFAULT 'Pending' 
)
GO

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
e-Procurement Technologies Ltd (India)
www.abcprocure.com

How to create Stored Procedure in SQL Server 2005 or 2008 ?

Solution :

You can directly copy below code highlight and paste in sql server query window and modify according to your need.

/**********************************************************************************
SP Name : XYZ_SP_ProgramOperation
Created by : Rajesh Singh
Created Date : 13th April 2010
Modified Date : 15th April 2010 
*********************************************************************************/

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID=OBJECT_ID('XYZ_SP_ProgramOperation'))
DROP PROCEDURE XYZ_SP_ProgramOperation
GO 
create PROCEDURE XYZ_SP_ProgramOperation
(
    @sOperation varchar(100),
    @iProgramID INT=NULL,
    @sProgramName varchar(100)=NUll,
    @sSlogan varchar(100)=NUll,
    @sStartTime varchar(100)=NUll,
    @sEndTime varchar(100)=NUll,
    @sDetail varchar(MAX)=NUll,
    @iCityID INT=NUll,
    @sStatus varchar(20)=NUll
    
)
AS
IF @sOperation='SELECT'
            BEGIN
            SELECT * FROM [XYZ_Program] WHERE sStatus='Active'
            END
ELSE IF @sOperation='SELECTPROGRAMBYID'
            BEGIN
            SELECT XYZ_Program.*, XYZ_Rj.sUserName,XYZ_Rj.sPhoto, XYZ_Rj.sRjName,(select sCityName from XYZ_City where iCityId=XYZ_Program.iCityID) as sCityName,XYZ_RjProgRel.iRjId
            FROM   XYZ_Program INNER JOIN
            XYZ_RjProgRel ON XYZ_Program.iProgramID = XYZ_RjProgRel.iProgramID INNER JOIN
            XYZ_Rj ON XYZ_RjProgRel.iRjId = XYZ_Rj.iRJid WHERE XYZ_Program.iProgramID=@iProgramID
            END
ELSE IF @sOperation='UPDATE'
            BEGIN
            UPDATE [XYZ_Program]
               SET [sSlogan] = @sSlogan
                  ,[sStartTime] = @sStartTime
                  ,[sEndTime] = @sEndTime
                  ,[sDetail] = @sDetail
                  ,[iCityID] = @iCityID
                  ,[sStatus] = @sStatus
             WHERE XYZ_Program.iProgramID=@iProgramID
            END


GO

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
e-Procurement Technologies Ltd (India)
www.abcprocure.com

Where to put Connnectionstring in web.config file.

Hello bloggers,

Most of the developers have big confusion that, where to put connection string in web.config file,
So this article will teach you which portion you can use to write your connection string.
When you will open web.config file, you will find this tag

<connectionStrings />

Here what you can do is, just replace this string with below connectionstring.

Use this connectionstring when database is on other server or other computer.

<connectionStrings>
  
      <add name="conn" connectionString="Data Source=XYZ;Initial Catalog=DatabaseName;user id=UserId;password=Password; Max Pool Size=7500;Connect Timeout=200;" providerName="System.Data.SqlClient"/>
      
</connectionStrings>

when database is in your computer, you can use local database server.

<connectionStrings>

<add name="conn" connectionString="Data Source=localhost;Database=MyDB;Integrated Security=SSPI " providerName="System.Data.SqlClient"/>
      
</connectionStrings>

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
e-Procurement Technologies Ltd (India)
www.abcprocure.com

Encryption of Connection Strings Inside the Web.config

Motive : To Hide Database login details, whenever error occors in web.config file.

Explaination : you might have notice, if somethings goes wrong in web.config file, then yellow page appears with line number error and it is also possible it shows us connection string with database login details. So to hide that details, you need do few line code.



Your initial connectionstring will be like this :

<connectionstrings>
<add name="ConnectionString" connectionString="Data Source=JAVAL\SQLEXPRESS;Initial Catalog=WebTesting;Integrated Security=True"
      providerName="System.Data.SqlClient" />
</connectionStrings>

After Encryption, your final connectionstring will be like this:

<connectionstrings configProtectionProvider="DataProtectionConfigurationProvider">
<encrypteddata>
<cipherdata>
<ciphervalue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAQ4qWFMYeZkaDZbyFqQ9I7QQAAAACAAAAAAADZgAAqAAAABAAAADtQc027fRNV8+BRcNmjmWVAAAAAASAAACgAAAAEAAAAMHuX/gjWbtiwU0J8TSVSTewAQAA0CQYUjmcHyCzAvzE6GJN3oGZOwd5h0lwcPfhLjpgbySErnGHYNvWf58o4ieqhMFGduY0Sr9i8yqlDezbqyCrRQIri1qfl+6n2BFy3hQkZoTithLuilyCo+hOSq5rTP62aUm3FIjra1owCQmBV8/a34fu9zSgr4RRNQg1sg8yPFqHCssZPcA65MlXdJncTa6xBb6UpznFzcDF6N8z7LHPjF9baQEoeITw4TCY6yhM/6f7WIVWe2157GaBLGtoAuZP7z9ASrDKXmhQWQfsuwnaHcrE3JEUE/2yDmzxZFP+dY6FSqRzpWZoftgZnAIAnk2XeVsjqRy4zFNErFkzqxqhHNOMViodViRFZAJ5LRPAp6x3BkL/3F6d13Oa4gsbAlVY4+x60HdoItVXLoz+8Z3UNLfybN25cpQKSStw3TKnqSbzqtVyRjq1l+E/3lQSUxXSvgnA4mioGpaMAnMxNHnH3yRWiaYF37VfAHjCy8JhIJCtEBCmCUHh957KcggiRDXd2kpSb9M8CfHQQ4YJvm/YfOV3YwojIR6P2PHe5B6sizTuJxt/GTdKJ0nlVNxGr6TkFAAAAMbNEVnc4h11d1G1lz1pqOTtg2PJ</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>

To do this please follow steps below,

First add this two namespace above your page:
Imports System.Configuration
Imports System.Web.Configuration


then Add this function anywhere in your code behind page :

Public Shared Function webencrypt()
Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration("~")
Dim configSection As ConfigurationSection = config.GetSection("connectionStrings")

configSection.SectionInformation.ProtectSection("DataProtectionConfigurationProvider")
config.Save()
End Function


and call this function in pageload, like this
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Call webencrypt()
Dim sqlstring As SqlConnection
'sqlstring = New System.Configuration.ConfigurationManager.ConnectionStrings("conn").ConnectionString
sqlstring = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
End Sub

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
e-Procurement Technologies Ltd (India)
www.abcprocure.com

<%@ Page Language="C#"%>

<% User us = new User();
   bool result;

   if (Request["ctl00$ContentPlaceHolder1$txtusername"] != null)
   {
       result = us.checkUserAvailability(Request["ctl00$ContentPlaceHolder1$txtusername"].ToString(), "username");
       if (result)
       {
           Response.Write("true");
       }
       else
           Response.Write("false");
   }
   else if (Request["ctl00$ContentPlaceHolder1$txtemail"] != null)
   {
       result = us.checkUserAvailability(Request["ctl00$ContentPlaceHolder1$txtemail"].ToString(), "email");
       if (result)
       {
           Response.Write("true");
       }
       else
           Response.Write("false");
   }
   
 %>

source code :

<script type="text/javascript" language="javascript">
        $().ready(function() {
            $("#aspnetForm").validate({
                rules: {
                    ctl00$ContentPlaceHolder1$txtfirstname: {
                        required: true
                    },
                    ctl00$ContentPlaceHolder1$txtlastname: {
                        required: true
                    },
                    ctl00$ContentPlaceHolder1$txtpassword: { required: true, minlength: 5 },
                    ctl00$ContentPlaceHolder1$txtemail: {
                        required: true,
                        remote: "Controller/checkUserAvail.aspx",
                        email: true
                    },
                    ctl00$ContentPlaceHolder1$txtaddress1: { required: true }
                },
                messages: {
                    ctl00$ContentPlaceHolder1$txtfirstname: {
                        required: "<br/>Please enter First Name"
                    },
                    ctl00$ContentPlaceHolder1$txtlastname: {
                        required: "<br/>Please enter Last Name"
                    },
                    ctl00$ContentPlaceHolder1$txtpassword: {
                        required: "<br/>Please enter a password",
                        minlength: "<br/>Minimum password length is 5 characters"
                    },
                    ctl00$ContentPlaceHolder1$txtemail: {
                        required: "<br/>Please enter a email",
                        email: "<br/>Please enter a valid email",
                        remote: jQuery.format("<br/>Email address is already in use")
                    },
                    ctl00$ContentPlaceHolder1$txtaddress1: { required: "<br/>Please enter address"
                    }
                }
            });
        });         
    </script>

Thank
Rajesh singh