ALTER TABLE with DEFAULT value

Question

User253847291 posted

hello, im trying to add a column into a ms access DB here is my sql:

ALTER TABLE tabLogin ADD COLUMN mailing_pronostico BIT DEFAULT ???

what should i put into ??? to make it true? i used 0, 1 and -1 but didnt work.

im trying the code from access and works without default value (i want to set to true) but i wonder if itd work from codebehind on my website in c#

Answer 1

User653228039 posted

Yeah, I always get confused with Bit since it was implemented differently between SQL 2000 and SQL 2005.

Try

True

or

'True'

Answer 2

User253847291 posted

negative on both, it still say there is an error.

Answer 3

User653228039 posted

Hmm, all my research is telling me 0/1 or True/False should work.  This is MS Access, right?  What is the error you are getting?

This guy looks to have had the same problem as you.  I don't use access, so I don't know what the solution guy is talking about, but it looked like it worked:

http://www.bigresource.com/Tracker/Track-ms_access-wQms55D9/

Answer 4

User-1199946673 posted

ALTER TABLE tabLogin ADD COLUMN mailing_pronostico BIT DEFAULT ???

 

 

ALTER TABLE tabLogin ADD COLUMN mailing_pronostico BIT DEFAULT -1

This is working for me

Answer 5

User253847291 posted

are you trying in access?

Answer 6

User253847291 posted

the DEFAULT keyword is not working at all.

and like Penpals say: "Tell me why!"

Answer 7

User-1199946673 posted

are you trying in access?

 

Yes

Maybe you should download Database Administrator for MS Access. This is a classic ASP tool that allows you to manage your online ACcess databases using a web interface. It also creates the SQL statement to create or update your table and queries

Answer 8

User253847291 posted

ALTER TABLE tabLogin ADD COLUMN mailing_pronostico BIT DEFAULT -1

This is working for me

hello, are you sure that this query worked in access? with DEFAULT keyworkd im gettin an error, without there is no default value set but the query perform correctly. I didnt try if with other data types it works but i doubt, however when you use C# and in code behind how do you add a field with a default value if not using an alter table query?

thank you,

Answer 9

User-1199946673 posted

hello, are you sure that this query worked in access?

 

I already said Yes!!! Id did test this in Access.

with DEFAULT keyworkd im gettin an error

Which error?

I didnt try if with other data types it works but i doubt, however when you use C#

Show your code?

Answer 10

User253847291 posted

hello, are you sure that this query worked in access?

 

I already said Yes!!! Id did test this in Access.

in my access i have an error, say error on alter table query like a syntax error, maybe is it because it is a localized access version?

Show your code?

            string table_name = GetTableName();
            string column_name = txtAlterAddNome.Text;
            string column_value = txtAlterAddValore.Text;
            string column_type = ddlAlterAddTipo.Text.ToUpper();

            //string sql = "ALTER TABLE " + table_name + " ADD COLUMN " + column_name + " " + column_type + " " + column_value;
            string sql = "ALTER TABLE " + table_name + " ADD COLUMN " + column_name + " " + column_type;

            OdbcConnection cnn = new OdbcConnection(GetDBConnection());
            //System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(GetDBConnection());
            OdbcCommand cmm = new OdbcCommand(sql, cnn);

Answer 11

User-1199946673 posted

in my access i have an error, say error on alter table query like a syntax error, maybe is it because it is a localized access version?

 

No, but I think the problem could be that you're using ODBC. You better use OleDb:

http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

But in the code you showed, you don't open the database, and also I can't see how you try to execute the statement, Are you esing ExecuteNonQuery?

Finally, Alhough the risks with Access are fairly small, but be carefull with SQL string conccatenation. Read this:

http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

Note that you can't parameterize tablenames! And I'm not sure if you could use parameters in Alter Table statements, I never tried that

Answer 12

User253847291 posted

But in the code you showed, you don't open the database, and also I can't see how you try to execute the statement, Are you esing ExecuteNonQuery?

sorry there was a storm arriving and i had to close quickly and didnt check what i pasted exactly, yes i call cmm.ExecuteNonQuery(); to alter the table and without the DEFAULT keyword the query comming but there is no default value i can also specify -1 but it is ignored.

i can try OleDB if that is the problem but still why in access it isnt working? I understand that i cant use teh structure view but in sql mode when i hit ! to run the alter command it say syntax error if im using DEFAULT i mentioned a localized version because when i select boolean field type i must specify a sub group (yes/no) or (true/false) or (on/off) im not sure if it is like that in all access releases.

Answer 13

User253847291 posted

the problem could be that you're using ODBC. You better use OleDb:

with OleDB worked but only if i set True as default value, setting -1 doesnt works.

using ODBC i get a syntax error much like when i try this in access.

Answer 14

User253847291 posted

with OleDB worked but only if i set True as default value

im not sure if this is a problem or not but although it set to True the default value for the field the current records which receive the new field doesnt inherit the default value, i should run an update query after to set to true all the records while new records will already be set to true.

Answer 15

User-1199946673 posted

but still why in access it isnt working?

 

That's very easy. in Access, the query pane doesn't support DDL (Data Definition Language), only DML (Data Manipulation Language), so you can't run Data Definition queries like ALTER TABLE...., you should use the Access Interface for that.

I'm not sure why it doesn't work with ODBC, but I don't think that's is still relevant because you better use OleDb anyway and that did work!

i mentioned a localized version because when i select boolean field type i must specify a sub group (yes/no) or (true/false) or (on/off) im not sure if it is like that in all access releases.

I'm working with a localized (dutch) version of Access also. What you're saying is what you're doing/seeing in Access. But you must realize that in a web environment, when using a mdb file, you're not dealing with an Access Database but with a Jet Database Engine. That means that only the Tables and Stored Queries are accessible. The Yes/no, True/False, on/off setting isused in the Access UI, it can't be retrieved in Jet (that is only available in the Access environment), where it is a simple boolean field, meaning -1 or 0 ( in fact all values but 0 are considered true), no matter what you choose in Access! This also means that there are no localized versions of the Jet Database Engine. It depends on the server settings for example in which format  dates are returned.... And the DDL and DML are always the same

 

Answer 16

User-1199946673 posted

although it set to True the default value for the field the current records which receive the new field doesnt inherit the default value

 

That's by design

i should run an update query after to set to true all the records while new records will already be set to true.

 That's correct

Answer 17

User253847291 posted

That's very easy. in Access, the query pane doesn't support DDL (Data Definition Language), only DML (Data Manipulation Language), so you can't run Data Definition queries like ALTER TABLE...., you should use the Access Interface for that.

hello,

just to clarify, how did you have access work with alter table query and default value (post in page 1) did u write a macro or a module for that?

Answer 18

User253847291 posted

although it set to True the default value for the field the current records which receive the new field doesnt inherit the default value

 

That's by design

i should run an update query after to set to true all the records while new records will already be set to true.

 That's correct

i have found that with DEFAULT set to True instead of -1 it sets the default value for new records :-)

Answer 19

User-1199946673 posted

i have found that with DEFAULT set to True instead of -1 it sets the default value for new records :-)

 

Isn't that eaxctly the same as using -1?

Answer 20

User253847291 posted

i have found that with DEFAULT set to True instead of -1 it sets the default value for new records :-)

 

Isn't that eaxctly the same as using -1?

apparently not, setting -1 will set to false all current records, while setting to True it will, in addition, set the default value to True for newly created records.

until some data access guru clarify i have no clue about why it makes this difference.