Why am I getting, "A column ID occurred more than once in the specification"?

Question

I've got code that creates a table if it doesn't exist and adds all necessary columns, but for cases where the user has an older version of the table, it adds some new

columns. Yet when that second condition is true and the DDL to add columns runs, I get, "A column ID occurred more than once in the specification"

Here is the code, along with the helper functions to determine existence of table and column:

    bool tableExists = dbconn.isValidTable(tablename) != -1;
    if (!tableExists) 
    {
        ddl = "CREATE TABLE Bla (. . . salvationID nvarchar(19), salvation float, discount float)";
        dbconn.DBCommand(ddl, false);
    }
    else // (the table does exist) 
    {
        if(!dbconn.isValidField(tablename,"redemptionID"))
        {
            ddl = string.Format("ALTER TABLE {0} ADD redemptionID nvarchar(19) ", tablename);
            dbconn.DBCommand(ddl,false);
            . . .

        public int isValidTable(string tableName)
        {
            int validTable = -1;
            string tblQuery = string.Format("SELECT COUNT(*) FROM {0}", tableName);
            checkConnection();
            try
            {
                SqlCeCommand cmd = objCon.CreateCommand();
                cmd.CommandText = tblQuery;
                object objcnt = cmd.ExecuteScalar();
                validTable = Int32.Parse(objcnt.ToString());
            }
            catch
            {
                validTable = -1;
            }
            return validTable;
        }

//This has been beautified/elegantized thanks to p.s.w.g at http://stackoverflow.com/questions/15693639/how-can-i-determine-whether-a-column-exists-in-a-sql-server-ce-table-with-c
        public bool isValidField(string tableName, string columnName)
        {
            bool retVal;
            string tblQuery = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND 

COLUMN_NAME = @columnName";
            checkConnection();
            try
            {
                SqlCeCommand cmd = objCon.CreateCommand();
                cmd.CommandText = tblQuery;
                SqlCeParameter tblNameParam = new SqlCeParameter("@tableName", SqlDbType.NVarChar, 128);
                tblNameParam.Value = tableName;
                cmd.Parameters.Add(tblNameParam);
                SqlCeParameter colNameParam = new SqlCeParameter("@columnName", SqlDbType.NVarChar, 128);
                colNameParam.Value = tableName;
                cmd.Parameters.Add(colNameParam);
                object objvalid = cmd.ExecuteScalar();
                retVal = !Convert.IsDBNull(objvalid);
            }
            catch
            {
                retVal = false; 
            }
            return retVal;
        }

Can you please specify exactly which part of the code is throwing the error? I'm not sure what you mean by "second condition". Also, it appears that you have removed some of your SQL for the sake of brevity. If you are asking a question about a SQL error please include all sql. — Abe Miessler, Mar 29 '13 at 15:52
The second condition is the else block. It's the ALTER TABLE ddl that's being run that is the problem - when it runs, I get the err msg in the post title. And if that's true - the column already exists, why does it pass the "not valid field" condition? — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 16:09
try putting a break point at `retVal = !Convert.IsDBNull(objvalid);` and make sure that it is return what you would expect based on it's value. — Abe Miessler, Mar 29 '13 at 16:44
I can't use breakpoints with this project - I have to use MessageBox.Show() and writing msgs to a file. — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 16:49
Because this is a Windows CE app that must run either on the device or an emulator, and due to the archaic version of software I'm using, I have to develop in an XPMode virtual machine using VS2003 and .NET 1.1. My travails diesbzg. have been recorded at length on other SO posts; the "Reader's Digest" version: I'm stuck in the prehistoric and antiquated ways of our great-grandfathers, debugging-wise. — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 16:56
Bad times. Well it seems like that is where the problem may lie. I would try and find a way to inspect that value — Abe Miessler, Mar 29 '13 at 16:59
how about retrieving the schema just before the alter table to make sure what is really there. Or perhaps the error is a side effect of incorrect permission. — koriander, Mar 29 '13 at 19:10
@koriander: I'm able to create the table, etc., so I don't think it's a permissions issue. If you have some code for retrieving the schema, I'd love to see it. — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 20:03
http://treasure4developer.wordpress.com/2007/12/28/get-table-schema-from-ms-sql/ — koriander, Mar 29 '13 at 20:11
Thanks, but I don't have much confidence that would work any better than what I've already got for "isValidField() (sic - should be "isValidColumn") above. — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 20:25
I haven't used CE but I see examples where the new column is added so: `ALTER TABLE FOO ADD COLUMN ( lastname nvarchar(19) )` http://stackoverflow.com/questions/2840021/how-write-query-to-alter-table-in-sql-server-ce — Tim, Mar 29 '13 at 21:26

score 1 · Answer 1 · answered Mar 29 '13 at 21:09

Try checking for DBNull.Value.

try
{
  SqlCeCommand cmd = objCon.CreateCommand();
  cmd.CommandText = tblQuery;
  object objcnt = cmd.ExecuteScalar();
  if ((objcnt != null) && (objcnt != DBNull.Value)) {
    validTable = Int32.Parse(objcnt.ToString());
  } else {
    MessageBox.Show("NULL returned from CreateCommand. Remove this line.");
  }
}
catch

score 1 · Accepted Answer · answered Mar 29 '13 at 21:26

I suspect what's going on is that isValidField() is throwing an exception and your catch is simply swallowing it while stating the field doesn't exist... when it probably does.

I would highly suggest that instead of just swallowing it that you actually display the message so that you know what's going on.

For example:

public bool isValidField(string tableName, string columnName)
{
    bool retVal;
    string tblQuery = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND COLUMN_NAME = @columnName";
    checkConnection();
    try
    {
        SqlCeCommand cmd = objCon.CreateCommand();
        cmd.CommandText = tblQuery;
        SqlCeParameter tblNameParam = new SqlCeParameter("@tableName", SqlDbType.NVarChar, 128);
        tblNameParam.Value = tableName;
        cmd.Parameters.Add(tblNameParam);
        SqlCeParameter colNameParam = new SqlCeParameter("@columnName", SqlDbType.NVarChar, 128);
        colNameParam.Value = tableName;
        cmd.Parameters.Add(colNameParam);
        object objvalid = cmd.ExecuteScalar();
        retVal = !Convert.IsDBNull(objvalid);
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
        retVal = false; // <-- wrong answer
    }
    return retVal;
}

Also, that function should not be a boolean. You have 3 conditions: 1. It exists; 2. It doesn't exist; 3. error occurred.

In the event of an error you don't want the later methods to think that it couldn't find it. Also, I'd do the same thing to where you are validating the table exists.

Without it returning a bool result, how will the caller know whether it's a waste of time to try adding a column that already exists? I think an exception can be equated to "it's not a valid column" for practical purposes (and what programmer wants to deal with impractical purposes)? — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 21:48

Why am I getting, "A column ID occurred more than once in the specification"?

2 Answers2