RSS

Monthly Archives: August 2010

Get Maximum ID From Database after Insertion in storeprocedure


this is sp which return the maximum value of auto generated Primary key and return and used in other detail table for foreign key.

ALTER PROCEDURE [dbo].[SP_InsertUpdateRegistrationInfo]
@RegistrationID int,
@RegistrationCode varchar(100),
@Title varchar(25),
@FName varchar(25),
@LName varchar(25),
@MName varchar(25),
@FamilyName varchar(100),
@Gender varchar(10),
@DOB datetime,
@Address1 varchar(100),
@Address2 varchar(100),
@City varchar(50),
@State varchar(50),
@ZIPCode varchar(50),
@Country varchar(50),
@Phone varchar(50),
@Mobile varchar(50),
@ReferringSource varchar(50),
@RefereeName varchar(50),
@RefereeAddress varchar(50),
@CourseID int ,

@CampusID int ,
@Status int,
@RegistrationSource varbinary(50),
@Religion varchar(50),
@MaritalStaus varchar(50),
@PassportNo varchar(100),
@VisaNo varchar(50),
@VisaStartDate datetime,
@VisaEndDate datetime,
@Nationality varchar(50),
@PicturePath varchar(50),
@EnteredBy uniqueidentifier,
@LastModifiedBy uniqueidentifier,
@EnteredDate datetime,
@ShiftID int,
@ModifiedDate datetime,
@Email varchar(100)

AS

DECLARE @ReturnValue int

DECLARE @SetVisaStartDate datetime

IF (@RegistrationID IS NULL or @RegistrationID=0) — New Item

BEGIN

insert into tblRegistration
(RegistrationCode,
Title,
FName,
LName,
MName,
FamilyName,
Gender,
DOB,
Address1,
Address2,
City,
State,
ZIPCode,
Country,
Phone,
Mobile,
ReferringSource,
RefereeName,
RefereeAddress,
CourseID,

CampusID,
Status,
RegistrationSource,
Religion,
MaritalStaus,
PassportNo,
VisaNo,
VisaStartDate,
VisaEndDate,
Nationality,
PicturePath,
EnteredBy,
LastModifiedBy,
EnteredDate,
ShiftID,
ModifiedDate,
Email)

values(

@RegistrationCode,
@Title,
@FName,
@LName,
@MName,
@FamilyName,
@Gender,
@DOB,
@Address1,
@Address2,
@City,
@State,
@ZIPCode,
@Country,
@Phone,
@Mobile,
@ReferringSource,
@RefereeName,
@RefereeAddress,
@CourseID,

@CampusID,
@Status,
@RegistrationSource,
@Religion,
@MaritalStaus,
@PassportNo,
@VisaNo,
@VisaStartDate,
@VisaEndDate,
@Nationality,
@PicturePath,
@EnteredBy,
@LastModifiedBy,
@EnteredDate,
@ShiftID,
@ModifiedDate,
@Email

)
SELECT @ReturnValue = SCOPE_IDENTITY()
END
ELSE
BEGIN
Update tblRegistration set

Title=@Title,
FName=@FName,
LName=@LName,
MName=@MName,
FamilyName=@FamilyName,
Gender=@Gender,
DOB=@DOB,
Address1=@Address1,
Address2=@Address2,
City=@City,
State=@State,
ZIPCode=@ZIPCode,
Country=@Country,
Phone=@Phone,
Mobile=@Mobile,
ReferringSource=@ReferringSource,
RefereeName=@RefereeName,
RefereeAddress=@RefereeAddress,
CourseID=@CourseID,

CampusID=@CampusID,
Status=@Status,
RegistrationSource=@RegistrationSource,
Religion=@Religion,
MaritalStaus=@MaritalStaus,
PassportNo=@PassportNo,
VisaNo=@VisaNo,
VisaStartDate=@VisaStartDate,
VisaEndDate=@VisaEndDate,
Nationality=@Nationality,
PicturePath=@PicturePath,
EnteredBy=@EnteredBy,

ShiftID=@ShiftID,
ModifiedDate=@ModifiedDate,
Email=@Email

where RegistrationID=@RegistrationID

SELECT @ReturnValue = SCOPE_IDENTITY()
END

IF (@@ERROR != 0)
BEGIN
RETURN -1
END
ELSE
BEGIN
SELECT @ReturnValue
END

