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 :)