• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

Cannot get query to function

Hello,
I am attempting to write a select query and am getting an error in my syntax

Code Follows:
-----------------
 protected void ddl_Building_SelectedIndexChanged(object sender, EventArgs e)
        {

            String strConnString = ConfigurationManager.ConnectionStrings["DetentionDWLConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);

            ///Retrieve Data in Grid as a filter based on Building ID////
            string query = "SELECT (Building_ID, Building_Code, Floor_ID, Floor_Name, Shift_ID, Shift_Name, Jail_Key, Position_Title, Position_Desc, Position_Key, Composite_Key)  where Building_ID=@Building_ID";
            SqlDataAdapter adp = new SqlDataAdapter(query, con);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            gv_position_existing.DataSourceID = null;
            gv_position_existing.DataSource = ds;
            gv_position_existing.DataBind();

            /////////////



            ddl_floor.Items.Clear();
            ddl_floor.Items.Add(new ListItem("--Select Floor--", ""));
            ddl_shift.Items.Clear();
            ddl_shift.Items.Add(new ListItem("--Select Shift--", ""));

            ddl_floor.AppendDataBoundItems = true;
            String strQuery = "select Floor_ID, Floor_Name from CodeTable_Floor " + "where Building_ID=@Building_ID";
           
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.AddWithValue("@Building_ID", ddl_Building.SelectedItem.Value);
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strQuery;
            cmd.Connection = con;

            try
            {
                con.Open();
                ddl_floor.DataSource = cmd.ExecuteReader();
                ddl_floor.DataTextField = "Floor_Name";
                ddl_floor.DataValueField = "Floor_ID";
                ddl_floor.DataBind();
                if (ddl_floor.Items.Count > 1)
                {
                    ddl_floor.Enabled = true;
                }
                else
                {
                    ddl_floor.Enabled = false;
                    ddl_shift.Enabled = false;

                    Bind_gv_building();


                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
                lbl_building.Text = " " + ddl_Building.SelectedItem.Text;

               
            }
        }

------------

the error is
------------
Incorrect syntax near ','.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','.

Source Error:


Line 70:             SqlDataAdapter adp = new SqlDataAdapter(query, con);
Line 71:             DataSet ds = new DataSet();
Line 72:             adp.Fill(ds);
Line 73:             gv_position_existing.DataSourceID = null;
Line 74:             gv_position_existing.DataSource = ds;
 

Source File: C:\Legacy\LegacyIntranetSolution\Detention_DWL\DetentionWatch.aspx.cs    Line: 72


-------------
0
Codeaddict7423
Asked:
Codeaddict7423
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hi.

This query looks incorrect:
 "SELECT (Building_ID, Building_Code, Floor_ID, Floor_Name, Shift_ID, Shift_Name, Jail_Key, Position_Title, Position_Desc, Position_Key, Composite_Key)  where Building_ID=@Building_ID";

Note you don't have a FROM clause.

Would expect:
 "SELECT Building_ID, Building_Code, Floor_ID, Floor_Name, Shift_ID, Shift_Name, Jail_Key, Position_Title, Position_Desc, Position_Key, Composite_Key FROM [some table name] where Building_ID=@Building_ID";

Also, I would make sure that the @Building_ID is being properly passed as a VARCHAR parameter and so that its text is not being parsed as SQL, i.e., you need the resulting query to be Building_ID = 'some id' or Building_ID = 1.  If it is a comma delimited list of building ids, that may cause the issue.

To verify, I would write out the value of the queries to screen or check via debugging.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now