How to display only last Record From data base in ado .net

store Procedure

Create Procedure [dbo].[Sp_User]
    (
    @Id int=null,
    @FullName varchar(50)=null,
    @Password varchar(50)=null,
    @Address varchar(100)=null,
    @Email varchar(50)=null,
    @Imgurl varchar(50)=null,
    @Activity varchar(50)=null
    )

    As Begin

    if(@Activity='Add')
    insert into registration
    (FullName,[Password],[Address],Email,Imgurl)
    values
    (@FullName,@Password,@Address,@Email,@Imgurl)


    if(@Activity = 'All')
    select top 1 FullName,Email,Password,imgurl from registration order by Id desc
    End

in User DAL

public List<User>All(ActivityMode a)
        {
            SqlConnection con = ConnectionDAL.SQL();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Sp_User";
            cmd.Parameters.AddWithValue("@activity", a.ToString());
            List<User> lst = null;
            con.Open();
            using (con)
            {
                SqlDataReader DR = cmd.ExecuteReader();
                if (DR.HasRows)
                {
                    lst = new List<User>();
                    while (DR.Read())
                    {
                        User obj = new User();
                        obj.Id = Convert.ToInt32(DR["Id"]);
                        obj.FullName = DR["FullName"].ToString();
                        obj.Email = DR["Email"].ToString();
                        obj.Password = DR["Password"].ToString();
                        obj.Address = DR["Address"].ToString();
                        obj.Imgurl = DR["Imgurl"].ToString();
                        lst.Add(obj);
                    }
                }
            }
            return lst;
        }

in Conrloar

public ActionResult ViewAllUser()

        {
            RegisterHandler th = new RegisterHandler();
            List<User> lst = new List<User>();
            lst = th.All();

            return View(lst);
        }

I want to display only the last record.

I`m running the project and getting this error.

Server Error in '/' Application.

ID

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: ID

Source Error:

Line 90: {
Line 91: User obj = new User();
Line 92: obj.Id = Convert.ToInt32(DR["ID"]);
Line 93: obj.FullName = DR["FullName"].ToString();
Line 94: obj.Email = DR["Email"].ToString();

How to resolve it? Please help me.

Answers


The reason that you are seeing this error is the stored procedure is not selecting all the columns that you are trying to read, you need to ensure the select list here:

select top 1 FullName,Email,Password,imgurl from registration order by Id desc

contains all the columns that you are reading here:

User obj = new User();
obj.Id = Convert.ToInt32(DR["Id"]);
obj.FullName = DR["FullName"].ToString();
obj.Email = DR["Email"].ToString();
obj.Password = DR["Password"].ToString();
obj.Address = DR["Address"].ToString();
obj.Imgurl = DR["Imgurl"].ToString();
lst.Add(obj);

As an aside, I'd suggest you create separate stored procedures to read and write data so that you can give independent permissions on them. You may also need to check for nulls on your reads

DR["<column>"]

Can result in DBNull.Value (if the column is nullable) as well as actual values and will error if you try and convert to an int in this case. You may also want to consider why you're selecting the "last user" as in a multi-threaded environment you may see unexpected results if you rely on that being the one previously inserted for example.


You are missing the Id Column in your Stored Procedure.

This is the correct sentence: select top 1 Id,FullName,Email,Password,imgurl from registration order by Id desc

Try this please.

Regards!


Problem is that you are trying to get it when there is no ID in select statement.

In below statement add Id in select query like

if(@Activity = 'All')
    select top 1 Id, FullName,Email,Password,imgurl from registration order by Id desc
    End

Need Your Help

JavaScript function's argument becomes undefined after the first use

javascript jquery arrays function arguments

I finally came up with code, which gets jSON data from file and now I'm trying to make some functions for that. Take a look at the code:

how to scroll text on label like marquee

iphone objective-c text marquee

I have text on label which is not appearing completely so i want text to scroll on label from left to right. Please don't suggest numberoflines property as a solution.