ALTER TABLE gives error after INSERT INTO in same table?

Le rner

hi, i am inserting values in such format: first add column than insert its value,than add second column but here the ALTER TABLE gives error after INSERT INTO in same table. please help me how can i do this. thanks.

_Damian S_

Step 1 - post your code.

Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Le rner

CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager

CString sExcelFile =_T("C:\\Users\\Admin\\Desktop\\1.xls");// Filename and path for the file to be created

CString Query_Str="";

TRY
{

Query\_Str.Format(\_T("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE\_DB=\\"%s\\";DBQ=%s"),                
sDriver, sExcelFile, sExcelFile);     // Create the database (i.e. Excel sheet)

if( database.OpenEx(Query\_Str,CDatabase::noOdbcDialog) )
{
	// Create table structure
	Query\_Str = "CREATE TABLE demo (Name TEXT)";
	database.ExecuteSQL(Query\_Str);

	// Insert data
	Query\_Str = "INSERT INTO demo (Name) VALUES ('Albert')";
	database.ExecuteSQL(Query\_Str); 

	//add column
	Query\_Str = "ALTER TABLE demo ADD Col2 TEXT";
	database.ExecuteSQL(Query\_Str);
}
// Close database
database.Close();

}
CATCH_ALL(e)
{
AfxMessageBox(e->ReportError());
}
END_CATCH_ALL;

can u please help me to ALTER TABLE with DEFAULT

Gerben Jongerius

You will probably have to commit the data to the database before continueing to change the table structure. Not all database drivers / databases like it when you modify a table and add data to that same table within the same transaction. (I know that this could cause table corruption in ms access for example.)

Le rner

please give any solution or help for this.

Lost User

First add all the columns and do the insert when all columns are present, or make sure that the column you're going to add accepts null-values.

Bastard Programmer from Hell :suss:

Le rner

Eddy Vluggen wrote:

First add all the columns and do the insert when all columns are present, or make sure that the column you're going to add accepts null-values.

its not sure how many column are in current sheet,so how can i do this? how can add column to accept null values. is there no any method to add column after inserting row in table.