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.

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.

Add column to table

Hi everybody.
Does anyone know if it is possible to add a formula field as column of a
replicated table?
How can I do it?
Thanks,
Marco
You can add a formula column the same as would if you added a non-formula
column to a published table. See BOL topic Schema Changes on Publication
Databases for more details.
To add columns to an article
1. In SQL Server Enterprise Manager, under Replication, expand Publications
and then right-click the publication where you want to modify a schema.
2. Click Properties, click Filter Columns, and then click Add Column.
3. In the Add Column to Replicated Table dialog box, enter the name of the
column and the SQL syntax that defines the column. For example if I want
to add a new formula column with a value of 10 greater than an existing
column col1, the SQL syntax should be: "AS ([col1] + 10)" . (For
information about the syntax required to define the column, see the
Transact-SQL ALTER TABLE statement)
4. In the Add Column to Replicated Table dialog box, select the
publications to which you want to add the column.
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Try something like:
sp_repladdcolumn @.source_object = 'tablename',
@.column = 'columnname',
@.typetext = ' as (ID + 1)'
I just used this on a test system where ID is an existing column and it was
added on the publisher and subscriber (through synchronization) fine.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Actual physical file size

Hi Everybody,
I like to compute the actual physical database file size using queries.

From the EM database properties, the file size shown is not matching with the master..sysaltfiles.size

Ex. Actual file size is 12MB and 1MB for MDF & LDF respectively.
'sysaltfiles' shows 1464 & 96 resp. Since they are shoing it as 8K Pages,
96*8 = 768KB is not matching with 1024KB for the LDF file.
Is there any overheads (additional space from the physical file for file headers) allocated?

Thanks
Babuhm odd, I see the same thing. Additionaly, when calculating via sysaltfiles and comparing it with what a 'dir' shows, I have a match. The EM does show the size correctly in the Space allocated (taskpad), but not the Database Size. In my case it's a 4,56 MB off. Detaching the DB didn't change the size on disk either.|||Hi Kaiowas,

So you are getting the computation of sysaltfiles.size*8K pages matching with those of the physical OS file size. There is no difference in your system.
Mine is not matching. Any idea?|||yes, I did size * 8 * 1024 to get a bytesize to compare against a dir/properties of the actual databasefile.