Link to home
Start Free TrialLog in
Avatar of David Peruso
David Peruso

asked on

How do I specify short text when creating a new access column in vb.net

The code below works, but I don't need it to be long data.
I've tried replacing the "LONGTEXT" with "SHORTTEXT" but that just gave me an string error.

If strBomPartCount.Length > uintTableCurrentColumnCount Then
                oConn.Open()
                For columnIndex = uintOffSet To strBomPartCount.Length
                    oQuery = "ALTER TABLE PcbBom ADD COLUMN PnQty" & (columnIndex - 4) & " LONGTEXT"
                    oComm = New OleDbCommand(oQuery, oConn)
                    oComm.ExecuteNonQuery()
                Next
                oConn.Close()

            End If

Thanks in advance.
Avatar of Rikin Shah
Rikin Shah
Flag of India image

Hi,

Are you sure if you're using ALTER TABLE syntax correctly??

When you specify LONGTEXT, you do not have to specify count. So, in your case-

oQuery = "ALTER TABLE PcbBom ADD COLUMN PnQty LONGTEXT"

Open in new window


Try with that.
Avatar of David Peruso
David Peruso

ASKER

The LONGTEXT works fine, no problem.
What I want is to save it as short text 255 chars, no need to waste space.
The (columnIndex - 4)  just appends a number to the column name PnQty, example: PnQty0, PnQty1 PnQty2...
Hi,

In that case, I suggest you go for query first in the string format-

Dim oQuery as String = "ALTER TABLE PcbBom add "
                For columnIndex = uintOffSet To strBomPartCount.Length
                    oQuery = oQuery & "ADD COLUMN PnQty" & columnIndex.ToString() & " LONGTEXT"
                    If columnIndex != strBomPartCount.Length Then
                        oQuery = oQuery & ","
                    End If
                Next

Open in new window


Check above code for any errors and correct it and then go for below statements-
oComm = New OleDbCommand(oQuery, oConn)
oComm.ExecuteNonQuery()

Open in new window

Thanks Rikin,
I see what your doing, instead of writing each individual  column just do one write, but it doesn't answer my original question of how to set the column to SHORT TEXT instead of LONG TEXT.
Maybe I thinking of this in the wrong way, does access reserve more space for long text than short text? Or is it dynamic somehow.
ASKER CERTIFIED SOLUTION
Avatar of Rikin Shah
Rikin Shah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial