468,168 Members | 1,561 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,168 developers. It's quick & easy.

How to create a checkbox datatype column in a table in VB .net code

I am creating a new boolean column in a MSAccess .mdb database like
this:

Dim Cmd As New OleDb.OleDbCommand("ALTER TABLE m_table ADD boolColumn
YesNo", objConn)
Cmd.ExecuteNonQuery()

, which works fine. But... when the .mdb file is opened in Access the
values for the new column are shown as "-1" and "0"...

Is there a way to make the column display in Access as checkboxes? I
want to be able to do this at runtime in my .net program.

thanks.
Jun 27 '08 #1
9 5899
<ev********@gmail.comschrieb
I am creating a new boolean column in a MSAccess .mdb database like
this:

Dim Cmd As New OleDb.OleDbCommand("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQuery()

, which works fine. But... when the .mdb file is opened in Access
the values for the new column are shown as "-1" and "0"...

Is there a way to make the column display in Access as checkboxes? I
want to be able to do this at runtime in my .net program.
No, not using OleDb. AFAIK you have to use ADOX (or even DAO) to alter
the format used by the Access aplication to display data. Note that this
is Access specific whereas OleDb only refers to the database part which
does not know a user interface.

Maybe interesting:
http://office.microsoft.com/en-us/ac...322071033.aspx
Armin

Jun 27 '08 #2
Thanks Armin,

Do you have an example of how to do this using DAO?

Evan

On May 1, 8:13 pm, "Armin Zingler" <az.nos...@freenet.dewrote:
<evan197...@gmail.comschrieb
I am creating a new booleancolumnin a MSAccess .mdb database like
this:
Dim Cmd As New OleDb.OleDbCommand("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQuery()
, which works fine. But... when the .mdb file is opened in Access
the values for the newcolumnare shown as "-1" and "0"...
Is there a way to make thecolumndisplay in Access as checkboxes? I
want to be able to do this at runtime in my .net program.

No, not using OleDb. AFAIK you have to use ADOX (or even DAO) to alter
the format used by the Access aplication to display data. Note that this
is Access specific whereas OleDb only refers to the database part which
does not know a user interface.

Maybe interesting:http://office.microsoft.com/en-us/ac...322071033.aspx

Armin
Jun 27 '08 #3
On May 1, 8:13 pm, "Armin Zingler" <az.nos...@freenet.dewrote:
<evan197...@gmail.comschrieb
I am creating a new booleancolumnin a MSAccess .mdb database like
this:
Dim Cmd As New OleDb.OleDbCommand("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQuery()
, which works fine. But... when the .mdb file is opened in Access
the values for the newcolumnare shown as "-1" and "0"...
Is there a way to make thecolumndisplay in Access as checkboxes? I
want to be able to do this at runtime in my .net program.

No, not using OleDb. AFAIK you have to use ADOX (or even DAO) to alter
the format used by the Access aplication to display data. Note that this
is Access specific whereas OleDb only refers to the database part which
does not know a user interface.

Maybe interesting:http://office.microsoft.com/en-us/ac...322071033.aspx

Armin
Thanks Armin,

Do you have an example of how to do this in .net? Basically, I want to
do this (I think this example is VB6):

Dim f1 As DAO.Field, pt As DAO.Property
Set f1 = CurrentDb.TableDefs("tblCapexCodes").Fields("ColNa me")
Set pt = f1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
CurrentDb.TableDefs("tblCapexCodes").Fields("ColNa me").Properties.Append
pt

(but cannot figure out how to implement it in .net)
Evan
Jun 27 '08 #4
Thanks Armin,

Do you have an example of how to do this in .net? Basically, I want to
do this (I think this example is VB6):

Dim f1 As DAO.Field, pt As DAO.Property
Set f1 = CurrentDb.TableDefs("tblCapexCodes").Fields("ColNa me")
Set pt = f1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
CurrentDb.TableDefs("tblCapexCodes").Fields("ColNa me").Properties.Append
pt

(but cannot figure out how to implement it in .net)
Evan
Jun 27 '08 #5
Thanks Armin,

Do you have an example of how to do this in .net? Basically, I want to
do this (I think this example is VB6):

Dim f1 As DAO.Field, pt As DAO.Property
Set f1 = CurrentDb.TableDefs("tblCapexCodes").Fields("ColNa me")
Set pt = f1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
CurrentDb.TableDefs("tblCapexCodes").Fields("ColNa me").Properties.Append
pt

(but cannot figure out how to implement it in .net)
Evan
Jun 27 '08 #6
On May 1, 8:13 pm, "Armin Zingler" <az.nos...@freenet.dewrote:
<evan197...@gmail.comschrieb
I am creating a new booleancolumnin a MSAccess .mdb database like
this:
Dim Cmd As New OleDb.OleDbCommand("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQuery()
, which works fine. But... when the .mdb file is opened in Access
the values for the newcolumnare shown as "-1" and "0"...
Is there a way to make thecolumndisplay in Access as checkboxes? I
want to be able to do this at runtime in my .net program.

No, not using OleDb. AFAIK you have to use ADOX (or even DAO) to alter
the format used by the Access aplication to display data. Note that this
is Access specific whereas OleDb only refers to the database part which
does not know a user interface.

Maybe interesting:http://office.microsoft.com/en-us/ac...322071033.aspx

Armin
Thanks Armin,

Do you have an example of how to do this in .net? Basically, I want to
do this (I think this example is VB6):

Dim f1 As DAO.Field, pt As DAO.Property
Set f1 = CurrentDb.TableDefs("tblCapexCodes").Fields("ColNa me")
Set pt = f1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
CurrentDb.TableDefs("tblCapexCodes").Fields("ColNa me").Properties.Append
pt

(but cannot figure out how to implement it in .net)
Evan
Jun 27 '08 #7
<ev********@gmail.comschrieb
Thanks Armin,

Do you have an example of how to do this using DAO?
http://support.microsoft.com/kb/304274
Look for "Displaycontrol" property. Note it's VBA/VB6 code, and you may
additionally need to call
System.Runtime.InteropServices.Marshal.ReleaseComO bject from VB.Net.
Armin

Jun 27 '08 #8
On May 2, 10:05 am, "Armin Zingler" <az.nos...@freenet.dewrote:
<evan197...@gmail.comschrieb
Thanks Armin,
Do you have an example of how to do this using DAO?

http://support.microsoft.com/kb/304274
Look for "Displaycontrol" property. Note it's VBA/VB6 code, and you may
additionally need to call
System.Runtime.InteropServices.Marshal.ReleaseComO bject from VB.Net.

Armin
I've got it now, thanks Armin.

My main problem was that I needed to include "Microsoft DAO 3.6 Object
Library" into the .net references so that I can use the DAO routines.

Here is my solution for anyone interested (it looks for the column
requested and if it can't find it, creates a bool field/column and
adds the extra parameters using DAO routines to make the field/column
a checkbox):

Public Sub CreateColumn(ByVal ColumnName As String, ByVal
ConnectionString As String)
Dim objConn As New OleDb.OleDbConnection(ConnectionString)
objConn.Open()

'get info about the column
Dim schemaTable As DataTable =
objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Column s, New Object()
{Nothing, Nothing, m_table_name, ColumnName})

'add the requested column if it does not exist
If schemaTable.Rows.Count = 0 Then

'create the new column
Dim Cmd As New OleDb.OleDbCommand(String.Format("ALTER TABLE
{0} ADD COLUMN {1} YesNo", m_table_name, ColumnName), objConn)
Cmd.ExecuteNonQuery()

Dim dbs As DAO.Database
Dim dbe As New DAO.DBEngine

Dim sDbPath As String = "C:\.....mdb"
Dim sDbPassword As String = ""

dbs = dbe.OpenDatabase(sDbPath, False, False, "MS
Access;PWD=" & sDbPassword & ";")

'make the new column a checkbox field (or else the field
displays as "-1" and "0" as "Yes/No")
Dim fld As DAO.Field
fld = dbs.TableDefs(m_table_name).Fields(ColumnName)

dbs.TableDefs(m_table_name).Fields(ColumnName).Pro perties.Append(fld.CreateProperty("DisplayControl" ,
DAO.DataTypeEnum.dbInteger, 106))

dbs.TableDefs(m_table_name).Fields(ColumnName).Pro perties.Append(fld.CreateProperty("Format",
DAO.DataTypeEnum.dbText, "Yes/No"))
dbs.Close()

'cleanup
dbs = Nothing
dbe = Nothing
fld = Nothing
End If

objConn.Close()
End Sub
Note: The "DisplayControl" and "Format" properties are used when
looking at the database in MSAccess
Jun 27 '08 #9
<ev********@gmail.comschrieb
Here is my solution for anyone interested (it looks for the column
requested and if it can't find it, creates a bool field/column and
adds the extra parameters using DAO routines to make the
field/column a checkbox):
Thanks for posting the code. If anybody will stumble across the COM
reference counting issue (if there is any), he will still be able to
ask. (I currently can't post a resolution in advance.)
Armin

Jun 27 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Peter Foti | last post: by
2 posts views Thread by Ryan Lafferty | last post: by
2 posts views Thread by Hymer | last post: by
1 post views Thread by akress | last post: by
reply views Thread by kamranasdasdas | last post: by
reply views Thread by gcreed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.