locked
Problem to exception RRS feed

  • Question

  • Dear,

    There is error like

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock,


    due to these codes below. Why?
                        using (SqlCommand sqlCmd = new SqlCommand("SELECT cast(ROW_NUMBER() OVER (ORDER BY house_id ASC) as int) serial_id, house_id, rent_type, house_name, area, area_unit2, clicked_times, area_unit, area_size, isnull(construct_area_size,0) construct_area_size, isnull(request_times,0) request_times, release_period_from, CONVERT(varchar,register_date,105) register_date,CONVERT(varchar,upd_date,105) upd_date, address1, address2, price, price_curr, ltrim(rtrim(cast(raised_by_user_id as varchar))) raised_by_user_id FROM house_tab where isnull(raised_by_user_id,0)=@raiser and isnull(deleted,'')<>'Y'", sqlConn))
                        {
                            sqlConn.Open();
                            sqlCmd.CommandType = CommandType.Text;
    
                            sqlCmd.Parameters.AddWithValue("@raiser", System.Web.HttpContext.Current.Session["user_id"]);
                            SqlDataAdapter sqlAd = new SqlDataAdapter(sqlCmd);
                            DataTable sqlDt = new DataTable();
                            sqlAd.Fill(sqlDt);
                            sqlConn.Close();
                            if (null != sqlDt)
                            {
                                houseList = sqlDt
                                                .AsEnumerable()
                                                .Select(dataRow => new HouseData
                                                {
                                                    serial_id = dataRow.Field<int>("serial_id"),
                                                    house_id = dataRow.Field<int>("house_id"),
                                                    house_name = dataRow.Field<string>("house_name"),
                                                    address1 = dataRow.Field<string>("address1"),
                                                    address2 = dataRow.Field<string>("address2"),
                                                    area = dataRow.Field<string>("area"),
                                                    area_unit = dataRow.Field<string>("area_unit"),
                                                    rent_type = dataRow.Field<string>("rent_type"),
                                                    register_date = dataRow.Field<string>("register_date"),
                                                    upd_date = dataRow.Field<string>("upd_date"),
                                                    area_unit2 = dataRow.Field<string>("area_unit2"),
                                                    clicked_times = dataRow.Field<decimal>("clicked_times"),
                                                    construct_area_size = dataRow.Field<decimal>("construct_area_size"),
                                                    request_times = dataRow.Field<decimal>("request_times"),
                                                    area_size = dataRow.Field<decimal>("area_size"),
                                                    price = dataRow.Field<decimal>("price"),
                                                    price_curr = dataRow.Field<string>("price_curr"),
                                                    raised_by_user_id = dataRow.Field<string>("raised_by_user_id"),
                                                })
                                                .ToList();
                            }
                        }
                    }


    Many Thanks & Best Regards, Hua Min


    Monday, September 10, 2018 1:49 AM

All replies

  • Post the entire exception message (and stack trace).. cause the given fragment contains only the signatures, which does not really tell something about the error.

    Comments:

    1) Consider using views. such large SQL statements should be placed in the database.

    2) Your ISNULL() usage indicates a model issue. Missing constraints and default values.

    3) Do NOT format dates in the backend. Do this in your code, using the current locale.

    4) Don't use CAST(.. AS VARCHAR(255)) without specifying a maximum length.

    5) When it is a OLTP system, "deleted" should be a state. Otherwise it should be at least a BIT.

    Monday, September 10, 2018 8:48 AM
  • The same extracted part was working fine in other project. How to locate the real reason per current exception encountered?

    Many Thanks & Best Regards, Hua Min

    Monday, September 10, 2018 9:44 AM
  • Post the entire exception message (and stack trace)..
    Monday, September 10, 2018 10:27 AM
  • Hi HuaMin Chen,

    Do you execute the SQL statement in SSMS and check if it works. if it works well in SSMS, please share related table structure and records, which could reproduce the issue on our side.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 11, 2018 2:33 AM
  • See my comment #1 :)
    Tuesday, September 11, 2018 8:58 AM
  • The exception is due to

    sqlAd.Fill(sqlDt);

    why?

    Many Thanks & Best Regards, Hua Min

    Wednesday, September 12, 2018 3:02 AM
  • Hi HuaMin Chen,

    It will beneficial to resolve the issue if you could share a bit more information that could reproduce the issue, which I mentioned before.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 12, 2018 5:30 AM
  • Hi,

    Any other advice/help to this?


    Many Thanks & Best Regards, Hua Min

    Tuesday, November 6, 2018 8:31 AM