Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2279
  • Last Modified:

dbf file check if column exists

I need to add a column to an existing table, this is a dbf table.  I'm using vb6.  To create the new column I'm using the code below.  And this works.  I just need a way to see if the column exists or not before I run the alter

    sConnect = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\Demotemp;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;"
   
   
    Set MyConn = New ADODB.Connection
    MyConn.ConnectionString = sConnect
    MyConn.Open
    MyConn.Execute "ALTER TABLE SHDIV ADD COLUMN Address2 CHAR(50) NULL;"

So how could I tell if in table SHDIV if it already has a column Address2 defined?

Thanks
Mark
0
cambridge-tech
Asked:
cambridge-tech
1 Solution
 
Jai STech ArchCommented:
you can add a On Error Go to to your peice of code...
so that when the same column  is being added again an error will be thrown ...catch the error and do as you like it...
0
 
CarlWarnerCommented:
Unfortunately, any attempts to directly figure it out are all a bit iffy and can be fooled by odd settings.

The best way, IMO, is to get a temporary RecordSet of all of the field names within the open table and loop through the RecordSet of those fieldnames to assure it doesn't exist.  It's not totally elegant, but it does provide a reliable answer allowing you to avoid an error condition long before one can happen in guessing.
0
 
CarlWarnerCommented:
As an aside, I noticed your connection string has an "Exclusive=No" in it.  In order to alter the structure of a table, you are required to have exclusive use of the table.  So, maybe a "EXCLUSIVE=Yes" is more in order here.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
Run a query on the table (SELECT *) which will return one record and see if the field exists. IF TYPE('query.field') = 'C'
0
 
suhashegdeCommented:
Check it with SQLcolumns()  in ODBC API
0
 
suhashegdeCommented:
Declare in VB

Declare Function SQLColumns Lib "odbc.dll" (ByVal hstmt&, ByVal
szTblQualifier$, ByVal cbTblQualifier%, ByVal szTblOwner$, ByVal
cbTblOwner%, ByVal szTblName$, ByVal cbTblName%, ByVal szColName$,
ByVal cbColName%) As Integer

0
 
CarlWarnerCommented:
Here is a simple and short OLE DB Session within VFP to test a field name's existence as I was saying was straight forward to do (adjust syntax accordingly for VB):

oConn = CREATEOBJECT("ADODB.Connection")

sConnect = [Provider=VFPOLEDB.1;Data Source=C:\Program Files\Microsoft Visual FoxPro 9\Samples\Northwind\northwind.dbc;Password="";Collating Sequence=MACHINE]

oConn.Open(sConnect)

oRS = CREATEOBJECT("ADODB.RecordSet")

oRS = oConn.Execute("SELECT * FROM customers WHERE .F.")

STORE ors.Fields.Count TO lnFldCount

STORE .T. TO llNoAdd

FOR ii = 0 TO lnFldCount
      IF UPPER(oRS.Fields(ii).Name) = "CONTACT"
            STORE .F. TO llNoAdd
            EXIT
      ENDIF
ENDFOR

STORE NULL TO oRS
STORE NULL TO oConn

Then based on the flag value for llNoAdd, either issue your ALTER TABLE command or not.
0
 
cambridge-techAcct MgrAuthor Commented:
Thanks Carl that worked great

And for the reminder to change the Exclusive to Yes

Thanks Again
Mark
0
 
CarlWarnerCommented:
Thanks and you're welcome.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now