none
System.Data.SqlClient.SqlException: Invalid column name 'ECC'. RRS feed

  • Question

  • Hello everyone,

    I'm having an issue with one of the pages that my company's website uses to view the employees that are in the database table. This code was written by another programmer before me and was left with some errors so any help that can be given would be greatly appreciated. The website is a asp.net C# written in Visual Studio 2010 with a back-end of sql server 2008.

    Here is the error:

    Source Error:
    Line 114:      commandString += "ORDER BY [LName] ASC;";
    Line 115:      SqlCommand command = new SqlCommand(commandString, conn);
    Line 116:      SqlDataReader reader = command.ExecuteReader();
    Line 117:      
    Line 118:      TableRow trow;

    Here is the rest of the page:

    using System;
    using System.Configuration;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.SqlClient;
    using System.Globalization;
    using System.Drawing;
    using CWIS;

    public partial class training_viewemployee : Page
    {  
      public string GetConnectionString()
      { // returns connnection string
        string strConnect = ConfigurationManager.ConnectionStrings["CWIS"].ConnectionString;
        return strConnect;
      }
     
      public string DateSQLFormat(string date)
      {
        if (date.Length != 0)
        {
          string new_date = null;
          string new_month = null;
          string new_day = null;
          string new_year = null;
          int counter = 0;
          while (date[counter].Equals('/') == false)
          { // loop to extract month from string
            // loop will exit when forward slash is encountered
            new_month += new string(date[counter], 1);
            counter++;
          }
          if (new_month.Length == 1)
          { // add leading zero to month number if necessary
            new_month = String.Concat("0", new_month);
          }
          counter++; // increment counter to go to next char after forward slash
          while (date[counter].Equals('/') == false)
          { // loop to extract day from string
            new_day += new string(date[counter], 1);
            counter++;
          }
          if (new_day.Length == 1)
          { // add leading zero to day number if necessary
            new_day = String.Concat("0", new_day);
          }
          counter++; // increment counter to go to next char after forward slash
          for(int i = 0; i < 4; i++)
          { // extract year from string
            new_year += new string(date[counter], 1);
            counter++;
          }
          
          // create string to return
          new_date = "'" + new_year + "-" + new_month + "-" + new_day + " 00:00:00.000" + "'";
          return new_date;
        }
        else
        {
          return "null";
        }
      }
     
      public string DateTimeFormatting(string date)
      {
        if (date.Length != 0)
        {
          int counter = 0;
          int startpos = 0;
          string new_date = null;
          string temp = null;
          while (counter < 2)
          {
            temp = date.Substring(startpos, 1);
            if (temp == "/")
            {  
              counter++;
            }
            new_date = new_date + temp;
            startpos++;
          }
          temp = date.Substring(startpos, 4);
          new_date = new_date + temp;
          return new_date;
        }
        else
        {
          return date;
        }
      }

      public void CreateTable(string strLOC)
      {
          SqlConnection conn = new SqlConnection();
          conn.ConnectionString = GetConnectionString();
          conn.Open();

          string commandString = "SELECT E.*, ECC.[Description] FROM [CW-IS].dbo.TR_Employees AS E JOIN [CW-IS].dbo.TR_ECC AS ECC ON E.[ECC] = ECC.[ECC]  ";
          switch (strLOC)
          {
            case "OH":
              commandString += "WHERE [Location] Like 'OH' ";
              break;
              
            case "TX":
              commandString += "WHERE [Location] Like 'TX' ";
              break;
                      
            case "WV":
              commandString += "WHERE [Location] Like 'WV' ";
              break;
          }
          commandString += "ORDER BY [LName] ASC;";
          SqlCommand command = new SqlCommand(commandString, conn);
          SqlDataReader reader = command.ExecuteReader();
          
          TableRow trow;
          TableCell tcell;
          string strActive;
          string strTemp;
          
          // create table headers
          trow = new TableRow();
          trow.Style["font"] = "bold 12px Arial";
          trow.Style["color"] = "black";
          tcell = new TableCell();
          tcell.Width = Unit.Parse("75px");
          tcell.Text = "Employee ID";
          trow.Cells.Add(tcell);
          tcell = new TableCell();
          tcell.Width = Unit.Parse("100px");
          tcell.Text = "Last Name";
          trow.Cells.Add(tcell);
          tcell = new TableCell();
          tcell.Width = Unit.Parse("100px");
          tcell.Text = "First Name";
          trow.Cells.Add(tcell);
          tcell = new TableCell();
          tcell.Width = Unit.Parse("75px");
          tcell.Text = "Hire Date";
          trow.Cells.Add(tcell);
          tcell = new TableCell();
          tcell.Width = Unit.Parse("50px");
          tcell.Text = "ECC";
          trow.Cells.Add(tcell);
          tcell = new TableCell();
          tcell.Width = Unit.Parse("200px");
          tcell.Text = "Job Title";
          trow.Cells.Add(tcell);
          tcell = new TableCell();
          tcell.Width = Unit.Parse("50px");
          tcell.Text = "Active";
          trow.Cells.Add(tcell);
          tcell = new TableCell();
          tcell.Width = Unit.Parse("50px");
          tcell.Text = "Temp";
          trow.Cells.Add(tcell);
          tcell = new TableCell();
          tcell.Width = Unit.Parse("75px");
          tcell.Text = "Location";
          trow.Cells.Add(tcell);
          tblTable1.Rows.Add(trow);
          
          string strEID;
          while (reader.Read())
          { // create table
            trow = new TableRow();
            trow.Style["font"] = "12px Arial";
            trow.Style["color"] = "black";
            tcell = new TableCell();
            strEID = String.Format("{0}", reader["Employee_ID"]);
            tcell.Text = "<a href='./training_viewemployee1.aspx?id=" + strEID + "'>" + strEID +"</a>";
            trow.Cells.Add(tcell);
            tcell = new TableCell();
            tcell.Text = String.Format("{0}", reader["LName"]);
            trow.Cells.Add(tcell);
            tcell = new TableCell();
            tcell.Text = String.Format("{0}", reader["FName"]);
            trow.Cells.Add(tcell);
            tcell = new TableCell();
            tcell.Text = DateTimeFormatting(String.Format("{0}", reader["Hire_Date"]));
            trow.Cells.Add(tcell);
            tcell = new TableCell();
            tcell.Text = String.Format("{0}", reader["ECC"]);
            trow.Cells.Add(tcell);
            tcell = new TableCell();
            tcell.Text = String.Format("{0}", reader["Description"]);
            trow.Cells.Add(tcell);
            tcell = new TableCell();
            strActive = String.Format("{0}", reader["Active_Employee"]);
            if (strActive  == "True")
            {
              tcell.Text = "Yes";
            }
            else
            {
              tcell.Text = "No";
            }  
            trow.Cells.Add(tcell);
            tcell = new TableCell();
            strTemp = String.Format("{0}", reader["Temp"]);
            if (strTemp == "True")
            {
              tcell.Text = "Yes";
            }
            else
            {
              tcell.Text = "No";
            }
            trow.Cells.Add(tcell);
            tcell = new TableCell();
            tcell.Text = String.Format("{0}", reader["Location"]);
            trow.Cells.Add(tcell);
            tblTable1.Rows.Add(trow);
          }
          reader.Close();
          conn.Close();
      }
     
      protected void ddlLocation_Changed(object sender, EventArgs e)
      {
        // delete old table
        int numrows = tblTable1.Rows.Count;
        for (int i = numrows; i > 0; i--)
        {
          tblTable1.Rows.RemoveAt(i);
        }
        CreateTable(ddlLocation.SelectedValue);
      }
     
      public void Page_Load(object sender, EventArgs e)
      {
        bool credcheck;
        
        CWIS.credentialcheck abc = new CWIS.credentialcheck();
        if (Session["uname"] == null || Session["pword"] == null)
        {
          credcheck = false;
        }
        else
        {
          credcheck = abc.CheckCredentials(GetConnectionString(), Session["uname"].ToString(), Session["pword"].ToString());
        }
        
        // if credential check fails
        if (!credcheck)
        {
          Response.Redirect("./logout.aspx");
        }
        
        if (!IsPostBack)
        { // if not a post back
          ddlLocation.SelectedValue = "All";
          CreateTable("All");
        }
      }
    }

    Thank you in advance.

    Tuesday, October 30, 2012 8:13 PM

