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

x
?
Solved

ALTER database during first run only

Posted on 2007-07-28
2
Medium Priority
?
226 Views
Last Modified: 2012-05-05
I have a Windows application written in VB.NET (Visual Studio 2003). It's a simple inventory database program using a MS Access MDB file.

I do periodic updates to the program which just replaces the main .exe file (no dlls or anything, very basic). In my last update I decided to add a new field which needed a new column in the database. When users update the program it doesn't overwrite the database, for obvious reasons.

My problem is: How do I update the database for the new column?

I can probably figure out how to execute an ALTER statement to add the column but I want it done only if it needs to be. Either once during the update MSI is run or during the first time the new exe is run.

Example of how I connect to the DB:
Dim connString As String
Dim AppDir As String
AppDir = Application.StartupPath() & "\DBName.mdb"
Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppDir)
Command = New OleDbCommand("SELECT * FROM Main")
Command.Connection = Connection
Command.Connection.Open()
Command.ExecuteNonQuery()
Connection.Close()

Please provide code also. I'm a little rusty on my VB.NET.

Another problem I have is I don't have a seperate MSI for updating, I'm using the same install one. So when I attempt to install the update I get an error saying another version of the application is installed. And that I have to uninstall it first via the control panel. I do that and it works fine but it's annoying. Is there a tool or setting I can add to fix this?

Thanks!
0
Comment
Question by:undy30
2 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 2000 total points
ID: 19587357
You can check if the column exist on the startup of your program...

For example
<startup>
Dim connString As String
Dim AppDir As String
AppDir = Application.StartupPath() & "\DBName.mdb"
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppDir
dim da as New OleDbDataAdapter("SELECT top 1 * FROM Main", connString)
dim ds as new dataset()
da.fill(ds,"main")
if columnExists(ds.tables("main"),"<columnname">) = true false
   Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppDir)
   Command = New OleDbCommand("ALTER Main ADD COLUMN...")
   Command.Connection = Connection
   Command.Connection.Open()
   Command.ExecuteNonQuery()
    Connection.Close()
end if
Private Function ColumnExists(ByVal Table As DataTable, ByVal ColumnName As String) As Boolean
    Dim bRet As Boolean =  False
    Dim col As DataColumn
    For Each col In Table.Columns
        If col.ColumnName = ColumnName Then
            bRet = True
            Exit For
        End If
    Next
 
    Return bRet
End Function
0
 
LVL 28

Expert Comment

by:Ark
ID: 19632664
       Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, tableName, columnName})
        ColumnExists = (schemaTable.Rows.Count() > 0)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question