Problem to show list

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Hi,
Using these codes

        public static List<HouseData> GetHouseList()
        {
            List<HouseData> houseList = new List<HouseData>();
            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn3"].ConnectionString))
            {
                using (SqlCommand sqlCmd = new SqlCommand("SELECT house_name, address1, address2, price, price_curr, raised_by_user_id FROM house_tab", sqlConn))
                {
                    sqlConn.Open();
                    sqlCmd.CommandType = CommandType.Text;
                    SqlDataAdapter sqlAd = new SqlDataAdapter(sqlCmd);
                    DataTable sqlDt = new DataTable();
                    sqlAd.Fill(sqlDt);
                    sqlConn.Close();
                    if (null != sqlDt)
                    {
                        houseList = sqlDt
                                        .AsEnumerable()
                                        .Select(dataRow => new HouseData
                                        {
                                            house_name = dataRow.Field<string>("house_name"),
                                            address1 = dataRow.Field<string>("address1"),
                                            address2 = dataRow.Field<string>("address2"),
                                            price = dataRow.Field<decimal>("price"),
                                            price_curr = dataRow.Field<string>("price_curr"),
                                            raised_by_user_id = dataRow.Field<decimal>("raised_by_user_id"),
                                        })
                                        .ToList();
                    }
                }
            }
            return houseList;
        }
        ...
        public void ProcessRequest(HttpContext context)
        {
            string productId = string.Empty;
            //-- get the ID from query string you get from <img src tag
            if (null != context.Request.QueryString &&
                !string.IsNullOrEmpty(context.Request.QueryString["ImID"]))
                productId = context.Request.QueryString["ImID"];

            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=213461;Data Source=C:\inetpub\VS2012\DB1.accdb");
            try
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT [photo_file] FROM [user_pict] where [user_id]=@par_id", conn);
                cmd.Parameters.Add("@par_id", OleDbType.VarChar).Value = productId;
                byte[] bArray = (byte[])cmd.ExecuteScalar();
                if (bArray != null)
                    context.Response.BinaryWrite(bArray);
            }
            catch (System.Data.SqlClient.SqlException)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
        ...

function showData() {
    $.ajax({
        type: 'POST',
        url: 'Default.aspx/GetHouseList',
        contentType: 'application/json; charset=utf-8',
        dataType: 'json',
        success: function (response) {
            var html = "";
            $.each(response.d, function (index, house) {
                html += "<dl><dt>" + house.house_name + "</dt>";
                html += "<dd>" + house.address1 + "</dd>";
                html += "<dd>" + house.address2 + "</dd>";
                html += "<dd>" + house.price + " ";
                html += house.price_curr + "</dd>";
                html += "<dd>" + (typeof house.house_name != "undefined" ? "<img src='ImageHandler.ashx?ImID='" + house.raised_by_user_id + "></img>" : "") + "</dd>";
                html += "</dl> ";
            });
            if (html != "")
                $("#houses").html(html);
        },
        error: function (response) {
            alert(response.d);
        }
    });
}

Open in new window

I cannot show the list below
http://my-friend.co/Test_rec4/Default.aspx?userid=mc23

Any help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
productId = context.Request.QueryString["ImID"];

whats wrong / what is not working?
HuaMin ChenProblem resolver

Author

Commented:
I cannot see the relevant list. Do you know where the problem is?
HainKurtSr. System Analyst

Commented:
error


{"Message":"Specified cast is not valid.","StackTrace":"   at System.Data.DataRowExtensions.UnboxT`1.ValueField(Object value)\r\n   at Own_rec._Default.\u003cGetHouseList\u003eb__1(DataRow dataRow)\r\n   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at Own_rec._Default.GetHouseList()","ExceptionType":"System.InvalidCastException"}
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HainKurtSr. System Analyst

Commented:
http://my-friend.co/Test_rec4/Default.aspx/GetHouseList

you are not passing anything to this request
HainKurtSr. System Analyst

Commented:

price = dataRow.Field<decimal>("price"),
raised_by_user_id = dataRow.Field<decimal>("raised_by_user_id"),

change those to string and lets see if it makes any difference...
HuaMin ChenProblem resolver

Author

