locked
ALTER TABLE with DEFAULT value RRS feed

  • 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#

    Wednesday, August 4, 2010 4:16 PM

Answers

All replies

  • User653228039 posted

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


    Try

    True

    or

    'True'

    Wednesday, August 4, 2010 5:00 PM
  • User253847291 posted

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

    Wednesday, August 4, 2010 5:09 PM
  • 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/

    Wednesday, August 4, 2010 5:24 PM
  • 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

    Wednesday, August 4, 2010 6:30 PM
  • User253847291 posted

    are you trying in access?

    Wednesday, August 4, 2010 6:46 PM
  • User253847291 posted

    the DEFAULT keyword is not working at all.

    and like Penpals say: "Tell me why!"

    Thursday, August 5, 2010 1:29 AM
  • 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

    Thursday, August 5, 2010 6:52 AM
  • 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,

    Thursday, August 5, 2010 12:52 PM
  • 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?

    Thursday, August 5, 2010 1:13 PM
  • 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);


    Friday, August 6, 2010 11:42 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 6, 2010 3:42 PM
  • 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.

    Friday, August 6, 2010 9:36 PM
  • 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.


    Friday, August 6, 2010 9:55 PM
  • 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.

    Friday, August 6, 2010 10:00 PM
  • 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

     

    Saturday, August 7, 2010 8:02 AM
  • 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

    Saturday, August 7, 2010 8:04 AM
  • 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?

    Sunday, August 8, 2010 8:15 PM
  • 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 :-)

    Sunday, August 8, 2010 8:16 PM
  • 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? Undecided

    Monday, August 9, 2010 7:49 AM
  • 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? Undecided

    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.

    Thursday, August 12, 2010 4:49 PM