Visual Basic

peteyshrew wrote:

it was something so simple its stupid.

Public Class SqlHelpers
    Public Shared GetConnection(ByVal OleDbFileName As String) As OleDbConnection
        ' Your connection string goes here.  Whether it's hard coded (bad idea!), or
        ' stored in the registry or in an app.config file, this method should
        ' retrieve it and create a new connection object out of it.
        '
        ' This example will see if the specified database exists in the same path
        ' the .EXE was launched from, and if so, create a new OleDbConnection out of it.
        Dim FullPath As String = Path.Combine(Application.StartupPath, OleDbFileName)
        If File.Exists(FullPath) Then
            Dim connString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", FullPath)
            Return New OleDbConnection(connString)
        Else
            Throw New FileNotFoundException("Unable to find the database file " & _
                OleDbFileName & " in the application startup path!")
        End If
    End Sub

    .
    . other helper methods...
    .
End Class

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
    Try
        Dim conn As OleDbConnection = SqlHelpers.GetConnection("PCBank.mdb")
        Dim comm As New OleDbCommand("insert into customers CustomerID, CustomerSurname, CustomerForename) " & _
            "values('" & (tbID.Text) & "','" & (tbSur.Text) & "','" & (tbFor.Text) & "')", conn)
        icount = comm.ExecuteNonQuery()
        MsgBox("record inserted")
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        If Not IsNothing(conn) AndAlso conn.State <> ConnectionState.Closed Then
            conn.Close()
        End If
    End Try
End Sub

Public Shared Function GetConnectionString(ByVal strConnection As String) As String
        Dim strReturn As New String("")
        If Not String.IsNullOrEmpty(strConnection) Then
            strReturn = ConfigurationManager.ConnectionStrings(strConnection).ConnectionString
        Else
            strReturn = ConfigurationManager.ConnectionStrings("YourConnectionStringName").ConnectionString
        End If
        Return strReturn
    End Function