which return the Maximum ID and how to get in Asp.net Following here………..

which is Dal Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Data.SqlTypes;
/// <summary>
/// Summary description for DALRegisrationOnline
/// </summary>
public class DALRegisrationOnline
{

#region “property”
public int RegistrationID { get; set; }
public  int RegistrationCode{get;set;}
public  string Title{get;set;}
public  string FName{get;set;}
public  string LName{get;set;}
public  string MName{get;set;}
public  string FamilyName{get;set;}
public  string Gender{get;set;}
public DateTime DOB{get;set;}
public  string Address1{get;set;}
public  string Address2{get;set;}
public  string City{get;set;}
public  string State{get;set;}
public  string ZIPCode{get;set;}
public  string Country{get;set;}
public string Phone{get;set;}
public string Mobile{get;set;}
public  string ReferringSource{get;set;}
public  string RefereeName{get;set;}
public  string RefereeAddress{get;set;}
public int CourseID{get;set;}
public int DepartmentID{get;set;}
public int CampusID{get;set;}
public  int Status{get;set;}
public  string RegistrationSource{get;set;}
public  string Religion{get;set;}
public  string MaritalStaus{get;set;}
public  string PassportNo{get;set;}
public  string VisaNo{get;set;}
public SqlDateTime VisaStartDate { get; set; }

public SqlDateTime VisaEndDate { get; set; }
public  string Nationality{get;set;}
public  string PicturePath{get;set;}
public  Guid EnteredBy{get;set;}
public  Guid LastModifiedBy{get;set;}
public DateTime EnteredDate{get;set;}
public int ShiftID{get;set;}
public DateTime ModifiedDate{get;set;}
public string Fax { get; set; }
public string EMail { get; set; }

//Enrolment of Student Properties
public DateTime EnrollmentStartDate { get; set; }
public DateTime EnrollmentEndDate { get; set; }
public string EnrollmentCode { get; set; }
public string Remarks { get; set; }
public int StudentStatus { get; set; }
public string Duration { get; set; }
//Payment of Enrollment Student
public int StudentID{get;set;}
public DateTime PaymentDate { get; set; }
public DateTime CreditCardExpiryDate { get; set; }
public string PaymentMode { get; set; }
public string CreditCardNumber { get; set; }

public string NameOnCard { get; set; }
public string CreditCardType{get;set;}
public int PaymentID{get;set;}
public string CreditCardCVV{get;set;}
public string CCSecurityNo { get; set; }
public string BankName{get;set;}
public string ChequePayOrder{get;set;}
public DateTime ChequePayOrderDate { get; set; }
public decimal Amount{get;set;}
public string TransactonCode{get;set;}
public int PaymentStatus{get;set;}
public decimal TotalFee { get; set; }
public decimal Discount { get; set; }
public decimal downPayment { get; set; }
public decimal Balance { get; set; }
public int TotalInstallment { get; set; }
public int NumberInstallment { get; set; }

#endregion

public static int InsertUpdateRegisrationInfo(DALRegisrationOnline RegInfo)
{
Database db = DatabaseFactory.CreateDatabase();
object[] parameters = new object[] { RegInfo.RegistrationID, RegInfo.RegistrationCode, RegInfo.Title, RegInfo.FName, RegInfo.LName, RegInfo.MName, RegInfo.FamilyName, RegInfo.Gender, RegInfo.DOB, RegInfo.Address1, RegInfo.Address2, RegInfo.City, RegInfo.State, RegInfo.ZIPCode, RegInfo.Country, RegInfo.Phone, RegInfo.Mobile, RegInfo.ReferringSource, RegInfo.RefereeName, RegInfo.RefereeAddress, RegInfo.CourseID, RegInfo.CampusID, RegInfo.Status, RegInfo.RegistrationSource, RegInfo.Religion, RegInfo.MaritalStaus, RegInfo.PassportNo, RegInfo.VisaNo, RegInfo.VisaStartDate, RegInfo.VisaEndDate, RegInfo.Nationality, RegInfo.PicturePath, RegInfo.EnteredBy, RegInfo.LastModifiedBy, DateTime.Now, RegInfo.ShiftID, DateTime.Now, RegInfo.EMail };
DbCommand cmd = db.GetStoredProcCommand(“SP_InsertUpdateRegistrationInfo”, parameters);
// return db.ExecuteNonQuery(cmd);
return (int)db.ExecuteScalar(cmd);

}

}

