I would like to know what you as a community think of my code in terms of database connection and SQL statements. How I do it physically. Let me know if there are any improvements I could make and if there are, point me in the direction on what i can do to modify/improve it (ie: code or pertinant links)

Note: The database has no password because it is just for learning I fully understand that in the industry there would have to be one. If you guys know how to connect to the database using my mechanism with a password I would love to hear it.

*Another note: I am using an access database (Accdb)

DATABASE CONNECTION

Imports System.Data.OleDb

Module Database_Connection

    Public provider As String 'This will tell VS what database source type to use.
    Public datafile As String 'This will provide the file itself that VS will use.
    Public connstring As String 'This is the connection string that will tie the Provider and Datafile together so that we can make a physical connection
    Public myconnection As OleDbConnection = New OleDbConnection 'Set's the variable myconnection as a new Connection to the database using the OleDb type.
    Public dr As OleDbDataReader 'This will be used to read data from the database.


    Public Sub Access_Database()

        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        datafile = "Folly_Beach_Data.accdb"
        connstring = provider & datafile
        myconnection.ConnectionString = connstring

        Try

            myconnection.Open() 'Opens the connection to test it.

        Catch ex As Exception

            MessageBox.Show("Error" & vbCrLf & vbCrLf & "Original Error: " & ex.ToString)

            'This is an error that most likely many people will recieve on their computers. I noticed the problem a 
            'while ago and looked for a way to fix it. This is both the easiest and only method to correct the error stated below.
            'It doesn't force you to download anything, you have to select the option to do so.
            If MsgBox("If you received an error that states: " & vbCrLf _
                      & Quotes & "The microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." _
                      & Quotes & "Please press ok to install the database engine: ", MsgBoxStyle.OkCancel, _
                      "Error") = MsgBoxResult.Ok Then

                System.Diagnostics.Process.Start("http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734")
                'This opens the webpage to directly download the file. As soon as you press okay on the messagebox the file is
                'instantly ready for download.

            ElseIf MsgBoxResult.Cancel Then

                MessageBox.Show("Here is the link for future reference if you would like to download it at a later time: " _
                                & vbCrLf & vbCrLf & "http://bit.ly/19FWu09", "For later")
                'I case you are untrusting of the file or cannot download it at the present time, it gives a link for later installation

                ConnectionError = True 'For Description view "MyVariables"

                myconnection.Close() 'Closes the connection

            End If



        Finally

            'If myconnection.State = ConnectionState.Open Then
            '    MessageBox.Show("The database was successfully connected to", "Success", MessageBoxButtons.OK)
            'End If

            ConnectionError = False

            myconnection.Close()
            'Closes the connection so we can open at a later time. 
            'Trying to re-use or re-open a connection string will crash the progrm.

        End Try

    End Sub

End Module

My Insert Statement

str = "INSERT INTO Customers (Customer_Name, Customer_Phone_Number, Customer_Address_Number, Customer_Address_Street, Customer_Address_City, Customer_Address_State, Customer_Address_Zip)" _
    & " VALUES (?,?,?,?,?,?,?)"

'This method using (?,?...) and Parameters.AddWithValue is used to prevent SQL injection.
'Makes the program much safer against hackers.

cmd = New OleDbCommand(str, myconnection)
cmd.Parameters.AddWithValue("@p1", Nametxt.Text)
cmd.Parameters.AddWithValue("@p2", Phonetxt.Text)
cmd.Parameters.AddWithValue("@p3", Housetxt.Text)
cmd.Parameters.AddWithValue("@p4", Streettxt.Text)
cmd.Parameters.AddWithValue("@p5", Citytxt.Text)
cmd.Parameters.AddWithValue("@p6", Statecbo.SelectedItem)
cmd.Parameters.AddWithValue("@p7", ziptxt.Text)
cmd.ExecuteNonQuery() 'Executes the SQL insertion above

MY UPDATE STATEMENT

str = "UPDATE Customers SET Customer_Name=?, Customer_Phone_Number=?, Customer_Address_Number=?, Customer_Address_Street=?, Customer_Address_City=?, Customer_Address_State=?, Customer_Address_Zip=? WHERE Customer_Phone_Number='" & CustomerPhoneEdit & "'"

cmd = New OleDbCommand(str, myconnection)

cmd.Parameters.AddWithValue("@Customer_Name", Nametxt.text)
cmd.Parameters.AddWithValue("@Customer_Phone_Number", Phonetxt.Text)
cmd.Parameters.AddWithValue("@Customer_Address_Number", Housetxt.Text)
cmd.Parameters.AddWithValue("@Customer_Address_Street", Streettxt.Text)
cmd.Parameters.AddWithValue("@Customer_Address_City", Citytxt.Text)
cmd.Parameters.AddWithValue("@Customer_Address_State", Statecbo.SelectedItem)
cmd.Parameters.AddWithValue("@Customer_Address_Zip", ziptxt.Text)

cmd.ExecuteNonQuery() 'Executes the SQL insertion above

Let me know what you think :)

You're ok. Assuming your code works.

The idea of having the user downloading the ole connection provider is really very good (in fact I might steal that from you :) ). If you know that the provider is not installed, then why not install it, there is not really a point to ask the user.
You might want to do this at installation time, or at programm starting time (as opposed to connection time).

You might also want to embed the downloading in try catch structure, and your reliance on the error message is also somewhat optimistic. I'd used the error number.

I personnally do not use parameters, maybe I'm wrong. But from a testing perspective, your problem is that you cannot "see" the sql in debug time using parameters (I might be wrong about this), and my experience is that this is where most of the bugs will be.

Edited 2 Years Ago by PerplexedB: thought of a few more things.

Thank you for responding! :). The reason why I ask is because because if somebody needs to download a package or a piece of software it should be optional (in my opinion). Of course obviously the program won't work with the boolean ConnectionError=True and they'll know that they would have to download it anyway. Besides, it gives them the link so they can download it on other computers or write it down if need be. And I would also how to check and see if it is already installed and that gets into a whole 'nother conglomorate of things.

Now the downloading is already within a try catch statement. It is part of the try catch for the connection itself.

The reason I use Parameters is because it's safer. It helps keep your database safe from intrusions by means of SQL Injection.

I truly appreciate your compliments and your response :)

This article has been dead for over six months. Start a new discussion instead.