Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

ASP.net web forms C# SQL issue

Hi

I am using the following code to load a ListBox in my ASP.net web forms C# project. When I added "CAST([Stock Level] As varchar)" it caused a large number of spaces in the ListBox that had not been there before. Why would this be?


        public void Load_Parts_ListBox()

        {

            try

            {


;

                //string oSQL = "Select [Part ID], [Part No] + ' | ' + [Description]  As Part From [Parts]";

                string oSQL = "Select [Part ID], [Part No] + ' | ' + [Description] + ' | ' + CAST([Stock Level] As varchar)  As Part From [Parts]";


                if (txtSearchPartNo.Text != "")

                {

                    oSQL = oSQL + " Where [Part] Like '%" + SQLConvert(txtSearchPartNo.Text) + "%'";

                }


                oSQL = oSQL + " Order By [Part]";




                Console.Write(oSQL);

                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);

                SqlCommand cmd = new SqlCommand(oSQL, con);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable dt = new DataTable();

                adp.Fill(dt);

                ListBox_Part.DataSource = dt;


                //LB.DataTextField = "Part No";

                ListBox_Part.DataTextField = "Part";

                //LB.DataValueField = "Part ID";

                ListBox_Part.DataValueField = "Part ID";


                ListBox_Part.DataBind();

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message + " Error loading Partlist");

            }

            finally

            {

            }

        }


SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

Not easy to see why.

Just what sort of field is 'Stock Level' ?  Integer, float....



You could also just try:

string oSQL = "Select [Part ID], TRIM([Part No] + ' | ' + [Description] + ' | ' + CAST([Stock Level] As varchar))  As Part From [Parts]";

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

@Andy If the OP requires trimming directly in the query, then this implies his database isn't well organized and requires normalization.


Although, I agree with @Ryan. Just debug your code and "step-over" the line that fetches the data, and then paste it here or provide a screenshot from the IDE. If the query returns it with spaces, we know there's a problem with the query (or database) that should be looked into.


Just a suggestion: you should use an ORM such as Entity Framework that would allow you to work easily with databases instead of manually opening and closing connections and writing your queries like that.

@Jonathan.  I agree with the bad design part should TRIM actually work but note I did that as a hack after asking some questions.  (I've seen loads of questions at EE concerning SQL query problems where the root is the database design was bad.)  Sometimes a hack is required sherely because redesigning the DB is not something that takes a couple of minutes.

Glad to help! Cheers :)