this code to return the last insert record in database

return (int)db.ExecuteScalar(cmd);

///

where we access this function like here………
//Registration Form Step1
DALRegisrationOnline reg = new DALRegisrationOnline();
reg.RegistrationID = 0;

reg.CampusID = Convert.ToInt32(ddlCampusName.SelectedValue);
reg.CourseID = Convert.ToInt32(ddlCurse.SelectedValue);
reg.DepartmentID = Convert.ToInt32(ddlCampusName.SelectedValue);
reg.Title = ddltitle.SelectedValue;
reg.FName = txtFirstName.Text;
reg.LName = txtLastName.Text;
reg.MName = txtMiddleName.Text;
reg.Gender = ddlGender.SelectedItem.Text;
reg.DOB = Convert.ToDateTime(txtDOB.Text);

//string dobDate = txtDOB.Text;

//string dd, mm, yy, dDate;
//yy = dobDate.Substring(0, 4);
//mm = dobDate.Substring(5, 2);
//dd = dobDate.Substring(8, 2);

//dDate = mm + “/” + dd + “/” + yy;
//reg.DOB =Convert.ToDateTime(dDate);

reg.Address1 = txtAddress1.Text;
reg.Address2 = “”;

reg.Country = ddlCountry.SelectedItem.Text;
reg.City = txtCity.Text;
reg.State = txtState.Text;
reg.ZIPCode = txtZipCode.Text;
reg.Religion = Convert.ToString(ddlReligion.SelectedValue);
reg.MaritalStaus = ddlMatrial.SelectedItem.Text;
reg.Nationality = txtNationality.Text;
reg.Mobile = txtMobile.Text;
reg.Phone = txtTelephone.Text;
reg.Fax = txtFax.Text;
reg.EMail = txtEmail.Text;
reg.PassportNo = txtPassportNo.Text;
reg.VisaNo = txtVisaNo.Text;

//Img Upload
string _ImgName = string.Empty;
string folderPath = ConfigurationManager.AppSettings.Get(“ImagePath”);

string ImgPathimg = Request.PhysicalApplicationPath + folderPath;
//if (fuPicture != null && fuPicture.HasFile)
//{
if (_ImgUrl != “”)
{

// _ImgName = System.IO.Path.GetFileName(fuPicture.PostedFile.FileName);
_ImgName = _ImgUrl;

string name = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + “_”;

string _imgrnd = name + _ImgName;
ImgPathimg = ImgPathimg + _imgrnd;
string name1 = _imgrnd;
fuPicture.SaveAs(ImgPathimg);
reg.PicturePath = _imgrnd;

}
else {
reg.PicturePath = “”;

}
System.Data.SqlTypes.SqlDateTime sqldatenull;
sqldatenull = SqlDateTime.Null;

///End Img Upload
if (txtVisaStDate.Text != “”)
{
reg.VisaStartDate = Convert.ToDateTime(txtVisaStDate.Text);
}
else
{

reg.VisaStartDate = sqldatenull;//DateTime.Now; //Convert.ToDateTime(“0000:00:00 00:00:00”);

}
if (txtVisaEndDate.Text != “”)
{
reg.VisaEndDate = Convert.ToDateTime(txtVisaEndDate.Text);
}
else
{

reg.VisaEndDate = sqldatenull;// DateTime.Now;// Convert.ToDateTime(“0000:00:00 00:00:00”);

}

reg.ReferringSource = txtReferringSource.Text;
reg.RefereeName = txtRefereeName.Text;
reg.RefereeAddress = txtRefereeAddress.Text;
reg.Status =0;
reg.EnteredBy = (Guid)Membership.GetUser().ProviderUserKey;

reg.LastModifiedBy = (Guid)Membership.GetUser().ProviderUserKey;

int _RegistrationID = DALRegisrationOnline.InsertUpdateRegisrationInfo(reg);

After Get Maximum Registration ID then use in other table

DALRegistrationQualifications regQul = new DALRegistrationQualifications();
regQul.RegistrationID = _RegistrationID;

Advertisements
 
Leave a comment

Posted by on August 25, 2010 in SQL Query

 

