System.Data.SqlClient.SqlException: Invalid column name

Question

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?)

Try running the query "select CustomerName from Customer" directly in Management Studio. I bet you'll find some kind of spelling error (Sincerely not insulting your intelligence, that kind of stuff happens). — Josh Darnell
– Josh Darnell, Commented Sep 3, 2011 at 6:31
you're right, i was not thinking right. But now it wont give me the dynamic data.. it returns "CustomerName" as the value for the CustomerName variable, but i want the data from the database, why is it not returning the dynamic data? — Wilkins
– Wilkins, Commented Sep 3, 2011 at 6:37
You're explicitly setting CustomerName to "CustomerName". You need to access the actual value of the CustomerName column from dr (something like string CustomerName = dr["CustomerName"];) — Josh Darnell
– Josh Darnell, Commented Sep 3, 2011 at 6:45
@jadarnel27: Okay i did that, and it says.... ERROR CS0266: Cannot implicitly convert type 'object' to 'string'. An explicit conversion exists (are you missing a cast?) — Wilkins
– Wilkins, Commented Sep 3, 2011 at 6:48
string CustomerName = dr["CustomerName"].ToString(); I'm on the East Coast (US) and it's 3 AM. I'm going to sleep =) good luck! — Josh Darnell
– Josh Darnell, Commented Sep 3, 2011 at 6:50

Tim · Accepted Answer · 2011-09-03 07:09:44Z

2

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.

edited Sep 3, 2011 at 7:09

answered Sep 3, 2011 at 6:47

Tim

28.6k8 gold badges65 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wilkins Over a year ago

what should i use instead, if all i need is one result?

Tim Over a year ago

I'm not sure what DB is, but you could use ADO.NET and the SqlCommand.ExecuteScalar method. Let me know if you need an example.

Wilkins Over a year ago

i found an example,thank you.. but its still giving me the same error.. i will post the new code

Madhur Ahuja · Accepted Answer · 2011-09-03 06:19:03Z

0

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

answered Sep 3, 2011 at 6:19

Madhur Ahuja

22.8k15 gold badges77 silver badges128 bronze badges

2 Comments

Wilkins Over a year ago

It is a valid column name, thats the thing.

KV Prajapati Over a year ago

@Wilkins Is there any blank in these name?

Brij · Accepted Answer · 2011-09-03 06:29:21Z

0

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

answered Sep 3, 2011 at 6:29

Brij

6,1329 gold badges45 silver badges69 bronze badges

1 Comment

Brij Over a year ago

Add your schema name like dbo.customer.

Decker97 · Accepted Answer · 2011-09-03 06:47:24Z

0

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.

answered Sep 3, 2011 at 6:47

Decker97

1,65310 silver badges11 bronze badges

1 Comment

Decker97 Over a year ago

I don't really use readers, but I'm guessing dr["CustomerName"] will give you a result - what do you want to do with it?

marc_s · Accepted Answer · 2011-09-03 07:28:06Z

0

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

edited Sep 3, 2011 at 7:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 3, 2011 at 6:41

Nabheet

1,3141 gold badge13 silver badges23 bronze badges

2 Comments

Wilkins Over a year ago

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

Nabheet Over a year ago

Please refer to jadarnel27's comment above. You need to change your string assignment for customer name. Please use string customername = dr.GetString(dr.GetOrdinal("CustomerName")); or something like that.

Collectives™ on Stack Overflow

System.Data.SqlClient.SqlException: Invalid column name

5 Answers 5

3 Comments

2 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related