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 exiting table

I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeIDYou cannot add a column in the middle of a table. When you do an ALTER
TABLE to add a column, it is added at the and of the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeID|||Big D wrote:

> I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
that
> is between FIRSTNAME and TypeCodeID named Active? I am hoping not having
to
> drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
Hi,
Yes, you use ALTER TABLE to add a column to an existing table, but columns
do not have any order, just as you have no control over the order of rows.
You can specify any order you desire for columns when you retrieve rows with
a SELECT statement.
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--|||Short answer, you shouldn't care. This:
> ID SSN LastName FirstName Active TypeCodeID
is equivalent to:
> ID SSN LastName FirstName TypeCodeID Active
It has the same data, and the same properties. When you query the data, you
order the columns how you want, as you should never do:
select *
from table
Other than when you are testing.
That having been said, I know what you mean and sometimes you want to
reorder the columns for "ease of use." For this, you have to drop and
recreate the table in the order you want the columns.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I did many times previously.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||However, Big didn't want to drop and re-create the table, which is exactly w
hat EM does...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Right" <serdar@.senkronyazilim.com> wrote in message
news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
> An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I
> did many times previously.
> "Big D" <BigDaddy@.newsgroup.nospam> wrote in message news:eXsabilYFHA.1868
@.TK2MSFTNGP14.phx.gbl...
>|||Thanks everyone who helped.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vF52XpYFHA.3616@.TK2MSFTNGP15.phx.gbl...
> However, Big didn't want to drop and re-create the table, which is exactly
> what EM does...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Right" <serdar@.senkronyazilim.com> wrote in message
> news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
>

Add Cases to Select Statment

I need to add some cases to the select statment for cpeorderstatus:

Here is my Select statement:

"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD')
AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER,
OI.ITEM AS CPEORDERTYPE, OI.QTY,
O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST,
C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE,
'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE
FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R
WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND
(C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"

I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP

This is really unclear. It looks like you're selecting

O.STATUS AS CPEORDERSTATUS

but you're not doing anything with it other than returning it. Why not simply adding 5 different values to whatever inserting into the orders table?