sql server query returning null

Question

I am new to asp.net and sql server i wrote a code to set my model and i just need a row so i wrote this code:

My model

public class FullPackages
{
    public int Id { get; set; }
    public int TypeId { get; set; }

    [Display(Name = "Package Type")]
    public string TypeName { get; set; } 
    public int AllowedSMS { get; set; }

    [Display(Name = "Time Span in Days")]
    public int? TimeSpan { get; set; }
    public decimal Price { get; set; }
}

My Method

    public ActionResult PackageDetails(int id = 0)
    {
        string sqlString = "select top 1 tblPackages.*, tblPackageTypes.Name from tblPackages join tblPackageTypes on tblPackages.TypeId = tblPackageTypes.Id where tblPackages.Id = "+id;

        SqlConnection con = new SqlConnection(cnxn); // cnxn is defined
        SqlCommand cmd = new SqlCommand(sqlString, con);
        SqlDataReader dsr;
        FullPackages package = new FullPackages();


            con.Open();

            dsr = cmd.ExecuteReader();  // this returns null value

                package.Id = dsr.GetInt32(0);
                package.TypeId = dsr.GetInt32(1);
                package.TypeName = dsr.GetString(5);
                package.AllowedSMS = dsr.GetInt32(2);
                package.TimeSpan = dsr.IsDBNull(3) ? 0 : dsr.GetInt32(3);
                package.Price = dsr.GetDecimal(4);
            con.Close();

        return View(package);
    }

when i remove top 1 from the sqlString it returns the values from the database but when i keep top 1 it returns null.And when i check the same sql with top 1 in sql server Mgmt Studio it returns single row.. Why is it so?? Please help me....

I guess this is not the working code, because ID is defined as int but is appended to the SQL Select string as if it were string. — NoChance
– NoChance, Commented Jan 6, 2014 at 5:03
@EmmadKareem no it works fine. i just appended the value of int to string. Also when i remove top 1 the code works fine. — Robz
– Robz, Commented Jan 6, 2014 at 5:04

Suraj Shrestha · Accepted Answer · 2014-01-06 06:13:26Z

1

You can do something like this instead of your code:

string sqlString = "select top 1 tblPackages.*, tblPackageTypes.Name from tblPackages join tblPackageTypes on tblPackages.TypeId = tblPackageTypes.Id where tblPackages.Id = " + id;
        SqlConnection con = new SqlConnection(cnxn); // cnxn is defined 

        SqlDataAdapter sda = new SqlDataAdapter(sqlString, con); 
        DataTable dt = new DataTable(); 
        sda.Fill(dt);
        FullPackages package = new FullPackages();

        package.Id = Convert.ToInt32(dt.Rows[0][0]); 
        package.TypeId = Convert.ToInt32(dt.Rows[0][1]);
        package.AllowedSMS = Convert.ToInt32(dt.Rows[0][2]); 
        package.TimeSpan = !DBNull.Value.Equals(dt.Rows[0][3]) ? Convert.ToInt32(dt.Rows[0][3]) : 0;
        package.Price = Convert.ToInt32(dt.Rows[0][4]);

answered Jan 6, 2014 at 6:13

Suraj Shrestha

1,8181 gold badge26 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robz Over a year ago

though the coding pattern is changed. This helped

Collectives™ on Stack Overflow

sql server query returning null

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related