Error: DataTable not returning colums.

Question

Hello:

I am not getting anything back from the DataTable. Can someone tell me what I am missing or if I have something out of order.

public void GetFieldList(string DBTableName)
{
ClassDB DBConn = new ClassDB();
//
Connection();
createAdapter();
DBCommand.CommandText = "SELECT * FROM [" + DBTableName.ToString() + "]";
MessageBox.Show("DBCommand.CommandText= " + DBCommand.CommandText.ToString()); // Select is good.
DBDataReader = DBCommand.ExecuteReader();
//
DataTable DBTable = new DataTable();
//
string DBSQLList = "";
//
MessageBox.Show("DBTablename= " + DBTableName); // DBTableName is good.
//
foreach (DataColumn DBColumn in DBTable.Columns) // DOES NOT LOOP THRU COLUMNS <<< ERROR
{
if (DBSQLList.Length > 0)
{
DBSQLList += ", ";
}
DBSQLList += DBColumn.ColumnName;
}
//
MessageBox.Show("DBSQLList= " + DBSQLList); // Its empty.
}

theLizard · Answer

this is in the new zip sorry I have made some other changes this happens a lot. public string BuildSelect(int fromOrdinal int toOrdinal string wherePart string orderby) { string s = SELECT ; for (int i = fromOrdinal; i { if (toOrdinal != 0) //0 == to end of field list { if (i { s += _fields[i].name; s += (i } else { break; } } else { s += _fields[i].name; s += (i } } s +=  FROM company; //company could also be tablename as a parameter. s += (!String.IsNullOrEmpty(wherePart)?  WHERE  + wherePart : ); s += (!String.IsNullOrEmpty(orderby) ?  ORDER BY  + orderby : ); return (s); } this is a simple version of building a select statement can you see what is going on! Can you see how it could be made generic for ALL your tables?

Gustavo · Answer

Lizard: Yep I can work out the rest. Thank you very much.

theLizard · Answer

That is the nature of things How the fields are shown can be ordered by whatever is needed but is not really relevant. With the example I have given you can you work out the basics for how to build an insert statement?

Gustavo · Answer

Lizard: You probably put them in order and never edited them. I just ran a test. I inserted a field 'test' after the 4th field. Ran the program and it shows as the last field. Thats ok the SELECT is still correct.

theLizard · Answer

Most likely I have website at the 18th position ordinal is zero based so SEQNO is 0

Gustavo · Answer

Lizard: There was missing fields because the fromOrdinal was set to 10 instead of 1. I changed it to 1 and it works. The order of the fields is not the same as the order in the table. Example: The WebSite field is show a the 7th field but in the table its really the 19th field. Is it many due to when I edited them or something?

theLizard · Answer

What do you mean by missing some fields and what order are they compared to the database order.

Gustavo · Answer

Lizard: Ok the toOrdinal was set at 10... I changed it to 1.

Gustavo · Answer

Lizard: Cooool... I steped thru the debug and looked at it. It works... BUT: Its missing some fields. I can figure out why. Also: Why would it not list the fields in order/sequence?

Gustavo · Answer

Lizard: I was trying to get rid of some bugs... but with your version 2 they went away. Thanks.

Gustavo · Answer

Lizard: It still does not like: ClassCompany b = new ClassCompany (TableName); Do I have to do something special in the ClassCompnay so it sees it? NEVERMIND ... I change the ClassCompany to:  public class ClassCompany. Is that OK? Im having an issue with the (string table)... Im looking into it.

theLizard · Answer

yes the big code is a separate file we will build on this but I also think I will move the public void GetFieldDefs() to the fields file because it is more or less generic. yes ClassFields is ok ClassCompany b = new ClassCompany(); you can change this to be ClassCompany b = new ClassCompany(company); public ClassCOmpany(string table) then this line can be changed sql += WHERE sysobjects.name='company'; to this line sql += WHERE sysobjects.name= ' + table + ';

Gustavo · Answer

Lizard: Ok I created the new classes. I am sure I did not do it correctly. Question 1: In my form code. The following code is what I added. Is it correct? the line with 'company' is bad. private void buttonLizardFields_Click( object sender EventArgs e) { ClassDB DBConn = new ClassDB (DBConnectionString); TableName = textBoxTableName.Text; // DBConn.DBCommand.CommandText = SELECT * FROM Company ; DBConn.Connection(); // //company b = new company(DBConn); // DBConn.Terminate(); } Question 2: The new class I named it ClassFields and it looks like this: using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ICEPack.Class { class fields { public int ordinal; public string name; public object type; public bool nullable; public object value; public object ctrl; public fields() { } } } Question 3: The big code class: That is a seperate class correct? If so I named it ClassCompany.

Gustavo · Answer

Lizard: Thanks I will study the code.

theLizard · Answer

Ok I can see where the problem is now. you are using the DataReader so you need to do the while(reader.read()) thing. I like your initiative but this is not the way to go. look at the attached zip... I have created the following classes fields.cs this is a class we will use to store field info company.cs -- this is the company class we will use for all things company. see if you can get them working you don't need to show anything yet just see if you can step through the code without errors it works here no problems with the modified company table you sent. I will be gone for a little bit so study the code.

Gustavo · Answer

Lizard: Yes I have data in the table.

theLizard · Answer

do you have data in the table?