Enter Null Values for DateTime From Asp.net To SQL Server database


introduction about Problem:

Solution 1

Inserting a null value to the DateTime Field from asp.net (C#,vb.Net)  in SQL Server is one of the most common issues giving various errors.And Problem To Handle.After Verious Website Visit and No Help find then resolve this Issue and want to share this code for everyone. Even if one enters null values the value in the database is some default value as 1/1/1900 12:00:00 AM.

The Output of entering the null DateTime based on the code would in most cases have errors as:

  • String was not recognized as a valid DateTime.
  • Value of type ‘System.DBNull’ cannot be converted to ‘String’.

Or No Error but DataTime entered in Database would be as 1/1/1900 12:00:00 AM
So lets write the code to enter null values in the DataBase.

The Db Image for data type of Date time  is as follows:

when you use Enterprise library  and you make different classes like BO,BLL,DAL Classes with property .

DAl Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Data.SqlTypes;

public class DALRegisrationOnline
{

public  string PassportNo{get;set;}
public  string VisaNo{get;set;}
public SqlDateTime VisaStartDate { get; set; }

public SqlDateTime VisaEndDate { get; set; }
public  string Nationality{get;set;}

}

That is the Function to Pass Stored Procedure

public static int InsertUpdateRegisrationInfo(DALRegisrationOnline RegInfo)
{
Database db = DatabaseFactory.CreateDatabase();
object[] parameters = new object[] { RegInfo.PassportNo, RegInfo.VisaNo, RegInfo.VisaStartDate, RegInfo.VisaEndDate, RegInfo.Nationality };
DbCommand cmd = db.GetStoredProcCommand(“SP_InsertUpdateRegistrationInfo”, parameters);
// return db.ExecuteNonQuery(cmd);
return (int)db.ExecuteScalar(cmd);

}

AspDotNet CS Class

DALRegisrationOnline reg = new DALRegisrationOnline();

System.Data.SqlTypes.SqlDateTime sqldatenull;
sqldatenull = SqlDateTime.Null;

///End Img Upload
if (txtVisaStDate.Text != “”)
{
reg.VisaStartDate = Convert.ToDateTime(txtVisaStDate.Text);
}
else
{

reg.VisaStartDate = sqldatenull;//DateTime.Now; //Convert.ToDateTime(“0000:00:00 00:00:00”);

}
if (txtVisaEndDate.Text != “”)
{
reg.VisaEndDate = Convert.ToDateTime(txtVisaEndDate.Text);
}
else
{

reg.VisaEndDate = sqldatenull;// DateTime.Now;// Convert.ToDateTime(“0000:00:00 00:00:00”);

}

int _RegistrationID = DALRegisrationOnline.InsertUpdateRegisrationInfo(reg);

after insertion the preview of db


The other Tutorial which help you…………………

Namespaces used

    System.Data.SqlClient/ System.Data.OleDb

  • System.Data.SqlTypes
  • Code for System.Data.SqlClient

C#

string
sqlStmt ;
string conString ;
SqlConnection cn =
null;
SqlCommand cmd =
null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) “;
conString = “server=localhost;database=Northwind;uid=sa;pwd=;”;
cn =
new SqlConnection(conString);
cmd =
new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(
new SqlParameter(“@FirstName”, SqlDbType.NVarChar, 11));
cmd.Parameters.Add(
new SqlParameter(“@LastName”, SqlDbType.NVarChar, 40));
cmd.Parameters.Add(
new SqlParameter(“@Date”, SqlDbType.DateTime));
sqldatenull = SqlDateTime.Null;
cmd.Parameters[“@FirstName”].Value = txtFirstName.Text;
cmd.Parameters[“@LastName”].Value = txtLastName.Text;
if (txtDate.Text == “”)
{
cmd.Parameters [“@Date”].Value =sqldatenull ;
//cmd.Parameters[“@Date”].Value = DBNull.Value;
}
else
{
cmd.Parameters[“@Date”].Value = DateTime.Parse(txtDate.Text);
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Record Inserted Succesfully”;
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

VB.NET

Dim sqlStmt As String
Dim
conString As String
Dim
cn As SqlConnection
Dim cmd As SqlCommand
Dim sqldatenull As SqlDateTime
Try
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) ”
conString = “server=localhost;database=Northwind;uid=sa;pwd=;”
cn =
New SqlConnection(conString)
cmd =
New SqlCommand(sqlStmt, cn)
cmd.Parameters.Add(
New SqlParameter(“@FirstName”, SqlDbType.NVarChar, 11))
cmd.Parameters.Add(
New SqlParameter(“@LastName”, SqlDbType.NVarChar, 40))cmd.Parameters.Add(New SqlParameter(“@Date”, SqlDbType.DateTime))
sqldatenull = SqlDateTime.Null
cmd.Parameters(“@FirstName”).Value = txtFirstName.Text
cmd.Parameters(“@LastName”).Value = txtLastName.Text
If (txtDate.Text = “”) Then
cmd.Parameters(“@Date”).Value = sqldatenull
‘cmd.Parameters(“@Date”).Value = DBNull.Value
Else
cmd.Parameters(“@Date”).Value = DateTime.Parse(txtDate.Text)
End If
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = “Record Inserted Succesfully”
Catch ex As Exception
Label1.Text = ex.Message
Finally
cn.Close()
End Try