Commented:
I change userId to int below and have re-deployed the project but I still get the same problem and cannot show the list.
        public void ProcessRequest(HttpContext context)
        {
            //string userId = string.Empty;
            int userId = 0;
            //-- get the ID from query string you get from <img src tag
            if (null != context.Request.QueryString &&
                !string.IsNullOrEmpty(context.Request.QueryString["ImID"]))
                userId = Convert.ToInt32(context.Request.QueryString["ImID"]);

            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=213461;Data Source=C:\inetpub\VS2012\DB1.accdb");
            try
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT [photo_file] FROM [user_pict] where [user_id]=@par_id", conn);
                cmd.Parameters.Add("@par_id", OleDbType.Integer).Value = userId;
                byte[] bArray = (byte[])cmd.ExecuteScalar();
                if (bArray != null)
                    context.Response.BinaryWrite(bArray);
                    ...

Open in new window

I get the prompt like "undefined" when displaying the web page.
HainKurtSr. System Analyst

Commented:
did you check ID: 42173619

change decimals to string... Line 23 & 25 in your question post...
HainKurtSr. System Analyst

Commented:
also, why you are using different var names for same thing?

userId = Convert.ToInt32(context.Request.QueryString["ImID"]);
...
OleDbCommand cmd = new OleDbCommand("SELECT [photo_file] FROM [user_pict] where [user_id]=@par_id", conn);
cmd.Parameters.Add("@par_id", OleDbType.Integer).Value = userId;

it should be

user_id = Convert.ToInt32(context.Request.QueryString["user_id"]);
...
OleDbCommand cmd = new OleDbCommand("SELECT [photo_file] FROM [user_pict] where [user_id]=@user_id", conn);
cmd.Parameters.Add("@user_id", OleDbType.Integer).Value = user_id;

but this is not our issue here...
are you sure user_id is integer?
HuaMin ChenProblem resolver

Author

Commented:
But ImID is the relevant ID being used on the handler. I do no know why you suggest to change from userId to user_Id.
Sr. System Analyst
Commented:
I keep saying

price = dataRow.Field<decimal>("price"),
raised_by_user_id = dataRow.Field<decimal>("raised_by_user_id"),

change those decimals to string and lets see if it works... but you ignore I guess :)
HuaMin ChenProblem resolver

Author

Commented:
Very good Sir, I can see the list below but what is the reason to see the picture file on the list?
http://my-friend.co/Test_rec4/Default.aspx?userid=mc23
HainKurtSr. System Analyst

Commented:
what is the reason to see the picture file on the list?

I could not get this part...
what is needed and what is not working here?
HainKurtSr. System Analyst

Commented:
I see page is asking for this

http://my-friend.co/Test_rec4/ImageHandler.ashx?ImID=

  • page is not there
  • you do not pass any ImID here
HuaMin ChenProblem resolver

Author

Commented:
Thanks a lot. How to identify the reason to that no value has been passed to house.raised_by_user_id, of this line

                html += "<dd>" + (typeof house.house_name != "undefined" ? "<img src='ImageHandler.ashx?ImID='" + house.raised_by_user_id + "></img>" : "") + "</dd>";

Open in new window

while on the table, there is valid values to such column.
HainKurtSr. System Analyst

Commented:
try this

var dd = (house.house_name)?"<img src='ImageHandler.ashx?ImID=" + house.raised_by_user_id + "' />":"";
html += "<dd>" + dd + "</dd>"

Open in new window

HainKurtSr. System Analyst

Commented:
or maybe

var dd = (house.house_name != "")?"<img src='ImageHandler.ashx?ImID=" + house.raised_by_user_id + "' />":"";
html += "<dd>" + dd + "</dd>"

Open in new window

HuaMin ChenProblem resolver

Author

Commented:
Thanks a lot. I applied your codes
        success: function (response) {
            var html = "";
            $.each(response.d, function (index, house) {
                html += "<dl><dt>" + house.house_name + "</dt>";
                html += "<dd>" + house.address1 + "</dd>";
                html += "<dd>" + house.address2 + "</dd>";
                html += "<dd>" + house.price + " ";
                html += house.price_curr + "</dd>";
                var dd = (house.house_name != "") ? "<img src='ImageHandler.ashx?ImID=" + house.raised_by_user_id + "' />" : "";
                html += "<dd>" + dd + "</dd>";
            });
            if (html != "")
                $("#houses").html(html);
        },
        ...
                using (SqlCommand sqlCmd = new SqlCommand("SELECT house_name, address1, address2, price, price_curr, ltrim(rtrim(cast(raised_by_user_id as varchar))) raised_by_user_id FROM house_tab", sqlConn))
                {
                    sqlConn.Open();
                    sqlCmd.CommandType = CommandType.Text;
                    SqlDataAdapter sqlAd = new SqlDataAdapter(sqlCmd);
                    DataTable sqlDt = new DataTable();
                    sqlAd.Fill(sqlDt);
                    sqlConn.Close();
                    if (null != sqlDt)
                    {
                        houseList = sqlDt
                                        .AsEnumerable()
                                        .Select(dataRow => new HouseData
                                        {
                                            house_name = dataRow.Field<string>("house_name"),
                                            address1 = dataRow.Field<string>("address1"),
                                            address2 = dataRow.Field<string>("address2"),
                                            price = dataRow.Field<decimal>("price"),
                                            price_curr = dataRow.Field<string>("price_curr"),
                                            raised_by_user_id = dataRow.Field<string>("raised_by_user_id"),
                                        })
                                        .ToList();
                    }
                }
            }
            return houseList;
            ...

Open in new window

but I still get the same problem.
HainKurtSr. System Analyst

Commented:
your setup is wrong!!!

request works, but server cannot handle it...

http://my-friend.co/Test_rec4/ImageHandler.ashx?ImID=12


Server Error in '/Test_rec4' Application.
--------------------------------------------------------------------------------


The resource cannot be found.
  Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable.  Please review the following URL and make sure that it is spelled correctly.

 Requested URL: /Test_rec4/ImageHandler.ashx


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.36392
HuaMin ChenProblem resolver

Author

Commented:
How to correct this issue?
HainKurtSr. System Analyst

Commented:
i dont know :)
do you have that page?
did you setup your web.config correctly?

actually, this should be another question...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial