ADODB SQL query question

KaiserBreath

Senior Member
Joined
Feb 22, 2005
Messages
797
Reaction score
45
Dear all, I am trying to copy some columns from an old table into a new table and adding need columns at the same time. Due to the design of my application, the column order is important.

This is the code:

Code:
SELECT oldTable.[ID], Date AS [MyDate], BIT AS [MyBIt], Text AS [MyText] INTO newTable
FROM oldTable;


However, it returns an exception saying:
"No value given for one or more required parameters."

If I run the query from MSAccess itself, it will pop out "Enter Parameter Value" Dialog box for Date, BIT and Text.

I tried various ways in setting parameters for the ADODB.Command, Eg:
Code:
.CreateParameter(, DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , vbNull)
Code:
.CreateParameter(, DataTypeEnum.adBoolean, ParameterDirectionEnum.adParamInput, , vbNull)
Code:
.CreateParameter(, DataTypeEnum.adVarWChar, ParameterDirectionEnum.adParamInput, , vbNull)


However, it still returns that exception.

Is there any way to solve this? I just need the new columns initialize to all null values.

The more direct question is how to use "INSERT INTO" with adding new columns in MS Access.

Thanks alot!
 
Last edited:

TrueBeliever_jh

Honorary Member
Joined
Jun 8, 2006
Messages
112,795
Reaction score
4,436
copy from TABLE_A to TABLE_B?

normal SQL would be:

INSERT INTO NEWTABLE
SELECT oldTable.[ID], Date AS [MyDate], BIT AS [MyBIt], Text AS [MyText] FROM oldTable;

then again, not too sure how much diff ADO SQL has evolved since i last touched it
 

davidktw

Arch-Supremacy Member
Joined
Apr 15, 2010
Messages
13,387
Reaction score
1,180
Dear all, I am trying to copy some columns from an old table into a new table and adding need columns at the same time. Due to the design of my application, the column order is important.

This is the code:

...


However, it returns an exception saying:
"No value given for one or more required parameters."

If I run the query from MSAccess itself, it will pop out "Enter Parameter Value" Dialog box for Date, BIT and Text.

I tried various ways in setting parameters for the ADODB.Command, Eg:
...


However, it still returns that exception.

Is there any way to solve this? I just need the new columns initialize to all null values.

Thanks alot!

For your query

SELECT oldTable.[ID], Date AS [MyDate], BIT AS [MyBIt], Text AS [MyText] INTO newTable
FROM oldTable;


Where do you expect the columns MyDate, MyBIt and MyText columns to be filled with data from since they are not found from the "oldTable" table ? Your select statement specify that the "newTable" table should have 4 columns, but your "oldTable" table are not supplying the last 3 of them(i inferred).

This is one possible way to do what you want.

Create your new table with the necessary new columns with NULL as default values, then use SELECT to populate from oldTable into the new table as such

Code:
INSERT INTO newTable(ID) SELECT ID FROM oldTable;
 
Last edited:

KaiserBreath

Senior Member
Joined
Feb 22, 2005
Messages
797
Reaction score
45
Thanks all for the reply.

I actually only need MyDate, MyBIt and MyText to be empty since they are new columns.

This query achieved what I wanted. However, I was wondering if there is a way to declare the new column types during "INSERT INTO"

SELECT oldtable.[ID], '' As [MyDate], '' AS [MyBIt], '' AS [MyText], oldTable.[Info]
INTO newtable
FROM oldtable;

ALTER TABLE oldtable ALTER COLUMN [MyDate] Date
ALTER TABLE oldtable ALTER COLUMN [MyBIt] Boolean
ALTER TABLE oldtable ALTER COLUMN [MyText] Text

As mentioned before, I cannot just add the new columns behind as the order is important due to design.
 
Last edited:

davidktw

Arch-Supremacy Member
Joined
Apr 15, 2010
Messages
13,387
Reaction score
1,180
Thanks all for the reply.

I actually only need MyDate, MyBIt and MyText to be empty since they are new columns.

This query achieved what I wanted. However, I was wondering if there is a way to declare the new column types during "INSERT INTO"

SELECT oldtable.[ID], '' As [MyDate], '' AS [MyBIt], '' AS [MyText], oldTable.[Info]
INTO newtable
FROM oldtable;

ALTER TABLE oldtable ALTER COLUMN [MyDate] Date
ALTER TABLE oldtable ALTER COLUMN [MyBIt] Boolean
ALTER TABLE oldtable ALTER COLUMN [MyText] Text

As mentioned before, I cannot just add the new columns behind as the order is important due to design.

Why don't you just issue out the CREATE TABLE DDL to create your newtable ?
 

KaiserBreath

Senior Member
Joined
Feb 22, 2005
Messages
797
Reaction score
45
That's the issue I cannot. I need to retain whatever existing information from the old columns if it still exists in the new one. If not currently I have about 6000 rows and reinserting row by row will be super lag.

By doing this, I only need to update values in the new columns to minimise latency.
 

davidktw

Arch-Supremacy Member
Joined
Apr 15, 2010
Messages
13,387
Reaction score
1,180
That's the issue I cannot. I need to retain whatever existing information from the old columns if it still exists in the new one. If not currently I have about 6000 rows and reinserting row by row will be super lag.

By doing this, I only need to update values in the new columns to minimise latency.

CREATE TABLE first has nothing to do with bulk insert later.

Unless you are in a dynamic situation where you don't know the columns of your oldTable, then you can choose to create new table based on the schema of your old table. Otherwise you can just create your new table once and the bulk select into the new table using rows from the old table.

CREATE TABLE newTable ( columns definition with the new columns allowing NULL);
INSERT INTO newTable(OLD COLUMNS) SELECT columns_one_by_one_listed FROM oldTable;

My MYSQL, we can

CREATE TABLE newTable LIKE oldTable;
// perform your table alteration here
ALTER .... with columns that can allow NULL
INSERT INTO newTable(OLD COLUMNS) SELECT columns_one_by_one_listed FROM oldTable;

or you can also

CREATE TABLE newTable SELECT * from oldTable;
// perform your table alteration here
ALTER .... with columns that can allow NULL
 

KaiserBreath

Senior Member
Joined
Feb 22, 2005
Messages
797
Reaction score
45
it's not up to me to decide lol. I have to make it compatible for both sql server and Ms access.

sql server is much simpler. I can chain the "select into" with CAST(NULL AS DATETIME) AS myDate
 
Important Forum Advisory Note
This forum is moderated by volunteer moderators who will react only to members' feedback on posts. Moderators are not employees or representatives of HWZ. Forum members and moderators are responsible for their own posts.

Please refer to our Community Guidelines and Standards, Terms of Service and Member T&Cs for more information.
Top