CODEWITHSUNDEEP
asp.netsql-server
Wilkins
Wilkins

Reputation: 185

System.Data.SqlClient.SqlException: Invalid column name

Trying to do a recordset, I just want one column of data, but this code is giving me an error.. I'm an ASP.NET newb, can anyone help?:

System.Data.SqlClient.SqlException: Invalid column name 'CustomerName'.

 using (SqlConnection con = new SqlConnection(DB.GetDBConn()))
 {
  con.Open();
  using (IDataReader dr = DB.GetRS("select CustomerName from Customer where CustomerID=" + Customer.CustomerID, con))
   {
       string CustomerName = "CustomerName";                    
   }
 }

 String EncCustomerName = Encrypt(CustomerName.Replace(".", "").Replace("-", ""),"1");

Question #2: How do I bind the database content to the CustomerName string? It seems like its only returning "CustomerName" as the value for CustomerName string.. I would like it to return the database data for CustomerName string.. Help?

Suggested to use a ExecuteScalar, so i modified the request to this 

   using (var con = new SqlConnection(DB.GetDBConn()))
   using (var cmdContrib = new SqlCommand("SELECT CustomerName FROM Customer WHERE   CustomerID=" + ThisCustomer.CustomerID, con))

   {

        con.Open();
        string CustomerName = cmdContrib.ExecuteScalar();
   }

And i Get this error: "string CustomerName = cmdCust.ExecuteScalar();"

CS0266: Cannot implicitly convert type 'object' to 'string'. An explicit conversion exists (are you missing a cast?)

Upvotes: 3

Views: 14629

Answers (5)

Nabheet
Nabheet

Reputation: 1314

Try doing a select * from customer where ... and put a breakpoint on your using datareader statement. Then use quick-watch on the datareader object to investigate the columns exposed in the recordset.

Or you could run the select statement on your db of choice to ensure that the column name is the same.

I agree with Madhur above, your column name is not spelled correctly. Or you are not connecting to the correct db.

Hope this helps

Upvotes: 0

Tim
Tim

Reputation: 28520

To answer your second question:

// Set it here so you can access it outside the scope of the using statement
string CustomerName = "";

using (SqlConnection con = new SqlConnection(DB.GetDBConn()))
{
    con.Open();
    using (IDataReader dr = DB.GetRS("select CustomerName from Customer where CustomerID=" + Customer.CustomerID, con))
    {
        while (dr.Read())
            CustomerName = dr["CustomerName"].ToString();
        }                    
    }
 }

If you're sure you'll only get one CustomerName result, using a DataReader is a bit of an overkill.

SqlCommand.ExecuteScalar Example

string CustomerName = "";

using (SqlConnection con = new SqlConnection(DB.GetDBConn()))
{

    SqlCommand cmd = new SqlCommand("SELECT CustomerName FROM Customer WHERE CustomerID = " + Customer.CustomerID, con);
    cmd.CommandType = CommandType.Text;

    con.Open();

    CustomerName = Convert.ToString(cmd.ExecuteScalar());

}

SqlCommand.ExecuteScalar Method

Additional Info

ExecuteScalar returns an object, so you'll need to convert the returned value to the proper type (in this case, string).

Also, you should declare your CustomerName value outside of the using blocks (as I did in my example) - otherwise it will be scoped to the using blocks and not available outside of them.

Upvotes: 2

Decker97
Decker97

Reputation: 1653

First, debug and check the value of:

DB.GetDBConn()

You will verify that you are going to the same in Studio as you are in the program.

I think it is the spelling somewhere between the db and your code.

Once you get past the error, you need to fix this:

{
       string CustomerName = "CustomerName";                    
   }

You are not accessing the reader, try some kind of tutorial for that stuff.

Upvotes: 0

Brij
Brij

Reputation: 6122

  1. Make sure you are trying to connect correct database.
  2. See CustomerName column should be in Customer table. check spelling also

Upvotes: 0

Madhur Ahuja
Madhur Ahuja

Reputation: 22661

It means that either CustomerName or CustomerID is not a valid column within your database. Check your table again.

Upvotes: 0

Related Questions