How to Set Allow Zero Length for Field using OLEDBCommand

gwittlock

I have the following code where I am updating a new field to a Table. I tried using the NULL in my statement and it does create the field but the Allow Zero length is still false in the table. Can anyone show me the errors of my ways? :)

       Dim sql As String
        Dim connection As OleDbConnection
        Dim connetionString As String

        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & PathCMDB() & "';"

        connection = New OleDbConnection(connetionString)
        connection.Open()

        Dim cmd As OleDbCommand = New OleDbCommand(sql, connection)

        cmd.CommandText = "ALTER TABLE \[Machines\] ADD \[CNC\_Folder\] Text (255) NULL"

        cmd.ExecuteNonQuery()

        connection.Close()
        connection.Dispose()

Matt U

Hope this helps: http://www.dmxzone.com/go/5314/allow-zero-length-and-sql-create-alter-table/[^]

djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.

gwittlock

Hmm not really I am not using ADOX. I was hoping to just change the sql statement somehow. I am just using standard OleDbConnection

Matt U

By the looks of it, it can't be done via a SQL script. If you set the column to "NOT NULL" rather than "NULL", that should allow zero-length values. Will that be feasible, or do you absolutely have to allow both NULL and zero-length?

djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.

gwittlock

I have to allow both. "So simply but yet so hard" I guess I could always create a ADOX wrapper but I don't really want to go that way

Matt U

May be your only option. Google it. I searched around and everything I found said that it's not possible via a SQL query script to allow NULL and zero-length.

djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.

gwittlock

Yeah I found the same thing when I Google it. I was hoping there was some bckdoor found by some of the GURU's here. Thanks for you help though.

Lost User

"Trigger".

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

gwittlock

Trigger? Not sure what that means

Lost User

You could write a trigger to do the validation. Not sure, but might also be done with a constraint that validates the length. --edit; I don't think that Access would allow the constraint-construction, I was thinking in Sql Server again. Still, Access does support triggers[^].

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

gwittlock

Ahhh! Am I right Eddy that there is some limitations on triggers? I thought I saw something that said you could only use framework 2, 3 or 3.5. I believe I did anyways. I would like to stick with 4.5.

Lost User

gwittlock wrote:

Am I right Eddy that there is some limitations on triggers?

Not that I know, but that hardly means anything; I don't use Access that often nowadays.

gwittlock wrote:

I would like to stick with 4.5.

The trigger would be in the database, not in the code :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]