How to bind Datalist control from code behind ?

Solution :

This blog will explain you, how you can bind Datalist control from code behind.
First you need to add Datalist control in .aspx file, you can do this by draging the control form the toolbox or you can past e the below code in .aspx file.

Then you need to bind this control from the database, you can do this by assigning Datasource of control to dataset, find code for this also below.


Place below code in HTML file.

<asp:DataList ID="dlPhotos" runat="server" RepeatColumns="3" CellSpacing="0" CellPadding="5"
            RepeatDirection="Horizontal" Width="100%">
            <ItemStyle VerticalAlign="Top" />
            <ItemTemplate>
                <table cellspacing="0" cellpadding="0" width="100%" border="0" align="center">
                    <tr>
                        <td align="center" class="cellheightfifteen">
                            <a id="lnk<%#Container.DataItem("iProductImageID")%>" href="javascript:void(0)" 

onclick="removePhoto('<%=Application("SiteURL")%>ajaxopes.aspx','<%#Container.DataItem("iProductImageID")%>','<%#container.da

taitem("sImageFileName")%>')">
                                Remove Photo </a><span class="redlink" id="spn<%#Container.DataItem("iProductImageID")%>"
                                    style="display: none;"></span>
                        </td>
                    </tr>
                    <tr>
                        <td class="heightfive">
                        </td>
                    </tr>
                    <tr>
                        <td align="center">
                            <table id="tbl<%#Container.DataItem("iProductImageID")%>" 

width="<%=Application("THUMBIMAGEWIDTH") + 10%>"
                                height="<%=Application("THUMBIMAGEHEIGHT") + 10%>" border="0" cellspacing="0"
                                cellpadding="0" class="tblbox1">
                                <tr>
                                    <td align="center" id="tdImage" runat="server">
                                        <img 

src='<%=Application("SiteURL")%>Images/ThumbleImages/<%#container.dataitem("sImageFileName")%>?<%=now.tostring%>'
                                            alt='Loading...' />
                                    </td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                    <tr>
                        <td align="center" class="cellheightfifteen">
                            <input type="radio" id="opt<%#Container.DataItem("iProductImageID")%>" name="rdPhoto"
                                onclick="setPhotoId('<%#Container.DataItem("iProductImageID")%>')" />
                        </td>
                    </tr>
                </table>
            </ItemTemplate>
        </asp:DataList>

Place below code in Code behind.

Code in VB.Net

If Not dsImage Is Nothing Then
       If dsImage.Tables(0).Rows.Count > 0 Then
          dlPhotos.DataSource = dsImage
          dlPhotos.DataBind()
       End If
End If

OR

Code in C#.Net

if ((dsImage != null)) 
{
        if (dsImage.Tables(0).Rows.Count > 0) 
    {
            dlPhotos.DataSource = dsImage;
            dlPhotos.DataBind();
        }
}

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com

Populate Datagrid control using Stored Procedure.

Please follow below steps.

Step 1: Create Stored Procedure, see below code and modify according to your need.

CREATE PROCEDURE [dbo].[GetProducts] 
   (
       @CategoryID int
   ) 
AS
SELECT ProductID, ProductName FROM Products WHERE CategoryID = @CategoryID

Step 2: Please see below code and explaination for each line.

Complete Code

SqlConnection conn = new SqlConnection("Data 
Source=localhost;Database=Northwind;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("GetProducts", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
dataGrid1.DataSource = ds;
dataGrid1.DataMember = "Products";

Explanation of above code line by line

Make sqlconnection using your connectionstring

SqlConnection conn = new SqlConnection("Data 
Source=localhost;Database=MyDB;Integrated Security=SSPI");

Declare sqlcommand and pass your Stored Procedure name and make connection to it.
All pass all the parameter to Stored Procudure.

SqlCommand command = new SqlCommand("GetProducts", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1;

Declare sqldataadapter and assign command to this,

SqlDataAdapter adapter = new SqlDataAdapter(command);

Declare dataset and fill dataset using sqldataadapter and table name "Products".

DataSet ds = new DataSet();
adapter.Fill(ds, "Products");

Fill datagrid by dataset ds,

dataGrid1.DataSource = ds;
dataGrid1.DataMember = "Products";

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com

Insert Form data in database using Stored Procedure

Please follow below steps.

Step 1: Create Stored Procedure, see below code and modify according to your need.

CREATE PROCEDURE [dbo].[InsertUser] (
    @User varchar(50),
    @Pass varchar(50)
) AS
INSERT INTO Users VALUES(@Username, @Password)

Step 2: Please see below code and explaination for each line.

Complete Code

string user = ... // get username from user
string pass = ... // get password from user

SqlConnection conn = new SqlConnection("Data 
Source=localhost;Database=MyDB;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("InsertUser", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@User", SqlDbType.VarChar).Value = user;
command.Parameters.Add("@Pass", SqlDbType.VarChar).Value = pass;
conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();

Explanation of above code line by line

Assign your text box value to this variables

string user = ... // get username from user
string pass = ... // get password from user

Make sqlconnection using your connectionstring

SqlConnection conn = new SqlConnection("Data 
Source=localhost;Database=MyDB;Integrated Security=SSPI");

Declare sqlcommand and pass your Stored Procedure name and make connection to it.
All pass all the parameter to Stored Procudure.

SqlCommand command = new SqlCommand("InsertUser", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@User", SqlDbType.VarChar).Value = user;
command.Parameters.Add("@Pass", SqlDbType.VarChar).Value = pass;

Open connection to database and run your store procedure, thus you can insert your form data to database succussfully.

conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com

Manually Reset Identity Column Value in SQL Server

Problem : If you are creating any table and using an identity column to that table, then at first time, when you insert any row, then identity value will start like 1,2,3... and so on..

But if you delete all the row from the table and when you try to insert new row in same table, the identity value will start from last identity value.

For Example the last identity was 6, so after deleting all data, when you insert new row, the identity value will be 7.

So to reset the identity Column value, you can use this below code.

Syntax

DBCC CHECKIDENT ( <table name>,RESEED,<new value>)

QUERY 

DBDD CHECKIDENT ('tbl_rajesh',RESEED,1)

Explaination :
table name = Your table name should be entered here
RESEED = this is used to reset your identity value.
new value = Starting identity value

If you want to check current identity value of any table, you can use below query.

DBCC CHECKIDENT (’tablename’, NORESEED)

Explaination :
table name = Your table name should be entered here
NORESEED = This will avoid RESEED function.

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com