locked
SQL Error: Invalid column name . . . [SOLVED]

    Question

  • Hello, I am new to Visual C# and MSDN.  My searches have yielded little help, so I'm hoping a post will.

    I am playing around with the Northwind sample DB and have a simple app that returns each customer and the orders made by that customer:

    SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM dbo.Customers", customerConnection);
    OleDbDataAdapter ordAdapter = new OleDbDataAdapter("SELECT * FROM Orders", orderConnection);       


    In trying to hard code a query for the orders belonging to a specific customer, I made the following addition (in green):

    SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM dbo.Customers WHERE CustomerID = ANATR", customerConnection);

    This generated an error:

    Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'ANATR'.

    What is the correct way to go about doing this? Ultimately, I would like to put in a variable that allows me to enter any customer's name/id and see all the associated orders.

    Any help or advice is appreciated!


    • Edited by Kingworks Friday, June 27, 2008 3:31 PM Problem solved
    Friday, June 27, 2008 1:47 PM

Answers

  • Tables["Customers"] is a table IN MEMORY which holds all the customers with CustomerID == 'ANATR' (which should be just one). Tables["Orders"] is a table in memory which holds all orders for all customers.  You are trying to join the two tables.  But there are orders in Tables["Orders"] for customers which are not in Tables["Customers"]. That's the problem you are having.

    change the second line to

    OleDbDataAdapter ordAdapter = new OleDbDataAdapter("SELECT * FROM Orders WHERE CustomerID = 'ANATR'", orderConnection);

    should solve the problem.


    Both the problems  you've had are far more related to SQL than C#, so  that's probably should be where you spend your study time.

     


    Truth, James (http://www.honestillusion.com)
    • Proposed as answer by Papy Normand Sunday, June 29, 2008 12:03 PM
    • Marked as answer by jack 321 Tuesday, July 01, 2008 5:54 AM
    Friday, June 27, 2008 3:16 PM

All replies

  • Try this:
    SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM dbo.Customers WHERE CustomerID = 'ANATR'", customerConnection);  
     
     
    • Proposed as answer by Papy Normand Sunday, June 29, 2008 12:02 PM
    Friday, June 27, 2008 1:54 PM
  • Thanks for the quick suggestion!  I fear I've got a bit of a domino effect going on. I have a new error (cited at Line 9 of the sample):

    Exception Details: System.ArgumentException: This constraint cannot be enabled as not all values have corresponding parent values.

    Here is the code in it's entirety (to the end of the file, at least):

    1 SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM dbo.Customers WHERE CustomerID = 'ANATR'", customerConnection); 
    2
    OleDbDataAdapter ordAdapter = new OleDbDataAdapter("SELECT * FROM Orders", orderConnection); 
    3 DataSet customerOrders = new DataSet(); 
    4  
    5 custAdapter.Fill(customerOrders, "Customers"); 
    6 ordAdapter.Fill(customerOrders, "Orders"); 
    7 Console.Write(custAdapter.ToString()); 
    8  
    9 DataRelation relation = customerOrders.Relations.Add("CustOrders"
    10 customerOrders.Tables["Customers"].Columns["CustomerID"], 
    11 customerOrders.Tables["Orders"].Columns["CustomerID"]); //This line is cited as the source of the error
    12  
    13 // Output 
    14 Response.Write("<table>"); 
    15 foreach (DataRow pRow in customerOrders.Tables["Customers"].Rows) 
    16
    17     Response.Write("<tr><td colspan=2>"); 
    18     Response.Write(pRow["ContactName"].ToString()); 
    19     Response.Write("</td></tr>"); 
    20                  
    21     foreach (DataRow cRow in pRow.GetChildRows(relation)) 
    22     { 
    23         Response.Write("<tr><td>" + cRow["OrderID"].ToString() + "</td>"); 
    24         Response.Write("<td>" + cRow["ShippedDate"].ToString() + "</td></tr>"); 
    25     } 
    26
    27 Response.Write("</table>"); 

    It was working fine when I wanted to return all of the customers and their orders.
    Friday, June 27, 2008 2:48 PM
  • Tables["Customers"] is a table IN MEMORY which holds all the customers with CustomerID == 'ANATR' (which should be just one). Tables["Orders"] is a table in memory which holds all orders for all customers.  You are trying to join the two tables.  But there are orders in Tables["Orders"] for customers which are not in Tables["Customers"]. That's the problem you are having.

    change the second line to

    OleDbDataAdapter ordAdapter = new OleDbDataAdapter("SELECT * FROM Orders WHERE CustomerID = 'ANATR'", orderConnection);

    should solve the problem.


    Both the problems  you've had are far more related to SQL than C#, so  that's probably should be where you spend your study time.

     


    Truth, James (http://www.honestillusion.com)
    • Proposed as answer by Papy Normand Sunday, June 29, 2008 12:03 PM
    • Marked as answer by jack 321 Tuesday, July 01, 2008 5:54 AM
    Friday, June 27, 2008 3:16 PM
  • Thank you for the solution and the advice.
    Friday, June 27, 2008 3:30 PM