All replies

  • If the error is the title of your post then what it is telling you is that there is no column named ECC in the table. I do not know what the SQL statement looks like but you may want to test it using SQL Server Management Studio before running in code.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, October 31, 2012 2:16 PM
  • Thank you for your reply, I tried what you just recommended and the query works just fine but still has the error.
    Wednesday, October 31, 2012 2:44 PM
  • What is the value of commandString before ExecuteReader is called?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, October 31, 2012 3:25 PM
  •   string commandString = "SELECT E.*, ECC.[Description] FROM [CW-IS].dbo.TR_Employees AS E JOIN [CW-IS].dbo.TR_ECC AS ECC ON E.[ECC] = ECC.[ECC]  ";
          switch (strLOC)
          {
            case "OH":
              commandString += "WHERE [Location] Like 'OH' ";
              break;

            case "TX":
              commandString += "WHERE [Location] Like 'TX' ";
              break;

            case "WV":
              commandString += "WHERE [Location] Like 'WV' ";
              break;
          }
          commandString += "ORDER BY [LName] ASC;";

    I'm thinking that this is it.

                                
    Wednesday, October 31, 2012 6:40 PM
  • So if you run the below statement through SQL Server Management Studio it executes without any problem? I haven't tried to decipher it yet but it looks a bit confusing.

    SELECT E.*, ECC.[Description] FROM [CW-IS].dbo.TR_Employees AS E JOIN [CW-IS].dbo.TR_ECC AS ECC ON E.[ECC] = ECC.[ECC] WHERE [Location] Like 'TX' ORDER BY [LName] ASC


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, October 31, 2012 7:27 PM
  • Correct...like I said I did not write the code I'm just trying to get it up and running.
    Thursday, November 1, 2012 12:46 PM
  • I,

    Might the problem is with the below line. your are using ECC column which is not using in your Sql Query.

       tcell.Text = String.Format("{0}", reader["ECC"]);

    if ECC column is present in your Sql Query then it will works fine.

    PS.Shakeer Hussain

    Thursday, November 1, 2012 12:53 PM
  • I'm sorry I'm looking at it and I thought the ECC what in the query. I tried changing it and it didn't work.
    Thursday, November 1, 2012 7:13 PM