Add new column into the table using ADO not ADOX

Hi Everybody,

I want to add new column 'firstaname' into the existing table "geo" already two columns namely

'salcode' 'lastname'.I run the following code it runs without error, when i opened the database see the structure of table, i didn't find new column in the table "geo"

here is the snippet of code what i am using.

/* FieldsPtr fields;
FieldPtr field;*/

_bstr_t name("firstname1");

//pRstTitles->get_Fields(&fields);

/* pRstTitles->Fields->Append(name, adVarChar , 15, adFldUnspecified);
pRstTitles->CursorLocation = adUseClient ;
pRstTitles->LockType = adLockOptimistic ;
pRstTitles->Open("geo",
_variant_t((IDispatch *)pConnection,true), adOpenStatic,
adLockOptimistic, adCmdTable);*/

pls pls pls help me out .. I need it very urgently.

First of all not every provider supports this, second I do not see where you call Update to actually change the table.|||

Thanks for the reply.

I have already tried fields->update() but got ADO excpetion "Not impl".

means it is not implemented, Even i tried recordset->update().but nothing happens.

|||I think you should open the recordset first, add fields and then update.
Have you tried that sequence? What is the provider you're using?|||

Well I tried that one also.

I opened the recordset then try to add new field.

then call recordset->update().

That time it throws exception.

operation is not allowed in this context.

I didnot Understand the provider.

If I am correct ...may be You are talking about MSADO15.dll

And database is MS-Access.

If You have some Test application Which is working on your end can You send me the snippet of taht code?

Thanks

|||Actually, I was wrong.
According to Append Method (ADO)

A run-time error will occur if the Append method is called on the Fields collection of an open Recordset, or on a Recordset where the ActiveConnection property has been set. You can only append fields to a Recordset that is not open and has not yet been connected to a data source. This is typically the case when a Recordset object is fabricated with the CreateRecordset method or assigned to an object variable.

I'm going to look if there is a solution to this, but you might have to use ADOX after all.|||Can you simply call execute on your connection object and do something like:

alter table geo add column firstname char(15)

|||

Thanks for Your Quick Replies.

well, Anton As I mentioned in the subject line I can't use ADOX in my project at this time.

Another important thing is that I can use Alter Table but i can't set attributes of the new column.

Actually We were using DAO, now importing project into ADO (for the support of 64 Bit OS).

In DAO there is one API void CreateField( LPCTSTR lpszName, short nType, long lSize, long lAttributes = 0 );
I am Making ADO wrapper, similar function exist in ADO fields.Append Name, Type, DefinedSize, Attrib, FieldValue

but due to not support of this function.I need to use Alter table clause but I am not able to set attributes of the new Column

by this appraoch. What I told you about "Geo" Table is just as an example.

|||

I'm not sure that

1) attributes of ADO field collection have 1 to 1 correspondence to attributes of DAO fields

2) what effect setting of the attribute value for DAO field has on the underlying Access data file; What functionality are you missing exactly?

|||

Hi Anton,

Atlast I gave up with the use of append function in ADO()

I am using alternate solution alter table query.

well I have created ADO Wrapper, and put in preprocessor setting,

if ADO Support is not there the code is functional for DAO.

and For ADO it will be functional for DAO.

Well CreateField() is a wrapper, If it is DAO, It will call DAO supported Function.

and If it ADO. I will have to implement this Function,

I implemented like this way. and I need to set Attributes by this appraoch.

void CDaoTableDef::CreateField(LPCTSTR lpszName,short nType,long lSize,long lAttributes /*= 0*/)
{
TRY_BLOCK
_bstr_t strCommand = m_strName.AllocSysString();

CConnectionPtrWrapper* pConnection = new CConnectionPtrWrapper();
pConnection->GetInnerObject() = m_pDatabase->GetConnection()->GetInnerObject();

#include <DBDAOINT.H> //for DAO data types (dbBoolean, etc.)

CString strType;
switch(nType)
{
case dbBoolean:
strType = _T("Bit");
break;
case dbByte: //1 byte
strType = _T("Smallint"); //need to verify
break;
case dbInteger: //2 bytes
strType = _T("Smallint");
break;
case dbLong: //4 bytes
strType = _T("Integer");
break;
case dbCurrency:
strType = _T("Varchar(255)");
break;
case dbSingle:
strType = _T("Float"); //Float is double-precision
break;
case dbDouble:
strType = _T("Float");
break;
case dbDate:
strType = _T("Datetime");
break;
case dbText:
strType.Format(_T("Text(%d)"), lSize); //size applicable for Text and binary types only
break;
case dbLongBinary:
strType.Format(_T("Long(%d)"), lSize);
break;
case dbMemo:
strType = _T("Varchar(255)");
break;
default:
ASSERT(0);
}

_bstr_t strATQry;
strATQry = _T("ALTER TABLE ") + m_strName + _T(" ADD COLUMN ") + lpszName + _T(" ") + strType;

pConnection->GetInnerObject()->Execute(strATQry, NULL, adExecuteNoRecords);

_RecordsetPtr rs = NULL;
rs->Open(strCommand, _variant_t((IDispatch *) pConnection->GetInnerObject(), true), adOpenKeyset, adLockOptimistic, adCmdUnknown);

FieldsPtr fields;
rs->get_Fields(&fields);

// fields->Append(lpszName, (DataTypeEnum)nType, lSize, (FieldAttributeEnum)lAttributes);
FieldPtr field = fields->GetItem(lpszName);
long lADOAttributes;
if (lAttributes & dbFixedField)
{
lADOAttributes &= adFldFixed;
}
if (lAttributes & dbUpdatableField)
{
lADOAttributes &= adFldUpdatable;
}
//dbVariableField:
//dbAutoIncrField:
//dbDescending:
field->PutAttributes(lADOAttributes);

:ysFreeString(strCommand);
delete pConnection;
CATCH_BLOCK
}

I hope You understand my problem.

Thanks

|||Frankly, I'm not sure I understand it completely.
So suppose you have varchar column how would adFldFixed make sense for it?
I can see how you can get attibutes derived from the existing columns, but I'm not sure if it makes any difference when creating. Perhaps I'm missing something here.|||

Thanks Anton,

As you said if the datatype is varchar, then attribute should adFldUpdatable not adFldFixed.

but for Defualt value some time we make it fixed, well fixed and updatable does not matter,

sometime we want to make it indexed,autonumber.

well no problem You tried. and I am creating field without taking care of attributes.

anyway Anton i need your e-mail id, in future we will be able to contact direct way.

well my id is vaibhavsaxena17@.hotmail.com.

Thanks Again.