Problem to exception

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Not sure what you're asking here. You're getting an exception - what does the exception say?
HuaMin ChenProblem resolver

Author

Commented:
Exception details are shown in above.
David ToddSenior Database Administrator

Commented:
Hi,

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

Regards
  David
Ensure you’re charging the right price for your IT

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!

Molnár IstvánHelpDesk / Programmer

Commented:
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 resolver

Author

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 Administrator

Commented:
Hi,

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

Regards
  David
HuaMin ChenProblem resolver

Author

Commented:
No problem to return record by the query.
Molnár IstvánHelpDesk / Programmer

Commented:
-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

Commented:
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 resolver

Author

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 / Programmer

Commented:
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 / Programmer

Commented:
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 resolver

Author

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

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