Problem to exception

Hi,

I have got
   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, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResul

Open in new window

with these codes
            List<HouseData> houseList = new List<HouseData>();
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn3"].ConnectionString))
                {
                    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();
                        }
                    }
                }
            }

Open in new window

LVL 12
HuaMin ChenProblem resolverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gr8gonzoConsultantCommented:
Not sure what you're asking here. You're getting an exception - what does the exception say?
HuaMin ChenProblem resolverAuthor Commented:
Exception details are shown in above.
David ToddSenior Database AdministratorCommented:
Hi,

Can you step into the error and tell us which line? Is the error SQL or c#

Regards
  David
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

Molnár IstvánHelpDesk / ProgrammerCommented:
1. Check the connection string if it is correct.
2. Check if input values are not too long to be stored in the designated columns.
HuaMin ChenProblem resolverAuthor Commented:
Error/exception is due to

sqlAd.Fill(sqlDt);

Open in new window


how to identify which field is having length issue?
David ToddSenior Database AdministratorCommented:
Hi,

Why not simply execute the query from SSMS and take a look at what its returning?

Regards
  David
HuaMin ChenProblem resolverAuthor Commented:
No problem to return record by the query.
Molnár IstvánHelpDesk / ProgrammerCommented:
-add a breakpoint at line 14   sqlAd.Fill(sqlDt) and check if the session for user_id is correct
-try like this System.Web.HttpContext.Current.Session["user_id"].ToString();

I tried like this and it's working fine with my database:
        private void LoadDB()
        {
            //List<HouseData> houseList = new List<HouseData>();
            try
            {
                        using (SqlConnection sqlConn = new SqlConnection("Data Source=.;Initial Catalog=mydatabase;Integrated Security=True")) //modified
                       {
                            using (SqlCommand sqlCmd = new SqlCommand("select * from aspnet_Users where UserId like @raiser", sqlConn)) //modified
                            {
                                sqlConn.Open();
                                sqlCmd.CommandType = CommandType.Text;

                                sqlCmd.Parameters.AddWithValue("@raiser", "9399F78F-F801-42FC-8C68-AB6989B46B0B"); // this input should be checked
                                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();
                //                }
                //            }
                        }
                    }

            }
            catch (Exception ex)
            {
            }
        }

Open in new window

gr8gonzoConsultantCommented:
You showed us the stack trace, not the exception details. The "SqlException exception" object should have a message telling you what's wrong. So either inspect the SqlException object via the debugger, or show it in a try/catch (which you should probably have anyway):

try
{
   ...adapter/fill/etc...
}
catch(SqlException e)
{
   // Log the error somehow or put a breakpoint here so you can look at it

   // Stolen from https://msdn.microsoft.com/en-us/library/aa326286(v=vs.71).aspx
   string errorMessage = "Message: " + e.Message + "\n" +
                            "Source: " + e.Source + "\n" +
                            "State: " + e.State + "\n" +
                            "Procedure: " + e.Procedure + "\n";

      System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
      log.Source = "My Application";
      log.WriteEntry(errorMessage);  Console.WriteLine(sqlException.
}

Open in new window

HuaMin ChenProblem resolverAuthor Commented:
Thanks to all.

Molnar,
I currently encountered problem/exception on line

sqlAd.Fill(sqlDt);

Open in new window


and how to capture value of columns?
Molnár IstvánHelpDesk / ProgrammerCommented:
I think the problem is with the following line (I suppose that is returning an object instead of a string), but without the exception message is hard to tell, try to modify the following line:
System.Web.HttpContext.Current.Session["user_id"]

To

System.Web.HttpContext.Current.Session["user_id"].ToString();

Ps. Try to show us the exception message:
like gr8gonzo said, add this block after closing the try paranthesis:
}//closing try block

EDITED:
            
            catch (Exception ex)
            {
                string errorMessage = "Message: " + ex.Message +
                    "StackTrace: " + ex.StackTrace +
                    "InnerException: " + ex.InnerException 
                         "Source: " + ex.Source;
                //MessageBox.Show(errorMessage); //sorry, I thought that is WinForms
                   // Response.Write(errorMessage); //uncomment if alert not working
                    Response.Write("<script language=javascript>alert(\'" + errorMessage + "\');</script>");
            }

Open in new window


When you get an exception it will show you a Alert box. Please create a screenshot of the Alert box and upload it.

Edit: Added InnerExeption to code
Molnár IstvánHelpDesk / ProgrammerCommented:
if raised_by_user_id it's an integer in the database try this :

sqlCmd.Parameters.Add("@raiser",SqlDbType.Int).Value=Convert.ToInt32(System.Web.HttpContext.Current.Session["user_id"].ToString());

instead

sqlCmd.Parameters.AddWithValue("@raiser", System.Web.HttpContext.Current.Session["user_id"]);

PS:
1. Can you please upload a screenshot  of the house_tab table design?
2. Can you show us how you tested the query from SSMS?
3. Upload a screenshot from Alert
HuaMin ChenProblem resolverAuthor Commented:
I try to capture user_id by code below but I got empty value
                        SqlCommand cmd3i = new SqlCommand("insert into err values('22uu" + System.Web.HttpContext.Current.Session["user_id"] + "', 2,getdate()) ", conn3i);

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.