Error: DataTable not returning colums.

Gustavo

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.
}

17 Replies

Know the answer? Post it — somebody with the same question will find it here.

theLizard16y ago
Accepted 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 < _fields.Length; i++)
            {
                if (toOrdinal != 0) //0 == to end of field list
                {
                    if (i <= toOrdinal) // to n of field list fromOrdinal 5, to toOrdinal 9
                    {
                        s += _fields[i].name;
                        s += (i < _fields.Length - 1 ? ", " : "");
                    }
                    else
                    {
                        break;
                    }
                }
                else
                {
                    s += _fields[i].name;
                    s += (i < _fields.Length - 1 ? ", " : "");
                }
            }
            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?
Gustavo16y ago
Lizard:

Yep, I can work out the rest.

Thank you very much.
theLizard16y ago
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?
Gustavo16y ago
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.
theLizard16y ago
Most likely, I have website at the 18th position ordinal is zero based so SEQNO is 0
Gustavo16y ago
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?
theLizard16y ago
What do you mean by missing some fields and what order are they compared to the database order.
Gustavo16y ago
Lizard:

Ok, the toOrdinal was set at 10... I changed it to 1.
Gustavo16y ago
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?
Gustavo16y ago
Lizard:

I was trying to get rid of some bugs... but with your version 2, they went away. Thanks.
Gustavo16y ago
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.
theLizard16y ago
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 + "'";
Gustavo16y ago
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.
Gustavo16y ago
Lizard:

Thanks, I will study the code.
theLizard16y ago
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.
Gustavo16y ago
Lizard:

Yes, I have data in the table.
theLizard16y ago
do you have data in the table?