Code for System.Data.SqlClient.

C#

string sqlStmt;
string conString ;
OleDbConnection cn =
null ;
OleDbCommand cmd =
null ;
try
{
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (?,?,?) “;
conString = “Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost”;
cn =
new OleDbConnection(conString);
cmd =
new OleDbCommand(sqlStmt, cn) ;
cmd.Parameters.Add(
new OleDbParameter(“@FirstName”, OleDbType.VarChar, 40));
cmd.Parameters.Add(
new OleDbParameter(“@LastName”, OleDbType.VarChar, 40));
cmd.Parameters.Add(
new OleDbParameter(“@Date”, OleDbType.Date));
cmd.Parameters[“@FirstName”].Value = txtFirstName.Text;
cmd.Parameters[“@LastName”].Value = txtLastName.Text;
if ((txtDate.Text == “”) )
{
cmd.Parameters[“@Date”].Value = DBNull.Value;
}
else
{
cmd.Parameters[“@Date”].Value = DateTime.Parse(txtDate.Text);
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Record Inserted Succesfully”;
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

VB.NET

Dim
sqlStmt As String
Dim
conString As String
Dim
cn As OleDbConnection
Dim cmd As OleDbCommand
Try
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (?,?,?) ”
conString = “Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost”
cn =
New OleDbConnection(conString)
cmd =
New OleDbCommand(sqlStmt, cn)
cmd.Parameters.Add(
New OleDbParameter(“@FirstName”, OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter(“@LastName”, OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter(“@Date”, OleDbType.Date))cmd.Parameters(“@FirstName”).Value = txtFirstName.Text
cmd.Parameters(“@LastName”).Value = txtLastName.Text
If (txtDate.Text = “”) Then
cmd.Parameters(“@Date”).Value = DBNull.Value
Else
cmd.Parameters(“@Date”).Value = DateTime.Parse(txtDate.Text)
End If
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = “Record Inserted Succesfully”
Catch ex As Exception
Label1.Text = ex.Message
Finally
cn.Close()
End Try

Add your Comment…..


 
Leave a comment

Posted by on August 25, 2010 in ASP Dot Net C#

 

Get document Extension from the Sql To Display in Gridview


This code for Testing Propose in sql Query Analyzer

This code for Testing Propose in sql Query Analyzer

declare @path varchar(2000)

set @path=’20108211632495_CustomPaging Code C-Sharp.docx’

select substring(@path,charindex(‘.’,@path)+1,len(@path))

Description

Declare Variable to Store the Image or document Name with Extenshion

Then Save document name in variable

Get The Ex of the Document

Substring Syntax:

SUBSTRING ( expression ,start ,length )

Arguments

expression

Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.

start

Is an integer that specifies where the substring begins.

length

Is a positive integer that specifies how many characters or bytes of the expression will bereturned. If length is negative, an error is returned.

SELECT au_lname, SUBSTRING(au_fname, 1, 1)

FROM authors

ORDER BY au_lname

Here is the result set:

au_lname

—————————————- –

Bennet                                   A

Blotchet-Halls                           R

Carson                                   C

DeFrance                                 M

del Castillo                             I

Yokomoto                                 A

(23 row(s) affected)

CHARINDEX

Syntax


CHARINDEX ( expression1 ,expression2 [ , start_location ] )

Arguments


expression1

Is an expression that contains the sequence of characters to be found. expression1 is an expression of the character string data type category.

expression2

Is an expression, typically a column searched for the specified sequence. expression2is of the character string data type category.

start_location

Is the character position to start searching for expression1 in expression2. If start_location is not specified, is a negative number, or is zero, the search starts at the beginning of expression2. start_location can be of type bigint.

Return Types


bigint if expression2 is of the varchar(max), nvarchar(max) or varbinary(max) data types, otherwise int.

Examples


The following code example returns the position at which the sequence of characters bicycle starts in the DocumentSummary column of the Document table.

USE AdventureWorks;

SELECT CHARINDEX(‘bicycle’, DocumentSummary)

FROM Production.Document

WHERE DocumentID = 3;

Here is the result set.

———–

48

The following example uses the optional start_location parameter to start looking for bicycle in the fifth character of the DocumentSummary column.

SELECT CHARINDEX(‘bicycle’, DocumentSummary, 5)

FROM Production.Document

WHERE DocumentID = 3;

Here is the result set.

———–

48

The following example shows the result set when expression1 is not found within expression2.

SELECT CHARINDEX(‘bike’, DocumentSummary)

FROM Production.Document

WHERE DocumentID =6;

Here is the result set.

———–

0

The following example uses the COLLATE function.

SELECT CHARINDEX ( ‘Test’, ‘Das ist ein Test’  COLLATE Latin1_General_BIN);

Solution#2 With Query

This is Actual Query to get the Extension of Document which save in db

SELECT DocumentID,

DocumentTitle,

substring(DocumentPath,charindex(‘.’,DocumentPath)+1,len(DocumentPath)) DocumentType,

DocumentPath,

EnteredDate,

EnteredBy,

ModifiedDate,

ModifiedBy,

case DocumentStatus

when 1 then ‘Active’

else

‘Inactive’

end DocumentStatus

FROM tblDocument

Thank for visit…………..if this code for helpful for you then kindly comments……

Sajjad Ahmed

Sofware Eng(ITBeams(Pvt) Ltd Lahore)


 
1 Comment

Posted by on August 23, 2010 in SQL Query

 

How To Add a Serial No. Column to your GridView or Auto Generate Row Number(Serial No) in GridView


Solution#1(Tested)

<asp:GridView  runat=”server” id=”gvstdudent”>
<Columns>
<asp:TemplateField Header Text=”SR.No”>
<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<asp:BoundField DataField=”FName” HeaderText=”First Name” />
<asp:BoundField DataField=”LName” HeaderText=”Last Name”/>

</asp:GridView>

Description

Auto Generate Row Number in GridView in asp.net. This Code explains how to add auto generate serial No  in a gridview dynamically using This code.

You need only one Template and write this code in ItemTemplate.

<%# Container.DataItemIndex + 1 %>

Step 1 : Drag GridView from toolbox in  web page.

Step 2 : Open properties of the Gridview and Click on Columns than add Template Column. if you want to set AutoGenerateColumns Property to fasle.

Step 3: Now open .aspx   .cs file(Serverside code behind File) and you can add HeaderTemplate and ItemTemplate of Newly add Template column and add this lines of code inside ItemTemplate Tag

<%# Container.DataItemIndex + 1 %>

After adding template Template column and above code inside itemTemplate tag your TemplateField code will look like this

<asp:TemplateField>
<HeaderTemplate>
Row No.
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>

See the full GridView Code

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” >
<Columns>
<asp:TemplateField>
<HeaderTemplate>
Row No.
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField=”FName” HeaderText=”First Name” />
<asp:BoundField DataField=”LName” HeaderText=”Last Name”/>
</Columns>
</asp:GridView>

Here is the picture if you run the application

Solution#2

protected

int i = 1;

protected void  gvstdudent 1_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

e.Row.Cells[0].Text = Convert.ToString(GridView1.PageIndex * GridView1.PageSize + i);

i++;

}

}

<asp:TemplateField HeaderText=”Sr. No.”>

<ItemTemplate>

<asp:Label ID=”Label1″ runat=”server”></asp:Label>

</ItemTemplate>

</asp:TemplateField>

Thanks………

 
1 Comment

Posted by on August 23, 2010 in ASP Dot Net C#

 

DataTable Create programmatically And Save Data pass to load gridview


Purpose: Why we Need you Share this Article…..

because we Need First Save data before database ,i can manipulate after accurate then click on save and save in db.

Some Text box and other control use and Add Button… After Click on Add Button the value of control save in datatable and datatable pass to gridview.the gridview load the data.after add maximum record then i click on final Save Button

the Gridview Load Data Save in Database.

.NET 2.0 and 3.0  it is possible to create a DataTable without creating a DataSet.you just  need  create the DataTable object:

Process 1:

First Create the Datatable Object with specific Name

DataTable dataTable = new DataTable(“Routine”);

Process 2:

Create a Function of make a Table

public void AddRoutine() {

DataColumn col0 = new DataColumn(“RoutineID”, typeof(int));
col0.ReadOnly = true;
col0.Unique = true;
col0.AutoIncrement = true;
DataColumn col1 = new DataColumn(“SubjectTitle”, typeof(string));
DataColumn col2 = new DataColumn(“Days”, typeof(string));
DataColumn col3 = new DataColumn(“LectureDate”, typeof(string));
DataColumn col4 = new DataColumn(“StartTime”, typeof(string));
DataColumn col5 = new DataColumn(“EndTime”, typeof(string));
DataColumn col6 = new DataColumn(“ClassType”, typeof(string));
DataColumn col7 = new DataColumn(“RoomFloor”, typeof(string));
DataColumn col8 = new DataColumn(“RoomNo”, typeof(string));
DataColumn col9 = new DataColumn(“RoomTitle”, typeof(string));
DataColumn col10 = new DataColumn(“Teacher”, typeof(string));

dataTable.Columns.AddRange(new DataColumn[] { col0, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10 });
Session[“Routine”] = dataTable;

}

Process 3:

create a button and write this on click Event.After Enter the record in Control and click button load data in gridview

protected void btnLoadGV_Click(object sender, EventArgs e)
{
//Create Routine Table
AddRoutine();
DataRow row;
DataTable dt = (DataTable)Session[“Routine”];

row = dt.NewRow();
txtRoomNo.Text = string.Empty;
txtRoomFloor.Text = string.Empty;
txtRoomTitle.Text = string.Empty;

row[“SubjectTitle”] = txtSubjTitle.Text;
row[“Days”] = ddlDays.SelectedItem.Text;
row[“LectureDate”] = txtLectureDate.Text;
row[“StartTime”] = txtStartTime.Text;
row[“EndTime”] = txtEndTime.Text;
row[“ClassType”] = ddlClassType.SelectedItem.Text;
row[“RoomFloor”] = txtRoomFloor.Text;
row[“RoomNo”] = txtRoomNo.Text;
row[“RoomTitle”] = txtRoomTitle.Text;
row[“Teacher”] = ddlTeacher.SelectedItem.Text;
dt.Rows.Add(row);
Session[“Routine”] = dt;

gvclassdetail.DataSource = dt;
gvclassdetail.DataBind();

}

Process 4

Load this DAta in Gridview

<asp:GridView ID=”gvclassdetail” runat=”server” AutoGenerateColumns=”False”
CellSpacing=”5″ CellPadding=”2″ DataKeyNames=”RoutineID”
onrowdeleting=”gvclassdetail_RowDeleting”>
<Columns>

<asp:CommandField ShowDeleteButton=”True” />

<asp:BoundField DataField=”RoutineID” HeaderText=”RoutineID” />
<asp:BoundField DataField=”SubjectTitle” HeaderText=”Subject Title” />
<asp:BoundField DataField=”LectureDate” HeaderText=”Date”  />
<asp:BoundField DataField=”StartTime” HeaderText=”Start Time” />
<asp:BoundField DataField=”EndTime” HeaderText=”End Time” />
<asp:BoundField DataField=”ClassType” HeaderText=”ClassType” />
<asp:BoundField DataField=”RoomFloor” HeaderText=”RoomFloor” />
<asp:BoundField DataField=”RoomNo” HeaderText=”RoomNo” />
<asp:BoundField DataField=”RoomTitle” HeaderText=”RoomTitle” />
<asp:BoundField DataField=”Teacher” HeaderText=”Teacher” />

</Columns>

<EmptyDataTemplate>
<asp:Label ID=”lblEmptyGV” runat=”server” Font-Bold=”True” ForeColor=”White”
Text=”No Records Found”></asp:Label>
</EmptyDataTemplate>

</asp:GridView>

Process 5

For Delete the Gridview Record from Datatable

protected void gvclassdetail_RowDeleting(object sender, GridViewDeleteEventArgs e)
{

DataTable del = (DataTable)Session[“Routine”];
del.Rows[e.RowIndex].Delete();
Session[“Routine”]=del;

gvclassdetail.DataSource = del;
gvclassdetail.DataBind();

}

Process 6:

After Load the Gridview then you Want to All Data of Gridview Save in Database then Write this Code at Second

Final SAve Button

DataTable dts = (DataTable)Session[“Routine”];
int restult=-1;
foreach (DataRow dataRow in dts.Rows)
{
DALRoutineManagement Routinemgn = new DALRoutineManagement();
Routinemgn.RoutineID = _Res;

Routinemgn.SubjectTitle = dataRow[“SubjectTitle”].ToString();
Routinemgn.Days = dataRow[“Days”].ToString();
Routinemgn.LectureDate = Convert.ToDateTime(dataRow[“LectureDate”].ToString());
Routinemgn.StartTime = dataRow[“StartTime”].ToString();
Routinemgn.EndTime = dataRow[“EndTime”].ToString();
Routinemgn.ClassType = dataRow[“ClassType”].ToString();
Routinemgn.RoomFloor = dataRow[“RoomFloor”].ToString();
Routinemgn.RoomNo = Convert.ToInt32(dataRow[“RoomNo”].ToString());
Routinemgn.RoomTitle = dataRow[“RoomTitle”].ToString();
Routinemgn.TeacherName = dataRow[“Teacher”].ToString();
restult = DALRoutineManagement.InsertUpdateRoutineDetail(Routinemgn);

}
if (restult > 0)
{
btnOk.Visible = true;
lblmsg.Visible = true;

}

End Tutorial

if solve your problem ,this kindly comments about this…or you need or any problem …send response me.

sajjad_mcs_iub@yahoo.com

 
Leave a comment

Posted by on August 11, 2010 in ASP Dot Net C#

 

passing multiple values in Querystring Using PostBackUrL


Some Time you Need Muliple parmater Pass to QueryStirng and Need Both of Value to Next Page for Update of Delete Record.And using Server Control Button Property Like PostBackUrl…..

<asp:Button ID=”Button1″ runat=”server” PostBackUrl='<%#String.Format(“update_routinemanagement.aspx?ClassID={0}&RoutineIDs={1}”,Eval(“Class_ID”),Eval(“RoutineID”))%>’
SkinID=”Edit” />

To Access The Query String at DestinationPage

if (Request.QueryString[“ClassID”] != null && Request.QueryString[“ClassID”].ToString() != string.Empty && Request.QueryString[“RoutineIDS”] != null && Request.QueryString[“RoutineIDS”].ToString() != string.Empty)
{
_RoutineID = int.Parse(Request.QueryString[“RoutineIDS”].ToString());
ViewState[“RoutineIDS”] = _RoutineID;
_ClassID = int.Parse(Request.QueryString[“ClassID”].ToString());
ViewState[“ClassID”] = _ClassID;

And Other Different Way From Other Website

Process 1
PostBackUrl='<%# string.format( "ViexProductFullDesc.aspx?ProductID={0}&code={1}",Eval("ProductID"),Eval("PRODUCTCODE"))) %>'

Process 2

<asp:ImageButton id="ImageButton1" runat ="server" ImageUrl ='<%# eval("IMAGEPATH") %>' PostBackUrl='<%# Eval("ProductID", "ViexProductFullDesc.aspx?ProductID={0}") %>' />

Process 3

<asp:ImageButton id="ImageButton1" runat ="server" ImageUrl ='<%# eval("IMAGEPATH") %>' PostBackUrl='<%# string.format(Eval("ProductID"),EVAL("PRODUCTCODE"), "ViexProductFullDesc.aspx?ProductID={0}&code={1}")) %>' />

But i Use Upper Link
 
3 Comments

Posted by on August 11, 2010 in ASP Dot Net C#